You have a really long data set with all of the data in column A. When you print, you will be wasting 75% of the horizontal white space on the page. Is there any way to have the column A data snake through the other columns? It would be nice if there was a checkbox for this, but Episode 984 resorts to using INDEX, ROW and COLUMN!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I don't know if you can see this, check out this retro old MrExcel logo sweatshirt.
I found that in the back of the drawer.
It must be from the very very early days before we had the Excel guy.
Today's problem this is send in by two people Lucy and someone else I forget your name, and they said you know look we have this one big long column of data 79 pages of stuff and we're just wasting the rest of this page.
Is there some way to get Excel to snake this data.
So, that way it fills up two three four columns and well word could do that very easily Excel there's really no good way.
Well actually, I think I have a pretty good way to do it.
What I'm gonna do is, I'm going to come over here and build a print range in column C and I'm going to use a function called INDEX.
INDEX in this case is a great function.
It's going to index column A the entire column and I'm going to put dollar signs in there to freeze it to the A and then I need to generate a row number.
Okay now, as I think about this.
I'm going to have four columns going across, and so I'm going to build a little formula here that says, I want the row of this row minus 1 times 4 plus the column of A1.
And what that should do is, successfully it gave me Abbott Donald.
Now let's do the big test as I copy this across that column is going to change the B1, C1, D1 and I should get the next three names going across the page and we'll do Format, Column Width 20 and sure enough Abbott Kelli, Abbot Marjorie and Abbott Paul.
Now, here's the big test I've been counting on that row number to increment and basically grab instead of cell number 1 to grab cell number 5.
So, I'm just going to copy this down let's see I think I've 4000.
So, I probably need to go down about 800 or so and as I start to test this they're the fifth name is showing up here and then the sixth name and seventh name and eighth name, let's go down and take a look throughout the whole data set.
Eventually, if I copied it down far enough it's going to switch over to zeros or just blanks and so it looks like I did not go far enough.
So, I'll copy down another few hundred rows and..
Right about there as we were good.
We can delete that.
So, it wasn't horrible it is one formula how to use a little bit of math skills to figure out where we're going to go with that, but now I can set the print range here.
So, the Office Icon, Print We're the heck.
Prepare, I still can't find it. I know where it was in the old Excel.
Let's go back here to Page Layout and Print Area, Set Print Area, there you go look at that I've been using this for a couple of years I still get confused, and we'll make it sure that it's wide enough to fit the longest value in each column.
And we've now, if we go into Print Preview instead of 79 pages fits to 40.
Adjust the margins.
See we're missing a column there, it's actually gonna fit in 20.
If we get it wide enough, with fit to one page.
There we go all right now, back to print preview and there we go 19 pages down from 79.
I'd be nice if we just had a checkbox, that we could check and fortunately don't.
So, we can use this function, the INDEX function and a little bit of creative math using row and column to basically make that data. Just go across the page and then down.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I don't know if you can see this, check out this retro old MrExcel logo sweatshirt.
I found that in the back of the drawer.
It must be from the very very early days before we had the Excel guy.
Today's problem this is send in by two people Lucy and someone else I forget your name, and they said you know look we have this one big long column of data 79 pages of stuff and we're just wasting the rest of this page.
Is there some way to get Excel to snake this data.
So, that way it fills up two three four columns and well word could do that very easily Excel there's really no good way.
Well actually, I think I have a pretty good way to do it.
What I'm gonna do is, I'm going to come over here and build a print range in column C and I'm going to use a function called INDEX.
INDEX in this case is a great function.
It's going to index column A the entire column and I'm going to put dollar signs in there to freeze it to the A and then I need to generate a row number.
Okay now, as I think about this.
I'm going to have four columns going across, and so I'm going to build a little formula here that says, I want the row of this row minus 1 times 4 plus the column of A1.
And what that should do is, successfully it gave me Abbott Donald.
Now let's do the big test as I copy this across that column is going to change the B1, C1, D1 and I should get the next three names going across the page and we'll do Format, Column Width 20 and sure enough Abbott Kelli, Abbot Marjorie and Abbott Paul.
Now, here's the big test I've been counting on that row number to increment and basically grab instead of cell number 1 to grab cell number 5.
So, I'm just going to copy this down let's see I think I've 4000.
So, I probably need to go down about 800 or so and as I start to test this they're the fifth name is showing up here and then the sixth name and seventh name and eighth name, let's go down and take a look throughout the whole data set.
Eventually, if I copied it down far enough it's going to switch over to zeros or just blanks and so it looks like I did not go far enough.
So, I'll copy down another few hundred rows and..
Right about there as we were good.
We can delete that.
So, it wasn't horrible it is one formula how to use a little bit of math skills to figure out where we're going to go with that, but now I can set the print range here.
So, the Office Icon, Print We're the heck.
Prepare, I still can't find it. I know where it was in the old Excel.
Let's go back here to Page Layout and Print Area, Set Print Area, there you go look at that I've been using this for a couple of years I still get confused, and we'll make it sure that it's wide enough to fit the longest value in each column.
And we've now, if we go into Print Preview instead of 79 pages fits to 40.
Adjust the margins.
See we're missing a column there, it's actually gonna fit in 20.
If we get it wide enough, with fit to one page.
There we go all right now, back to print preview and there we go 19 pages down from 79.
I'd be nice if we just had a checkbox, that we could check and fortunately don't.
So, we can use this function, the INDEX function and a little bit of creative math using row and column to basically make that data. Just go across the page and then down.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
