Excel Tip: Setting the print area and page breaks
From the Not Just Numbers blog:
In this post I want to touch on a topic that I've not written a lot about on this blog - printing.
There are many settings in Excel that you can use to influence what is printed, and I'm sure I will revisit this topic to cover some more of them soon.
In this post, I simply want to focus on how to determine how a single worksheet appears on paper, when you click the Print button.
This is essentially driven by two elements:
- The Print Area - which determines what section of the sheet is printed
- The Page Breaks - which determine how this is spread across the physical pages printed.
Excel breaks up the Print Area based upon the settings on the Page Layout ribbon.Here, you can set the page orientation, paper size, margins, etc. as well as set the scale, or more usefully, set the number of pages wide the Print Area should be considered to be and how many pages long (using the Width and Height settings).
These Width and Height settings are, by default, set to automatic, meaning that they will be determined by the page size and orientation, along with the scale set beneath them.
You can, however, fix them so that, for example, the whole Print Area is resized to fit on 1 page wide and 1 page tall (by setting them both to 1). Obviously, with that setting, there will be no page breaks.
Another typical setting is to set the width to 1 but leave the height as automatic. This will resize so that it is always only 1 page wide, but will insert as many page breaks as required for the height.
Most of the time, this is all you need to determine your page breaks, however, there are also times where you want the breaks to happen in specific places. You can preview where they are and move them around using Page Break Preview.
This is accessed from the View ribbon and shows blue lines where the page breaks are and a watermark telling you the page numbers. These blue lines can be dragged to wherever you need them.
That's about it, and the good thing is that all of these settings stay with the sheet, so you should only need to set them once unless the layout of the sheet changes.
You might also be interested in
Chartered Accountant with extensive experience in management reporting, Microsoft Excel and business process improvement.
Also runs a spreadsheet development business at http://www.needaspreadsheet.com.
Provides consulting on the above...