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!
Related posts:
Freezing rows and columns in Excel
How do I keep my table headings over multiple pages in a Word document?
———————
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 …
Yousuf
December 30, 2013 at 10:56 am
thanks for the info
LikeLike
Liz at Libro
January 1, 2014 at 3:04 pm
You’re welcome – thanks for your comment and glad you found it helpful!
LikeLike
Steve
October 8, 2015 at 3:13 pm
Hi there, I’ve added row A to the top of every page, but id like it to emit the last (summary) page if possible. Is there a way to do this?
LikeLike
Liz Dexter
October 16, 2015 at 4:31 pm
Thanks for your question – unfortunately it’s not possible to do this as it’s to do with the printing of the spreadsheet rather than the set up of the spreadsheet itself.
LikeLike
Inder
February 14, 2015 at 11:21 am
Thanks for the correct info.
LikeLike
Mallikarjun
January 13, 2014 at 9:44 am
than you very much
LikeLike
Liz at Libro
January 13, 2014 at 10:14 am
You’re welcome!
LikeLike
Amy
January 23, 2014 at 4:57 pm
Is it possible to set up the “repeat rows” in the Page Set Up/Sheet but EXCLUDE certain pages? I have a worksheet that is 13 pages long, I would like to repeat rows 1 – 3 on all pages except the last as it is a summary of information. I was trying to avoid making the summary info a separate tab as the workbook is extensive with a fair amount of tabs already…. PS Using Excel 2010
Any insight is greatly appreciated! Amy
LikeLike
Liz at Libro
January 23, 2014 at 5:41 pm
Thank you for your question, Amy. I don’t think you can select a particular page for this not to happen on; I’m afraid I think you would have to add an extra tab. I will leave this open to other readers, though – if anyone knows how to do this, please comment below!
LikeLike
Christina
February 11, 2014 at 3:44 pm
Hi Liz, Is there a way to have this command apply to the entire workbook? I’m in a book with more than 35 sheets and I would like it to do the same throughout all of them, hopefully without manually doing each one. Thanks!
LikeLike
Liz at Libro
February 11, 2014 at 4:21 pm
I’ve just had a search online and I can’t find anything that suggests you can do this across multiple sheets in a workbook – sorry! If you find out that you can, please pop back to let me know and I’ll add it to the post.
LikeLike
DeSilva, Christina
February 12, 2014 at 3:39 pm
Thank you for your quick feedback! Flagging this email in case I ever find a way! ☺
Christina
Christina DeSilva, Risk Management Analyst
Willis Group, Human Capital Practice
Direct: 212-915-8625 Cell: 917-618-5988 Fax: 212-519-5422
LikeLike
Liz at Libro
February 12, 2014 at 4:09 pm
You’re welcome – I’m always so glad when people feel able to interact with my posts and ask questions! I’ll keep an eye out for a solution, too!
LikeLike
bill russell
April 29, 2014 at 2:31 pm
Hi Liz is it possible to print out the column to the left of the A column ie the actual numbers down the left of the spreadsheet
LikeLike
Liz at Libro
April 29, 2014 at 2:38 pm
Hi Bill, thanks for your comment, and yes it is in Excel 2013:
Page layout tab – sheet options. Under Headings, tick the Print box. Or in Page Setup, you can tick where it says Print – Row and Column headings. Or you can do this when in the process of setting a document up to print.
This works in Excel 2007, 2010 and 2013
I will publish a new post soon with instructions and screenshots – watch this space!
Best wishes,
Liz
LikeLike
Lesley Young
July 22, 2014 at 10:35 pm
Liz,
Thank you for all your help. I was about to throw my computer out the window and then I saw your instructions. You have been a great help to me.
Lesley
LikeLike
Liz at Libro
July 23, 2014 at 9:27 am
You’re welcome, Lesley – I’m glad I could help and appreciate you taking the time to drop by and say thank you!
LikeLike
Patel
August 7, 2014 at 6:36 pm
Thanks for posting this! It was very helpful : )
LikeLike
Laurence
October 1, 2014 at 6:35 pm
You’re awesome! Informative and to the point. Thank you. I’ll be bookmarking your page I hope you don’t mind. 🙂
LikeLike
Zezo
November 9, 2014 at 9:27 am
Thank you so much 🙂 keep up the good work.
LikeLike
Liz Dexter
November 9, 2014 at 11:43 am
You’re welcome, glad I could help!
LikeLike
heystellla
November 17, 2014 at 3:21 pm
Thank you!
LikeLike
regie
November 25, 2014 at 3:00 pm
I have 3 pages (when printed) excel file and I used a repeat column header to have the same header on every page. On my third page I don’t want this header appear. How can I do it?
LikeLike
Liz Dexter
November 25, 2014 at 3:06 pm
If you don’t want the header to appear on the third page, you’re going to have to put that page on another sheet in the spreadsheet, because unfortunately you can’t pick and choose which pages you want this to work with. I’m struggling to understand why you’d want to do this, though, so if you could explain what you’re actually trying to do, I might be able to help further …
LikeLike
Joey
December 16, 2014 at 3:51 pm
THANKS A BUNCH. WISH I COULD HUG YOU
LikeLike
Liz Dexter
December 17, 2014 at 6:40 am
Ha! Thank you, glad I could help!
LikeLike
mohan
February 27, 2015 at 11:44 am
Thanks a lot
LikeLike
Irma
April 7, 2015 at 5:56 am
Very nice clear instructions!!! Thank you so much!
LikeLike
Thia
April 22, 2015 at 9:26 am
quick and easy way to lear . Thank you
LikeLike
H
April 22, 2015 at 2:11 pm
Thanks! It helped! Office work is BOOOOOOOOOOOOOOOOOORRRRRRRIIINNG though
LikeLike
Manish Sharma
May 1, 2015 at 8:33 am
Thanks it is very helpful
LikeLike
Danielle
May 11, 2015 at 6:25 am
Hi there. Was wondering if you could help me with separating a heading over 2 rows of header row in a table. At the moment I am spacing between words to have them wrap but this is messy. The only other option is to narrow the column to make it wrap but this doesn’t suit. Then when I reference the field in formula’s I have to count the spaces between words etc….
LikeLike
Liz Dexter
May 11, 2015 at 7:53 am
Thanks for your question. I’m not entirely sure what you’re trying to do, I’m afraid. Can you explain what you’re trying to get to?
LikeLike
Laurie
November 11, 2015 at 5:41 pm
Hi there. What if I have several multiple page tables in one worksheet and each table has a different heading? I guess I could put each table in its own worksheet, but that is a lot of work. thanks for any advice you can provide.
LikeLike
Liz Dexter
November 11, 2015 at 5:44 pm
I’m afraid you will have to put each in its own sheet, as this is a function of printing rather than excel itself, so it doesn’t intrinsically know which are headers and which to choose to print on each page (sorry!).
LikeLike
Eli
November 16, 2015 at 8:13 am
i love you! thank you so much!!
LikeLike
Liz Dexter
November 16, 2015 at 8:48 am
Hehe – you’re welcome! Glad to inspire love!
LikeLike
Elliot
December 23, 2015 at 10:15 pm
Thank you! Been wondering for awhile. Finally used google as a resource. Easy steps to follow. Cheers!
LikeLike
Minhaj
June 6, 2016 at 6:09 am
It’s helpful so much. Thanks a lot.
LikeLike
ted
August 29, 2016 at 12:44 am
I cannot print the instructions that you have on this page=how I print out table headings etc. how do I do it.
LikeLike
Liz Dexter
August 29, 2016 at 5:38 am
If you want to print out the page from the Web, you should be able to fine a symbol that looks like three horizontal lines stacked vertically in the top right of your browser screen. Click there and you’ll have the option to Print. This works in Firefox and Chrome, which I have open at the moment myself. Or you can highlight all the text, right-click and copy then paste into Word and print from there. I hope that helps and is what you were asking.
LikeLike
Vumpa
September 13, 2016 at 9:14 am
I was great ! it’s really helped me.
LikeLike
Felix
September 22, 2016 at 9:32 am
Hallow Liz! is their a way i can exclude my last two pages such that they are not printed with collumn headings or label? Kindly asist
LikeLike
Liz Dexter
September 22, 2016 at 9:35 am
I’m not sure why you would want to do this, but unfortunately, you can’t exclude certain pages. You could add those pages to a new tab in your spreadsheet and print them separately, as a workaround. I hope that helps!
LikeLike
Hidayah Hidi
December 15, 2016 at 2:51 am
Thank you Liz 🙂
LikeLike
Jim
November 2, 2017 at 1:00 pm
Hi, Can you suggest, how can I learn making tables and charts in Excel (KPI)?
LikeLike
Liz Dexter
November 2, 2017 at 1:07 pm
I’d take a look at YouTube for instructional videos as a good first step. I hope that helps.
LikeLike