Microsoft Excel Tutorial:
How to add page breaks so you print five rows per page of a 5000 page report? Shoaib wants to do this without VBA! There are four methods in this video, three of which do not use VBA.
Table of Contents
(0:00) Problem is how to print five rows per page without VBA
(0:22) Solution 1: Change bottom margins
(1:18) Solution 2: VBA Macro
(1:47) Selecting top row of every page with Go To Special and a formula
(2:10) Solution 3: Insert one page break, then Tab / F4 998 times
(2:46) Solution 4: Use Subtotals in hidden columns
(5:25) Wrap-up
How to add page breaks so you print five rows per page of a 5000 page report? Shoaib wants to do this without VBA! There are four methods in this video, three of which do not use VBA.
Table of Contents
(0:00) Problem is how to print five rows per page without VBA
(0:22) Solution 1: Change bottom margins
(1:18) Solution 2: VBA Macro
(1:47) Selecting top row of every page with Go To Special and a formula
(2:10) Solution 3: Insert one page break, then Tab / F4 998 times
(2:46) Solution 4: Use Subtotals in hidden columns
(5:25) Wrap-up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2370 - Print Five Rows Per Page.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, a great question from Shoaib. He watched an old video.
Where I showed how to add page breaks with VBA.
He needs to print five rows per page.
From a 5000-line report.
With no VBA. Oh my gosh, No VBA.
The quick answer if your rows are all the same height.
Then the best thing to do is to change the margins.
Go into Page Break Preview here.
You can see we are getting 38 rows plus the header on each page.
We go into Margins, Custom Margins.
Kick the bottom margin way up.
I am going to try 6.5 inches.
Click OK.
And there are now six rows per page.
Argh just one row too many.
Margins. Custom Margins. Go to 6.75 inches.
Click OK.
Four rows. Not quite enough space. We are allowed to go in between 6.5 and 6.75.
Instead of 6.75, I will go with 6.55.
Click OK.
And we have five rows per page.
Now that is only going to work if every row is the same height.
I have to guess that because Shoaib only wants five rows per page, you must have some rows that are taller.
And I know he said, "No VBA".
But it would be super-easy with a little bit of VBA.
Reset all the old page breaks.
Figure out where the last row is.
Go from row 7 to the last row step five.
So skipping every five rows.
and then HPageBreaks.Add before that cell.
I just click run here and we are done.
Look at page break, page break, page break.
That's great.
But Shoaib says “No VBA”.
Oh Shoaib, I am convinced that this trick used to work.
But I went to my Excel MVP friends they tried it all the way back to Excel 2003 and it never worked.
So, what I've done here is I have added a formula that puts a 1 on the first row of every page.
So it's doing the MOD of ROW comma five.
If it is equal to 2 then put a 1, otherwise quote quote.
Go To Special, Formulas, that are numbers.
So I have only the cells with 1 selected.
And then I go to Break, Insert Page Break.
And it only inserts one page break! It doesn't insert them all.
I swear this used to work.
Alright. So the last thing I did was insert a page break.
So what I could do now (and I am not suggesting you do this) is Tab to move from A7 to A12.
And then F4 is Repeat Last Command.
So you have 5000 rows? You are going to do Tab/F4 998 more times.
Tab. F4. Tab. F4. Tab. F4. Tab.
Ugh. This will not be practical.
Shoaib, here we go. This seems like a hassle but it is fast.
Choose Table Design, Table Styles, Light to remove the table formatting.
And then, Table, Convert to Range.
Do you want to convert the table to a normal range?
Yes I am going to have to because this trick requires it to be a normal range.
Out here I am going to put a dummy column with the number 1.
Double-click to copy that down.
That gives me a numeric column on the right hand side.
On the left side, insert a column.
And I am going to say Page.
Alight. Now. =ROW() plus zero divided by 5.
That zero is going to change. I can never remember what the right number is.
We are just going to copy this down a little bit.
My whole goal here is to get everything in the first five rows to have the same digit before the decimal.
And THIS row - number seven - to be the first one have 1 before the decimal.
Alright so it looks like instead of ROW() plus zero I am going to need ROW() minus two.
Let's try that.
Double click and copy that down.
So these are all a zero with a decimal point after it.
And to get rid of everything after the decimal point we use the INT function like this.
Alright so now all of those are on page zero. And then five on page one.
Five on page two. Five on page three.
In my case I have 116 pages. In your case you'll have a thousand pages.
Then what we're going to do is we are going to say that the print range is only going to be B through K.
So Page Layout, Print Area, Set Print Area.
On Page Titles, make sure that 1:1 is set for Rows to Repeat at Top.
Now check this out. That means that no one is going to see column A or column L.
You can even hide them after you do this trick.
Data, Subtotal.
At each change in Page.
We are going to use… it doesn't matter what function!
Because no one's going to see column A.
Sum the Dummy column.
And right here - Page Break between Groups.
The fast way to insert a thousand page breaks.
All right there. See we have 0 total, 1 total, 2 total? That's okay because that's not going to print Hide column A. Hide column L.
It is certainly faster than pressing Tab, F4 998 times.
And when we look at Print Preview, there is the first five.
Next page. The next five. Next page. The next five.
And, YES, there IS a blank row at the end of each page.
But who cares? That's why we cleared the table format so there were no borders or anything like that.
Well, without VBA, that's the best that I can come up with.
If you like these videos please Like, Subscribe, and Ring the Bell.
I want to thank Shoaib for sending that question in.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, a great question from Shoaib. He watched an old video.
Where I showed how to add page breaks with VBA.
He needs to print five rows per page.
From a 5000-line report.
With no VBA. Oh my gosh, No VBA.
The quick answer if your rows are all the same height.
Then the best thing to do is to change the margins.
Go into Page Break Preview here.
You can see we are getting 38 rows plus the header on each page.
We go into Margins, Custom Margins.
Kick the bottom margin way up.
I am going to try 6.5 inches.
Click OK.
And there are now six rows per page.
Argh just one row too many.
Margins. Custom Margins. Go to 6.75 inches.
Click OK.
Four rows. Not quite enough space. We are allowed to go in between 6.5 and 6.75.
Instead of 6.75, I will go with 6.55.
Click OK.
And we have five rows per page.
Now that is only going to work if every row is the same height.
I have to guess that because Shoaib only wants five rows per page, you must have some rows that are taller.
And I know he said, "No VBA".
But it would be super-easy with a little bit of VBA.
Reset all the old page breaks.
Figure out where the last row is.
Go from row 7 to the last row step five.
So skipping every five rows.
and then HPageBreaks.Add before that cell.
I just click run here and we are done.
Look at page break, page break, page break.
That's great.
But Shoaib says “No VBA”.
Oh Shoaib, I am convinced that this trick used to work.
But I went to my Excel MVP friends they tried it all the way back to Excel 2003 and it never worked.
So, what I've done here is I have added a formula that puts a 1 on the first row of every page.
So it's doing the MOD of ROW comma five.
If it is equal to 2 then put a 1, otherwise quote quote.
Go To Special, Formulas, that are numbers.
So I have only the cells with 1 selected.
And then I go to Break, Insert Page Break.
And it only inserts one page break! It doesn't insert them all.
I swear this used to work.
Alright. So the last thing I did was insert a page break.
So what I could do now (and I am not suggesting you do this) is Tab to move from A7 to A12.
And then F4 is Repeat Last Command.
So you have 5000 rows? You are going to do Tab/F4 998 more times.
Tab. F4. Tab. F4. Tab. F4. Tab.
Ugh. This will not be practical.
Shoaib, here we go. This seems like a hassle but it is fast.
Choose Table Design, Table Styles, Light to remove the table formatting.
And then, Table, Convert to Range.
Do you want to convert the table to a normal range?
Yes I am going to have to because this trick requires it to be a normal range.
Out here I am going to put a dummy column with the number 1.
Double-click to copy that down.
That gives me a numeric column on the right hand side.
On the left side, insert a column.
And I am going to say Page.
Alight. Now. =ROW() plus zero divided by 5.
That zero is going to change. I can never remember what the right number is.
We are just going to copy this down a little bit.
My whole goal here is to get everything in the first five rows to have the same digit before the decimal.
And THIS row - number seven - to be the first one have 1 before the decimal.
Alright so it looks like instead of ROW() plus zero I am going to need ROW() minus two.
Let's try that.
Double click and copy that down.
So these are all a zero with a decimal point after it.
And to get rid of everything after the decimal point we use the INT function like this.
Alright so now all of those are on page zero. And then five on page one.
Five on page two. Five on page three.
In my case I have 116 pages. In your case you'll have a thousand pages.
Then what we're going to do is we are going to say that the print range is only going to be B through K.
So Page Layout, Print Area, Set Print Area.
On Page Titles, make sure that 1:1 is set for Rows to Repeat at Top.
Now check this out. That means that no one is going to see column A or column L.
You can even hide them after you do this trick.
Data, Subtotal.
At each change in Page.
We are going to use… it doesn't matter what function!
Because no one's going to see column A.
Sum the Dummy column.
And right here - Page Break between Groups.
The fast way to insert a thousand page breaks.
All right there. See we have 0 total, 1 total, 2 total? That's okay because that's not going to print Hide column A. Hide column L.
It is certainly faster than pressing Tab, F4 998 times.
And when we look at Print Preview, there is the first five.
Next page. The next five. Next page. The next five.
And, YES, there IS a blank row at the end of each page.
But who cares? That's why we cleared the table format so there were no borders or anything like that.
Well, without VBA, that's the best that I can come up with.
If you like these videos please Like, Subscribe, and Ring the Bell.
I want to thank Shoaib for sending that question in.
We'll see you next time for another netcast from MrExcel.
