Robert asks about using a pivot table to report on text values. This would be easy if Microsoft offered a way to specify "First" as the calculation, but they don't. Instead, Episode #1193 shows a Pivot/VLOOKUP solution to the problem.
Transcript of the video:
MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent in by Mary, Mary's trying to get this data to fit on one page, and I said “You know, a great way to do this, first of all, is to use this new Page Layout View.” So down here by the Zoom slider we have Normal and Page Break Preview which we've had before, but the Page Layout, that's new in Excel 2007.
And the cool thing there is, you can see very quickly where the Page Break is going to be.
So we're in trouble here, there's no way this thing is going to fit.
We come to Page Layout, go to Orientation, Landscape, alright, that gets us close, Margins, and go to Narrow, ah, look at that, It fits.
Alright, but then Mary started to say “Well you know, I need more room for this column here and this column here,” and so on, and you see that that forces things over.
So I said “Alright.” And what we can do is, here in the Scale to Fit section, Page Layout, just for the width we're going to say “we want to fit that to 1 page wide.” Alright, and you can see that we fit about 36 rows of data.
But the problem is, as you start to make these columns wider, if you decide “What, you know, I need more room for data here.” We're actually making everything a lot smaller.
So now, you know, 38 rows, and as you continue to make these columns wider, it's going to force the zoom to be smaller and smaller and smaller.
So you have to be careful when you're using Scale to Fit, because as you make things wider, the font's just going to get smaller and, you know, eventually no one's going to be able to read anything there.
So, a couple of cool tricks.
First of all, Page Layout, a great view, because you get to still work in the data, you can still change things, you can edit data, you can add new columns, but you get to see where things are.
And then change of the orientation, and also the new margins, Narrow, you know, and look at that left .25, right .25, that's good!
If you needed the top and bottom, you can still come into Custom Margins like you could do in Excel 2003 and actually edit those, so a couple of cool ways to go.
I want to thank Mary for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question is sent in by Mary, Mary's trying to get this data to fit on one page, and I said “You know, a great way to do this, first of all, is to use this new Page Layout View.” So down here by the Zoom slider we have Normal and Page Break Preview which we've had before, but the Page Layout, that's new in Excel 2007.
And the cool thing there is, you can see very quickly where the Page Break is going to be.
So we're in trouble here, there's no way this thing is going to fit.
We come to Page Layout, go to Orientation, Landscape, alright, that gets us close, Margins, and go to Narrow, ah, look at that, It fits.
Alright, but then Mary started to say “Well you know, I need more room for this column here and this column here,” and so on, and you see that that forces things over.
So I said “Alright.” And what we can do is, here in the Scale to Fit section, Page Layout, just for the width we're going to say “we want to fit that to 1 page wide.” Alright, and you can see that we fit about 36 rows of data.
But the problem is, as you start to make these columns wider, if you decide “What, you know, I need more room for data here.” We're actually making everything a lot smaller.
So now, you know, 38 rows, and as you continue to make these columns wider, it's going to force the zoom to be smaller and smaller and smaller.
So you have to be careful when you're using Scale to Fit, because as you make things wider, the font's just going to get smaller and, you know, eventually no one's going to be able to read anything there.
So, a couple of cool tricks.
First of all, Page Layout, a great view, because you get to still work in the data, you can still change things, you can edit data, you can add new columns, but you get to see where things are.
And then change of the orientation, and also the new margins, Narrow, you know, and look at that left .25, right .25, that's good!
If you needed the top and bottom, you can still come into Custom Margins like you could do in Excel 2003 and actually edit those, so a couple of cool ways to go.
I want to thank Mary for sending that question in, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
