Excel Tip: Setting the print area and page breaks

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.

Print Area

By default, the print area will be what is know as the Used Range of the worksheet. This is from cell A1 to the column of the rightmost cell with anything in it to the row of the cell furthest down the worksheet with anything in it.

This, however, can be changed by highlighting the range that you want as your Print Area and selecting Print Area, Set Print Area from the Page Layout ribbon.

If everything you want to print is not adjacent to each other, you can select multiple ranges by holding the Control key while you select them.

You can even set the Print Area as a dynamic range, but we'll leave that for a future post.

Page Breaks

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.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".