RSS

How do I print out table headings at the top of every page in Excel?

29 Aug

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.)

1 spreadsheet

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)

1 view menu

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:

2 no headers

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:

3 page layout menu

The Page Setup menu will default to showing you the Page tab. Click on the Sheet tab at the extreme right:

4 page setup menu

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):

5 page setup menu sheet

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.

6 page setup menu sheet choose

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.

7 done

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

 
53 Comments

Posted by on August 29, 2013 in Errors, New skills, Short cuts, Word, Writing

 

Tags: , , , , , , , ,

53 responses to “How do I print out table headings at the top of every page in Excel?

  1. Yousuf

    December 30, 2013 at 10:56 am

    thanks for the info

    Like

     
    • Liz at Libro

      January 1, 2014 at 3:04 pm

      You’re welcome – thanks for your comment and glad you found it helpful!

      Like

       
      • 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?

        Like

         
        • 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.

          Like

           
    • Inder

      February 14, 2015 at 11:21 am

      Thanks for the correct info.

      Like

       
  2. Mallikarjun

    January 13, 2014 at 9:44 am

    than you very much

    Like

     
  3. 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

    Like

     
    • 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!

      Like

       
  4. 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!

    Like

     
    • 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.

      Like

       
      • 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

        Like

         
        • 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!

          Like

           
  5. 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

    Like

     
    • 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

      Like

       
  6. 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

    Like

     
    • 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!

      Like

       
  7. Patel

    August 7, 2014 at 6:36 pm

    Thanks for posting this! It was very helpful : )

    Like

     
  8. 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. 🙂

    Like

     
  9. Zezo

    November 9, 2014 at 9:27 am

    Thank you so much 🙂 keep up the good work.

    Like

     
    • Liz Dexter

      November 9, 2014 at 11:43 am

      You’re welcome, glad I could help!

      Like

       
  10. heystellla

    November 17, 2014 at 3:21 pm

    Thank you!

    Like

     
  11. 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?

    Like

     
    • 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 …

      Like

       
  12. Joey

    December 16, 2014 at 3:51 pm

    THANKS A BUNCH. WISH I COULD HUG YOU

    Like

     
  13. mohan

    February 27, 2015 at 11:44 am

    Thanks a lot

    Like

     
  14. Irma

    April 7, 2015 at 5:56 am

    Very nice clear instructions!!! Thank you so much!

    Like

     
  15. Thia

    April 22, 2015 at 9:26 am

    quick and easy way to lear . Thank you

    Like

     
  16. H

    April 22, 2015 at 2:11 pm

    Thanks! It helped! Office work is BOOOOOOOOOOOOOOOOOORRRRRRRIIINNG though

    Like

     
  17. Manish Sharma

    May 1, 2015 at 8:33 am

    Thanks it is very helpful

    Like

     
  18. 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….

    Like

     
    • 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?

      Like

       
  19. 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.

    Like

     
    • 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!).

      Like

       
  20. Eli

    November 16, 2015 at 8:13 am

    i love you! thank you so much!!

    Like

     
    • Liz Dexter

      November 16, 2015 at 8:48 am

      Hehe – you’re welcome! Glad to inspire love!

      Like

       
  21. 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!

    Like

     
  22. Minhaj

    June 6, 2016 at 6:09 am

    It’s helpful so much. Thanks a lot.

    Like

     
  23. 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.

    Like

     
    • 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.

      Like

       
  24. Vumpa

    September 13, 2016 at 9:14 am

    I was great ! it’s really helped me.

    Like

     
  25. 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

    Like

     
    • 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!

      Like

       
  26. Hidayah Hidi

    December 15, 2016 at 2:51 am

    Thank you Liz 🙂

    Like

     
  27. Jim

    November 2, 2017 at 1:00 pm

    Hi, Can you suggest, how can I learn making tables and charts in Excel (KPI)?

    Like

     
    • 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.

      Like

       

I love hearing from my readers - do please leave a comment!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: