On the Page tab of File>Page Setup, there is a Scaling option that allows you to print using a percentage or a number of pages (wide or tall). When you use the Fit To option, to specify a number of pages, Excel ignores your manual page breaks. I don’t know about you, but that’s not usually what I want to happen.
I don’t know a good way to get around this, but if the layout of your page doesn’t change too much, there is at least one solution. When you scale using Fit To, Excel calculates the percentage that it has to shrink your print area in order to meet the Fit To conditions.
By specifying the Fit To, you can re-enter Page Setup, see what percentage Excel has calculated, and use that percentage. In the above example, I set Fit To to 1 page wide by 1 page tall. I closed out Page Setup and went right back into it. Excel figured that it would take a 59% scaling to get it on one page, and I switched option buttons back to Adjust To %. Now my manual page breaks are recognized.
Actually, Excel will only ignore your page breaks if they do not logically allow the “fit to” condition to be met.
For example, if you have a “fit to height” condition set of 2 pages but have three horizontal page breaks, it is illogical and is ignored. If you have a “fit to width” set of 2 pages but have three vertical page breaks, it is illogical and is ignored.
However, if you have it set to “fit to height” and have only vertical page breaks or “fit to width” and have horizontal page breaks, they will be obeyed.
I personally like 100% to keep all my workpapers uniform looking, but when I share files, some people have printers that don’t work with my files and will split the last column onto another page. I work around this by making it 100%, but then setting it to “fit to width” of 1 page and leaving “fit to height” blank. My page breaks are followed, and it does scale down to 98 or 99% when needed.
Nice blog, BTW. Please keep it up.
Thanks rzf. You’re right, it does respect page breaks when it’s logical to do so. I also noticed that it still doesn’t show them on the sheet (at least in XL2000).
Thanks for pointing that out. This is one reason that I think blogs are better than FAQs – corrections.
i nneed a macro that prints a sheet in landscape
and in scaling fit to 1 page(s) wide by 1 tall
could someone help me out with this
Hi all,
The problem I’m having is exactly as originally described by Dick.
I have a sheet with 9 graphs which I want to display nicely on 3 pages as
4/4/1. Setting print area to 1 wide by 3 high fits all graphs, but places
page breaks through the middle of some.
I’ve been trying to introduce 2 horizontal page breaks, but they are always ignored
while ever page width/height options are included. Only way I’ve managed to format the
sheet correctly is to determine and set the appropriate scale (zoom). When this is done
the manual page breaks are recognised correctly.
Greg –
Set up the worksheet with print area and appropriate page breaks at 100% zoom, the way you want it to print. Then make the charts, and size them appropriately to fit on the pages.