Objective: to make it easier to get the right content in the right place on the printed page.
Many Excel workbooks are probably designed without any consideration for printing the final result. After all, much of the power of a spreadsheet lies in its interactivity.
However, a large number of spreadsheets do have a printed result as the intended outcome, and for these a detailed knowledge of how Excel deals with page breaks, headers and footers, and pagination can save a lot of irritation and wasted time.
Page breaks and views
By default, an Excel worksheet doesn’t show where it will be split onto different physical pages when printed. Switching from Normal view to either of the other main view types displays this information.
The Excel view can be changed either by using the buttons next to the zoom control in the Status Bar, or by using the equivalent options in the Workbook Views group of the View Ribbon tab:
In the above example we have selected Page Break Preview View. Our page breaks are shown as dotted blue lines that we can drag left and right and up and down to change the positioning of the page breaks. Note that increasing the amount to include on a printed page will result in the content being scaled down by a percentage in order to fit. Returning from Page Break Preview to Normal View retains the page break lines, but without the ability to drag them to new positions.
The other view was introduced in Excel 2007. This is Page Layout and is similar to Print Layout view in Word in that it displays your worksheet as it will be printed on the physical page, complete with margins and sheet headers and footers:
The Page Layout screen includes horizontal and vertical rulers that show the margin position. The margin can be adjusted directly by dragging it left/right or up/down in the ruler. You can also click in the header and the footer areas. When you do so, the Design Ribbon tab will be displayed with the Header and Footer Elements group including the range of commands allowing you to enter date and time, page numbers, file and sheet information, and pictures in each of the three sections. The Header and Footer dropdowns in the Header & Footer group, include a range of pre-built header and footer options.
Where a single table of information is split over several printed pages, it’s usually necessary to ensure that row and column headings are included on each page. This is achieved using Sheet Titles. The Page Setup screen (Page Layout Ribbon tab, Page Setup group, Dialog button) includes a Sheet tab which allows you to set the various sheet options for printing, such as including row and column headers, gridlines and comments.
You can choose which rows and columns to repeat on each printed page by clicking in the appropriate text box and dragging through the required rows or columns either in the row or column headers or in the worksheet cells themselves. In this example, we have set row 1 and columns A and B as sheet titles and we can see that they are repeated on every printed page:
We often don’t need to print everything on a sheet. We can define the area that we do want to print using the Print Area command in the Page Setup group of the Page Layout Ribbon tab. First, select the cells to be printed and then click on the Print Area button to set this area as the printable area for that sheet.
This dropdown can also be used to clear an existing print area so that the full sheet will print – there is no need to clear an existing print area before setting a different one. The print area is set up as a named range called Print_Area, limited in scope to that worksheet, so selecting Print_Area from the Name box dropdown on any sheet will select the Print_Area so that you can see the range of cells that it includes.
The File, Print screen includes an option in the dropdown at the top of the Settings section to ignore any Print_Area at the time of printing.
Often the most significant issue when printing an Excel worksheet is getting it to fit on the right number of pages. Usually, the easiest way to achieve this is to use the Scaling options. The Scale to Fit group of the Page Layout Ribbon tab includes the ability to set a defined scaling percentage, but it is usually easier to define the number of physical pages wide and high that you wish to print on.
To fit the entire printable area on a single piece of paper you would set both Width and Height to 1 but, if you know that you want to fit all columns in a single page width, but allow rows to print on multiple pages, you can just set the Width option and leave the Height set to Automatic.
Where have my Print Preview margins gone?
If you were used to seeing and adjusting your page margins in Print Preview, you might have been disappointed to see that the standard File, Print option doesn’t show the margins. However, there is a Show Margins command in the bottom right-hand corner of the Print Preview screen which will display adjustable margins:
Alternatively, you can add the Print Preview Full Screen command to your Quick Access Toolbar to enable you to display the ‘old’ Print Preview screen.
Note that the File, Print screen now combines the print preview with all the printing options and also includes a link to the Page Setup options.
Printing areas from different worksheets on one page – the Excel camera
One seemingly insurmountable problem is how to print sections of several different worksheets on the same piece of paper. In fact, this can be achieved by using Excel’s camera tool which is now, rather less romantically, available as the Paste, Linked Picture command. You can only take pictures of cells, so if you want to include an object, such as a chart, you need to select the area of cells surrounding it, rather than the chart object itself.
Once you have selected the cells to capture as a picture, copy them and then use Home Ribbon, Clipboard group, Paste dropdown, Linked Picture command to paste a dynamic picture of your cell range onto another sheet. You can assemble multiple pictures on one sheet:
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.