Cheryl reports that when she prints out her report from Excel, Excel randomly skips half a page on page 3.
How bizarre is that?
It turns out that Excel doesn't really behave randomly, it just seems that way. Today, in Episode *#1628, Bill teaches us about Manual Page Breaks, Margins, and Center on Page Vertically.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
*Note: The Podcast indicates this is Podcast #1626; it is actually #1628.
How bizarre is that?
It turns out that Excel doesn't really behave randomly, it just seems that way. Today, in Episode *#1628, Bill teaches us about Manual Page Breaks, Margins, and Center on Page Vertically.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
*Note: The Podcast indicates this is Podcast #1626; it is actually #1628.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn excel from MrExcel podcast. Episode # 1626 Page 3 Skips Down Half a Page When Printing Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
It's good to be back podcasting and today's question sent in by Cheryl.
Cheryl says I have the weirdest thing I have this Staff Directory here and when i print it out for some unknown reason I'm going to page down page down this page skips half a page and there's no blank rows there.
She says this is right before person 34.
What's going on and you can see there are no blank rows here but i figured out how it was and it's kind of an insidious little problem.
Alright so here's the situation. We have 2 rows per person and whoever has been maintained it either Cheryl or someone noticed that the page break was happening right here was cutting this person off, person 22 and so they came to this cell and they did Insert Page Break to force person 22 start on a new page.
Well that's great right. But then somewhere along the way someone added a bunch of records to this page which this former manual Page Break here then really wasn't needed anymore.
It was needed up here and so excel is putting automatic Page Breaks in here and automatic page break is saying.
Hey I've gotten to the end of as much as i can fit on the page, so i'm going to force you to put in a Page Break and so It starts here with person 34 but then the old manual Page Break is still hanging out.
Alright and why is it printing in the middle of the page?
It's printing in the middle of the page because someone back in excel 2003 probably on the Margins tab checked Horizontally and Vertically. All Right so let's uncheck Vertically and we'll take a look at Print Preview.
Page down or next page next page.
All right so now we're just getting the few page, the few records.
But at least they're printing at the top of the page.
All right here's how i want to fix this i'm going to go find those manual Page Breaks and I'm going to remove them.
I use Alt+I+B I actually don't know where it is in the or it must be back in under here or under home.
Insert. Now i have no idea where it is. I still use the excel 2003.
Alt+I+B for Insert Page Breaks which also will remove a Page Break that you've inserted.
All right so this is an automatic one. That's fine.
Let me come down here and find. You can see if you look really really close maybe I'll zoom in.
The manual Page Breaks have a longer - than the automatic Page Breaks.
I'm going to move this one here and in Cheryl's case there were only two of them. There are only two places where they forced that to happen.
Then i'm going to change a few Page Setup things.
I want these headings to appear at the top of every page.
It's under Page Layout.
Print Titles. I'm going to say Repeat Rows 5 : 5 at the top of every page and then i'm going to go into Print Preview. This here is called Print Preview Full Screen.
I added it to my Quick Access toolbar.
It gets me back to the old Print Preview so yeah i'm going to see that it's splitting person 22.
That's bad but if i adjust my margins so that way person 22 fits. Then provided every single person has exactly two records we should be okay.
Let's take a look at the next one.
Yes here always now page 2 is going to end at the end of a person.
All right which is perfect I never have to insert manual Page Breaks anymore because it's just going to work.
I've used this trick a lot. Just the margins either up or down until i get full records all the time and we solved that problem all right.
So there you go we thought that we had a situation where excel was skipping down half a page when printing.
It's Actually that there was a manual Page Break left over from before and that obscure setting to center on page vertically makes it print in the middle of the page.
Alright hey I want to thank you for stopping by. See you next time for another netcast from MrExcel.
Learn excel from MrExcel podcast. Episode # 1626 Page 3 Skips Down Half a Page When Printing Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
It's good to be back podcasting and today's question sent in by Cheryl.
Cheryl says I have the weirdest thing I have this Staff Directory here and when i print it out for some unknown reason I'm going to page down page down this page skips half a page and there's no blank rows there.
She says this is right before person 34.
What's going on and you can see there are no blank rows here but i figured out how it was and it's kind of an insidious little problem.
Alright so here's the situation. We have 2 rows per person and whoever has been maintained it either Cheryl or someone noticed that the page break was happening right here was cutting this person off, person 22 and so they came to this cell and they did Insert Page Break to force person 22 start on a new page.
Well that's great right. But then somewhere along the way someone added a bunch of records to this page which this former manual Page Break here then really wasn't needed anymore.
It was needed up here and so excel is putting automatic Page Breaks in here and automatic page break is saying.
Hey I've gotten to the end of as much as i can fit on the page, so i'm going to force you to put in a Page Break and so It starts here with person 34 but then the old manual Page Break is still hanging out.
Alright and why is it printing in the middle of the page?
It's printing in the middle of the page because someone back in excel 2003 probably on the Margins tab checked Horizontally and Vertically. All Right so let's uncheck Vertically and we'll take a look at Print Preview.
Page down or next page next page.
All right so now we're just getting the few page, the few records.
But at least they're printing at the top of the page.
All right here's how i want to fix this i'm going to go find those manual Page Breaks and I'm going to remove them.
I use Alt+I+B I actually don't know where it is in the or it must be back in under here or under home.
Insert. Now i have no idea where it is. I still use the excel 2003.
Alt+I+B for Insert Page Breaks which also will remove a Page Break that you've inserted.
All right so this is an automatic one. That's fine.
Let me come down here and find. You can see if you look really really close maybe I'll zoom in.
The manual Page Breaks have a longer - than the automatic Page Breaks.
I'm going to move this one here and in Cheryl's case there were only two of them. There are only two places where they forced that to happen.
Then i'm going to change a few Page Setup things.
I want these headings to appear at the top of every page.
It's under Page Layout.
Print Titles. I'm going to say Repeat Rows 5 : 5 at the top of every page and then i'm going to go into Print Preview. This here is called Print Preview Full Screen.
I added it to my Quick Access toolbar.
It gets me back to the old Print Preview so yeah i'm going to see that it's splitting person 22.
That's bad but if i adjust my margins so that way person 22 fits. Then provided every single person has exactly two records we should be okay.
Let's take a look at the next one.
Yes here always now page 2 is going to end at the end of a person.
All right which is perfect I never have to insert manual Page Breaks anymore because it's just going to work.
I've used this trick a lot. Just the margins either up or down until i get full records all the time and we solved that problem all right.
So there you go we thought that we had a situation where excel was skipping down half a page when printing.
It's Actually that there was a manual Page Break left over from before and that obscure setting to center on page vertically makes it print in the middle of the page.
Alright hey I want to thank you for stopping by. See you next time for another netcast from MrExcel.
