I recently wrote about retaining your headers across multiple pages in a Word document. This post will tell you how to make your header rows (or columns) in Excel 2007 and Excel 2010 print on multiple pages when you print them out.
What is the header row in Excel?
The header row is the row in a spreadsheet that contains the headings for all of the columns in your spreadsheet. It’s usually Row 1, or maybe a few rows at the top of the spreadsheet.
(If your columns have headings in Column A instead or as well as your rows, you can use all of the stages in this post but choosing the columns option where appropriate, or both.)
Why would I want my header rows to print out on multiple pages?
If you’ve got a complicated spreadsheet that you want to print out in a report, as a handout, or as a pdf, it’s useful to have the header row show on every page. When you’re using Excel itself, you can freeze the rows and/or columns so you can see them as you scroll down. But this doesn’t carry over to the printout.
How do I check if my header row will be printed on every page?
To check what the printout will look like, you need to change from the standard Normal view of your document (see the first image in this post) to the Page Layout view.
To do this, choose the View tab, then the Workbook Views section and press the Page Layout button. Your view will change to what the document will look like on the page (this is also where you add headers and footers to an Excel document – more on that another time)
If we scroll down to the second page of our document in this view, we can see that the second page just starts with the next line of the spreadsheet – not very useful if you want to be able to see the headings at the top of each page:
How do I make the heading row print at the top of every page?
Staying in the Print Layout view, choose the Page Layout tab and look at the Page Setup section. In the bottom right corner, you’ll find a little arrow. Click on the arrow to access the Page Setup menu:
The Page Setup menu will default to showing you the Page tab. Click on the Sheet tab at the extreme right:
At last we’re in the Sheet menu. This is where you can choose the print area, titles, gridlines, quality, etc., but what we’re interested in is Rows to repeat at top (and/or Columns to repeat at left, if you have either or both of these):
Now, how do you tell Excel which row you want to print at the top of every page? I got a bit flummoxed by this at first, I have to admit. Here’s how you do it:
Make sure your cursor is in the appropriate input box – in this case I have left-clicked with the mouse in Rows to repeat at top.
Then click with the mouse on the far left of the row you want to select. Can you see the dotted line round it on the image below? That means that it’s been selected. If you just click on one row, $1-$1 will appear in the text entry box. If you highlight more than one row, it will read $1-$2, etc.
If you want to make sure that a heading column appears on every page of your printout, make sure the cursor is in the Columns to repeat at left box and click above the column you want to choose. You can choose a row(s) and a column(s) if you want to!
Once you’ve clicked on OK, you can scroll down in Page Layout view to see the top of the second page. There are the headings, ready to print on every page! You can change back to Normal view: the instructions that you’ve given Excel here will stay the same.
We’ve learned how to make sure that your heading row (or column) prints on every page of your printout when you’re printing out your Excel 2007 or Excel 2010 document.
If you’ve enjoyed this post, please do share it using the buttons below!
This is part of my series on how to avoid time-consuming “short cuts” and use Microsoft Office in the right way to maximise your time and improve the look of your documents.
Please note, these hints work with versions of Microsoft Excel currently in use – Excel 2007 and Excel 2010, for PC. Mac compatible versions of Excel should have similar options. Always save a copy of your document before manipulating it. I bear no responsibility for any pickles you might get yourself into!
Find all the short cuts here …