Practical issue: Gain better control over the spreadsheet elements you print out [1]
Solutions: Deciding what to print [2]
Print areas [3]
Print titles [4]
Headers and footers [5]
Fitting the content on to the page [6]
Print options [7]
Print Preview [8]
Further reading [9]

By default, Excel will print the entire contents of a worksheet, but there are a number of Page Setup and Print options to select and fit your print outs onto a specified number of pages. Print Preview is a helpful tool that can also reduce paper and toner wastage, writes Simon Hurst.

Practical issue: Gain better control over the spreadsheet elements you print out

Much of the look of any printed Excel output will depend on the formatting [10] of your spreadsheet. However, Excel's Page Setup and Printing tools can be used to control the areas of each sheet that are to be printed, add headers and footers and also Print Titles that use the existing sheet title rows and columns. In addition, there is a very useful Scaling option that allows you to shrink your print area to fit a designated number of pages wide and tall. Print Preview helps reduce paper (and ink/toner) wastage by letting you see what your output will look like before sending it to the printer.

Solutions - Deciding what to print

Print areas

If you have not set a specific Print Area for a worksheet, Excel will print the entire contents of the sheet. Use View, Page Break Preview (Excel 2007: View ribbon tab, Workbook Views section, Page Break Preview) to display the area of the sheet that will be printed together with an indication of what will print page by page. Here we can see a worksheet with no print area in page break preview, showing what will print on each page. The blue lines can be dragged to control what will be printed on each page. In this case we have dragged the page break upwards:

To set a print area, select one or more blocks of cells and then use File-Print Area-Set Print Area (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Area dropdown, Set Print Area) . Here we have selected three different blocks of cells by holding down the control key while selecting the second and third block. We have then set the print area. An existing print area can be extended or reduced by dragging the page break line as described above.

Excel 2007 includes an additional option in its Print Area dropdown - Add Print Area. If you have already set a print area and want to add another, separate area to it, then select the additional area or areas and you should find a Print Area dropdown option to 'Add to Print Area'.

Versions of Excel prior to 2007 don't appear to include an Add to Print Area option (or does someone know better?). If you do want to add a separate area to an existing print area, then use Edit-Go to-Print_Area to select the current area or areas, then select the additional, separate area by dragging over it while holding down the control key. This will create a multiple selection including your original and new print areas. You can then use Set Print Area to set this combined set of areas as the print area:

To return from Page Break Preview to your normal view, choose View-Normal (Excel 2007: View ribbon tab, Workbook Views section, Normal) .

Print titles

When the contents of a sheet will be printed over several pages, you may want to include the topmost row or rows and the leftmost column or columns as titles on each printed page. To do this, go to File-Page Setup and choose the Sheet tab (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Titles) . In the Print titles area you can click in the "Rows to repeat at top:" and/or "Columns to repeat at left:" text boxes and then click on a row or column, or drag across several rows or columns, to use them as titles on each printing page.

Here we have set row 1 and columns A and B as the print titles for printing our sheet:

Note that the Print titles section of the Sheet screen is only available when it is accessed directly from File-Page Setup (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Titles) - not when you get there from the Print Preview, Setup button.

Headers and Footers

Like Print Titles, Headers and Footers are repeated on each page of the printed output, and are set for each sheet. From the File, Page Setup screen shown above, choose the Header/Footer tab. (Excel 2007 either use the Page Layout ribbon tab, Page Setup section, Print Titles option and then switch to Header/Footer or, from the same section, click on the Page Layout dialog button at the bottom right) .

You can choose from some built-in Headers and Footers by choosing from the Header and Footer dropdowns or you can click on the Custom Header and Custom Footer buttons to type in your own text in one of the three sections: Left, Center or Right. You can also use the buttons provided to enter page numbers, date and time information, file path and name details, the sheet name and, for Excel XP and later, a picture which you can also format using the "Format Picture" button. You can insert no more than one picture in each section. Often, the easiest way to create your custom header or footer will be to choose a similar built-in version and then use the custom button to edit it.

Fitting the content on to the page

The Page section of the Page Setup screen includes the Scaling settings. You can either set the print Adjust to: option from 10% to 400% of the normal size, or you can use the Fit to: dialogue to condense the contents to fit on the designated number of pages wide by tall. Here we have chosen to fit the contents to 2 pages wide by 10 tall which has resulted in a reduction to (a rather difficult to read!) 23% of normal size.

Print options

Having sorted out what you want to print on each sheet, and at what size, when you choose File-Print (Excel 2007: Office Button, Print) you can then choose whether to print the active sheet or sheets, the entire workbook, or just any currently selected area or areas. If you select more than one sheet and choose "Active sheets" or "Entire workbook" then any page numbering included in the headers or footers will be continuous across all the printed pages.

Print Preview

It is almost always worth checking your potential printed output by previewing it before sending it to the printer. The Print screen described above includes a Preview button, or you can go directly to Print Preview via File-Print Preview (Excel 2007: Office Button, Print option, Print Preview) . The Preview screen includes buttons to Print, access the Page Setup options, display the margins so they can be viewed and repositioned as well as Zoom, navigation and close buttons.

Further reading

Use Excel's built in templates to set up standard printing options [11]
Include watermarks in an Excel print out [12]
Include the contents of a worksheet cell in the header and footer area [13] and also use "&&" to include a single ampersand
ExcelZone Compendium Archive - further articles and queries on printing [14]

Subscribe to the ExcelZone newswire
[15]To keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function [16] will take you back to the AccountingWEB home page after it adds your name to the subscription list.