Our Expert Consultants often produce formatted print-ready spreadsheets. Sometimes you want the same cells to appear at the top of every printed page e.g. the column headers above a lengthy table. In most Microsoft Office products, this trick is performed using headers. Although these can be used in Excel, they are only appropriate for file information such as the table's creator, the company's logo, or a file name. You cannot include cell content in a header. The solution is to use the Print Titles function.
Let's say we wish to repeat Rows 1 and 2 on every page of our final report (1). The logo could be included in a conventional header, but the column heading in A2 could not be. The first step is to seek out the Print Titles function from the Page Layout ribbon (2). It is not essential to select your desired rows beforehand.
Clicking Print Titles brings up the general Page Setup dialog box with which you may be familiar from earlier versions of Excel. If you were to navigate away from the Sheet tab, you would have the ability to adjust margins and the number of pages you wished to print the document over. As you wish to repeat rows, click on the selection icon on the far right of the 'Rows to repeat at top' box (2).
The Page Setup box will become hidden and you will now see Microsoft's standard Excel selection tool. To select a row, you simply need to click in it. To select Rows 1 and 2, you will need to click and hold as you drag the cursor over both rows in your spreadsheet. The selected rows will then be displayed in Excel's formula notation (3). In this case, $1:$2. Note that instead of physically selecting rows, you can simply type the row numbers using $x:$y notation if you are familiar with it. Once rows are entered, you should click on the icon to the far right of the selection box to return to the Page Setup box (3). Click OK to complete the task.
You may wish to view the effect of your changes. As the header rows are only duplicated for printing purposes, this has to be done by looking beyond the first page on Print Preview. A common issue would be that there is either too much, or too little, white space at the top of your continuation pages. Including a blank row in your Print Titles will duplicate it to the top of all pages so you should always consider whether that is desirable. Finally, it should be noted that you can follow an almost identical procedure to duplicate columns on the left-hand side of printed documents, except you need to select 'Columns to repeat at left'.
