Search Not Just Numbers

Friday 27 January 2017

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".

7 comments:

  1. How to set page height to automatic:

    not obvious (at least it wasn't to me); you need to set the scrollable number of pages to zero, or delete it (NB you can't actually scroll to zero, you have to type)
    but very useful (and if you're doing this then you probably want to minimise the left and right margins too)

    jim

    ReplyDelete
    Replies
    1. hmm… just noticed that there's now selectable options on the print preview for this - I've only just upgraded to 2010 (8 months ago!) and still use the old Page Setup dialogue as my first call

      jim

      Delete
  2. Jim
    Don't worry, I regularly find there's a new way to do something that I've been doing the same way since Excel 2003 or before!

    ReplyDelete
  3. Glen—
    Bless you for mentioning this simple but critically important Excel feature! In my book, “Painting with Numbers: Presenting Financials and Other Numbers So People Will Understand You,” I list 18 Deadly Sins of Numbers Presentation. Here’s Deadly Sin #12:

    12. TO PRINT THE FINISHED REPORT, REQUIRING YOUR AUDIENCE TO DO MORE THAN JUST CLICK THE “PRINT” ICON

    In other words, be familiar with Print Area, Page Breaks, and Page Setup overall. It is a courtesy to your audience that they may well notice, and they will certainly notice if you overlook it!

    Randall Bolten

    ReplyDelete
  4. Hi Glen

    Do you also have a tip to get pages from different worksheets in a workbook all come out with the same size font? I've found that setting the font size the same on each sheet doesn't seem to work.

    ReplyDelete
    Replies
    1. Nicola
      You'll need to make sure that the scale % below the width and height settings is set the same on each sheet. Logically, you can't then also tell it to fit to a page, as it can only do this by adjusting the size of the characters.
      One approach that should work would be to fit them all to page, see ehich one has the lowest scale %, and set the scale % of the others to that figure.

      I hope that helps.

      Delete