The page setup options within Excel provide you with flexibility and control over printed output.  In this post we look at the Page Layout view, the Page layout tab and the Page Setup dialog box.

My previous post on printing looked at the three different views (Normal, Page Layout and Page Break Preview) and the Print Preview screen. Take this opportunity now to refresh yourself on this before proceeding with this post.

This post will again use the Power Comparison file as an example.

YouTube Video on Printing in Excel

Here is a video which shows you how to print your work in Excel:

Print your work

Page Layout View

After opening your Excel file, select the Page Layout view.  The easiest way to do this is to go down to the status bar in the bottom right hand corner of the Excel screen and click on the middle button in the group of three view buttons.

One of the nice things specifically about the Page Layout view is that it makes it easy to add a header or footer to your page.  A header is the area at the top of a page and a footer is the area at the bottom of a page.  They are in a different area to the main content part of the page.

If you scroll to the top of your worksheet in Page Layout view then you see a white space at the top of the sheet with the words Click to add header in the centre of this area.  Excel allocates three areas for the header (and also for the footer): left hand side (left aligned), the middle (centre aligned) and right hand side (right aligned).  They are separate in terms of defining what goes in each area but they are not separated by any boundaries when it comes to printing, so one area can literally overlap another area if it is too large, for example two lots of printing in the one spot!

Click in one of the three header slots at the top of the page.

The Design tab displays.  This is a new tab that only shows up when you are editing the header or footer in Page Layout view. Let’s go quickly go through each group of buttons and controls.

Header and Footer

• Click Header to see a range of predefined options for your header.  If one of these is suitable then select it and you don’t need to go any further with adding a header.  Please note that if you see a comma in a particular option then this is a separator between different areas of the header.
• Click Footer to see a range of predefined options for your footer. It operates in the same way as the header options.

Header & Footer Elements

• Page Number and Number of Pages – I quite like to use these two together, for example, type “Page “, then click Page Number (it shows up as &[Page]), then type ” of ” and then click Number of Pages (it shows up as &[Pages]), i.e. Page &[Page] of &[Pages]. The ampersand (&) followed by a particular word in square brackets is Excel’s way of coding this particular field or function.
• Current Date, Current Time – These will show the current date or time when the information is sent to the printer, i.e. not the date or time when the file was last saved.
• File Path, File Name, Sheet Name – File Path combined with File Name is a good option if you sometimes forget where you saved a particular document and you have a printed page from this file.  File Name is useful if you include your method of version control in the file name (e.g. a version name and/or date) or you just want to remember what you saved it as.  Sheet Name is the name of the particular Excel sheet that you are working on.  I sometimes use this as a heading.
• Picture, Format Picture – If you would like to insert a picture like a company logo then click Picture. The Insert Picture dialog box opens up and you will have to locate and select the picture file.  Once you have inserted a picture then you can format it using Format Picture, e.g. scale it to a suitable size.  Please note that you will most probably have to adjust the size of your header (or footer) area, otherwise the logo will overlap with the content on your worksheet.  See later on in this post on how to adjust your margins.

Please note that any text appearing in the header or footer can be formatted using the options on the Home tab in the Font group.

Navigation

Use these two buttons to quickly move between the header and footer.  Only one of these buttons is active at a time.

Options

Some of the features for the header and footer like Different First Page and Different Odd and Even Pages are more common in word processing applications like Microsoft Word. Nevertheless, it is nice to have this flexibility, when required. For example, your first page could contain a title and additional information at the top, so you may not want a header on this page. However, subsequent pages may simply have the title in a smaller font in the header.

Page Layout Tab

Now click outside the header and footer area, e.g. select cell A1, and then select the Page Layout tab.  There are three groups that I would like to look at on this tab.

Page Setup

• Margins – There are some built-in options that you can select for the margins (e.g. Normal, Wide or Narrow).  If you would like something different then select Custom Margins … at the bottom of the dropdown list. The Page Setup dialog box displays, opened on the Margins tab – see the Page Setup Dialog Box below.
• Orientation – There are two options to choose from: Portrait or Landscape.  Portrait is when the long side of the page is vertical, i.e. up and down.  Landscape is when the long side of the page is horizontal, i.e. left and right.
• Size – This is the paper size to be printed on.  Generally, you won’t have to change this for your own work.  However, it is something to watch if you receive a file from someone else, in particular, check to see if it is Letter (e.g. common in the USA) or A4 (e.g. common in Australia and New Zealand).
• Print Area – Use this option to manage the range of cells that get printed on this sheet. There are two options available: Set Print Area and Clear Print Area.  Select your range of cells to be printed before setting the print area.
• Breaks – You can insert, remove or reset the page breaks.  Normally I choose to use the Page Break Preview view to manage page breaks rather than this option.
• Background – You can add a background image to the sheet.  Please be careful about using this option as images can easily obscure the content on the sheet unless they are very faint watermark type images.  Once an image is inserted as a background image, then this button allows you to remove the image.
• Print Titles – Clicking this button opens up the Page Setup dialog box opened on the Sheet tab. The second section on this tab contains two options: Rows to repeat at top and Columns to repeat at left. This option is very useful in that it allows you to repeat your headings on each page without repeating this information on the worksheet itself.  To select the rows (or columns) you want to repeat, simply click in the field and then select the appropriate rows (or columns) on the worksheet.  The field displays the references corresponding to your selection.

Scale to Fit

• Width and Height – You can specify how many pages wide you want your printed output to be on and how many pages high.  If you specify the width and height then you don’t need to use the Scale tool.  But there are some things to note here. Firstly, if you want to adjust the margins then do this first as this will impact on Excel’s calculation of the amount of reduction required to make things fit.  Secondly, this can be used only to reduce things down less than 100%.  If you want to enlarge things then use the Scale tool. Thirdly, I noticed that Excel didn’t immediately respond to my request to make it one page wide (from two pages).  However, when I went to Print Preview I saw the impact of this change.
• Scale – You can adjust the size of the printed output, i.e. reduce (number less than 100%) or increase (number greater than 100%) the size.  If you have specified the number of pages using the Width and Height then it is not possible to use Scale unless you change both the Width and Height back to Automatic.

Sheet Options

You can turn on or off the gridlines (the lighter lines around each cell on the worksheet) and the headings (A, B and C for the columns and 1,2 and 3 for the rows).  If you are not sure what this looks like then deselect one or both View checkboxes. The Print checkboxes work in the same way except in the context of what prints out.  It is nice that these things can be controlled independently of each other.

 Page Setup Dialog Box

There are two main ways of accessing the Page Setup dialog box.

Access the Page Setup Dialog Box via the Page Layout Tab

Sometimes there is a button in the bottom right hand corner of a group of buttons on a tab.  Clicking this button displays a dialog box which gives you further options related to that particular group.

Clicking this button for the Page Setup group or the Scale to Fit group opens the Page Setup dialog box opened on the Page tab.  The button for the Sheet Options group opens the Page Setup dialog box opened on the Sheets tab.

 Access the Page Setup Dialog Box via the Print Preview Screen

The Page Setup dialog box can also be accessed from the Print Preview screen.

For Excel 2007 users, click Page Setup on the Print Preview tab.

For Excel 2010 users, click on the text link Page Setup at the bottom of the screen just to the left of the preview of your worksheet.

Recommendation: The Page Setup Dialog Box from the Page Layout Tab

There are two slight differences between the two Page Setup dialog boxes.  The one from the Page Layout tab has Print and Print Preview buttons on the Page tab, in contrast to the one from the Print Preview screen. Also some options on the Sheet tab can’t be updated with the one from the Print Preview screen, in particular the Print Area and Print Titles section.

Given the choice between the two, I would recommend the Page Setup dialog box accessed from the Page Layout tab due to the availability of more options, especially the Print Titles section.

Further Options in the Page Setup Dialog Box

There are four tabs on the Page Setup dialog box: Page, Margins, Header/Footer and Sheet.  Each tab has slightly more options on it than is available on the Page Layout tab or the Print Preview screen in Excel 2010.  For example, the Margins tab has a useful option to centre the page horizontally.  However, most of these extra options  rarely require any change from their default value so that in many cases you will not need to open up the Page Layout dialog box.  Nevertheless, it is useful to know how to access it so that you can tweak things as and when required.

Summary of the Page Setup Options in Excel

Some great options for having control over the printed output in Excel are:

• Go the Page Layout view and adjust the headers and footers from there.
• Go to the Page Layout tab and adjust common print settings from there.
• If you would like further Page Layout options then access the Page Setup dialog box either from the Page Layout tab (recommended) or the Print Preview screen.