Software optimize

Eliminate a mostly blank page from the end of an Excel report

Excel has a built-in feature that lets you control the number of pages used to print data. This feature enables you to eliminate a last page that has only a few records or to expand data over more pages than it really requires.

No matter what kind of formatting you apply to an Excel sheet, you can't easily control the number of rows on each page when printing. Occasionally, you might end up with just a few rows printing on the last page. That's not exactly a problem, but those two or three additional rows printed on the last sheet can look unprofessional. In addition, it seems wasteful in today's greener environments.

There's an easy way to avoid this situation if you're willing to compromise just a bit. Simply use Excel's Scale To Fit option to reduce the number of pages needed. For instance, if a report is five pages, but page five has only two rows of data, this option would reduce the number of pages to four. Here's how:

  1. From the File menu, choose Page Setup and click the Page tab. In Excel 2007, click the Page Layout tab and open the Scale To Fit group.
  2. In the Scaling section, click the Fit To option.
  3. In the Tall control (the second control), enter the number of printed pages that you want. In this case, that's 4. Now click OK.

Excel will reduce the text size just enough to squeeze those last few records onto the fourth page, instead of printing a mostly empty fifth page. That's the compromise -- the option changes the text size just a bit. In most situations, where you're pulling only a few records onto the previous page, no one will notice the change. However, if you have conventions that don't allow for subtle changes, you won't want to use the Scale To Fit option. In addition, Excel will ignore manual page breaks when you apply this option.

You can use this same setting to expand the size of the printout. For instance, if you have four pages of data and you want to fill six, you'd follow the instructions above but enter 6 in the Tall control. Excel won't increase the font size, but it will divide the data over six pages. The first Fit To setting works the same way, with columns.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
stapleb
stapleb

I have used this option for years, and you are right, it saves wasted paper. I usually go to Print Preview first to see what is needed, and access Page Setup from there as I can immediately see the results of the steps I have taken as soon as I click OK.

plumbsue60
plumbsue60

I think that that is great. I am learning more about excel everyday.

Excelmann
Excelmann

After clicking the "Fit to" option, note that just above the "Adjust to % normal size" option has changed to reflect how much Excel has reduced your print area. Click the radio button for "Adjust to % normal size" (do not adjust the %) and you can now set page breaks where you desire and still control the total pages.

gfhavewala
gfhavewala

So simple, yet so effective. (When the solution is absurdly simple, it takes a genius to think of it!)

RB1955
RB1955

I agree with with "stapleb" in that Print Preview is the place to start. If everything is looking ok, no changes needed. But that is rarely the situation with XL printouts when I'm doing engineering analysis. And I usually have to print in Landscape to get as much width-per-page as I can. One option that the Corel/WordPerfect product has is literally called MakeItFit. You choose the MakeItFit option when needed and you can change things like font size (I was surprised that some fonts show as 12 can be changed to 11.7 and all 12 font is scaled for you), borders (top, bottom, left or right sides individually), line spacing (want 1.75 lines on your report... ok do it), and other options (if memory serves). I don't think XL has a comparable feature, but I'm stuck with Office '03 right now.

ssharkins
ssharkins

Desperation. ;) That's what forces me to look around most of the time.

basil.cinnamon
basil.cinnamon

That is a useful trick and I use it in many of my macros before printing. to automate the layout. I first get the total row count, then estimate how many pages tall to use (in essence, telling it to print N rows per page), and finally set the pages tall number. However, this has its limitations because it can only count rows, but cannot figure out how high (in inches) is each row (when rows are set to wrap), and thus cannot figure out the total height (inches) consumed by all the rows. Consequently, one frequently gets a printout where the printed font is either too tiny or too large. Thus, if many rows have wrapped, by forcing N rows onto one page (by setting the pages tall count) this will cause the font to be too tiny. If no rows have wrapped, the font can be too large. Does anyone know a way to figure out how many inches (as opposed to rows) the printout will need? If one were able to know that, one could set a desirable value of X inches per page, then calculate the number of pages tall needed, and thereby get the font to always be of the same size.

wwgorman
wwgorman

tThis is a great tip and although I've used Excel since version 1.1 and used the fit to number of pages I've never used the fit to tall option. I'm in the midst of a report now using multiple Excel pages and i'll be sure to use it.

ssharkins
ssharkins

This is how I use it as well -- in Print Preview, I can see that the last page is mostly empty, so I flag the setting and print. Well, sometimes I check again, if there are more than just a few rows on the last page, but usually, I get what I want this way. This is one of those easy tricks that support people can share with their users because there's no intuitive built-in alternative that you can just leave enabled -- none that I'm aware of anyway.

ssharkins
ssharkins

I've never tried to automate this process -- I just preview the report after flagging this setting to make sure the results are as expected. It would be a neat trick if you could come up with a smart macro -- good luck!

dhays
dhays

If your data allows, one could adjust the margins a little to get those extra lines or columns to fit. While in the print preview pane ajust margins or column size to fit your needs.