How to print row and column headers of Excel spreadsheets on every page

Today I'd like to tell you about a small but important feature tucked away in Excel 2013 and its previous versions. In this article you'll learn how to make header rows and column headers print on each page.

If you often have to print large and complex Excel worksheets, I am sure that you face this problem as often as I do. I can easily scroll up and down through the document without losing sight of the column titles because I have the header row frozen. However, when I print the document, the top row is printed only on the first page. If you are sick and tired of turning the printouts back and forth to see what kind of data is in each column or row, feel free to find out the solution to the issue in this article.

Repeat Excel header rows on every page

Your Excel document turns out to be long and you need to print it. You go to the print preview and find out that only the first page has the column titles at the top. Take it easy! You can specify the Page Setup settings to repeat the top row on every printed page.

  1. Open the worksheet that you're going to print.
  2. Switch to the PAGE LAYOUT tab.
  3. Click on Print Titles in the Page Setup group.

    Click Print Titles on the PAGE LAYOUT tab to open the Page Setup dialog box

  4. Make sure that you're on the Sheet tab of the Page Setup dialog box.
  5. Find Rows to repeat at top in the Print titles section.
  6. Click the Collapse Dialog icon Select range next to "Rows to repeat at top" field.

    Click the Collapse button to get back to the worksheet and select the rows

    The Page Setup dialog window is minimized and you get back to the worksheet.

    You can notice that the cursor changes to the black arrow. It helps to choose an entire row with one click.

    Use the black arrow to select a row or several rows

  7. Select a row or several rows that you want to print on every page.
    Note: To select several rows, click on the first row, press and hold the mouse button and drag to the last row you want to select.
  8. Click Enter or the Collapse Dialog button again to return to the Page Setup dialog box.

    Select rows and click the Collapse button again to return to the Page Setup dialog box

    Now your selection displays in the Rows to repeat at top field.

    The reference means that the first row will be printed on every page

    Note: You can skip steps 6-8 and enter the range using the keyboard. However, pay attention to the way you enter it - you need to use the absolute reference (with the dollar sign $). For example, if you want to see the first row on every printed page, the reference should look like this: $1:$1.
  9. Click on Print Preview to see the result.

    Go to the print preview to see the worksheet before printing

There you go! Now you know exactly what the columns mean on every page.

Get a header column on every printout

When your worksheet is too wide, you'll have the header column on the left only on the first printed page. If you want to make your document more readable, follow the steps below to print the column with row titles on the left side of every page.

  1. Open the worksheet that you want to print.
  2. Go through steps 2-4 as described in Repeat Excel header rows on every page.
  3. Click the Collapse Dialog button to the right of the Columns to repeat at left box.

    Use the Collapse Dialog button to pick the columns to repeat at left

  4. Pick a column or columns that you want to see on every printed page.
    Select columns that you want to see on every printed page
  5. Click Enter or the Collapse Dialog button again to check if the selected range is displayed in the Columns to repeat at left field.
  6. Press the Print Preview button in the Page Setup dialog box to look at your document before printing.

    Preview your document before printing

Now you don't have to turn the pages back and forth to find what the values in each row mean.

Print row numbers and column letters

Excel normally refers to worksheet columns as letters (A, B, C) and to rows as numbers (1, 2, 3). These letters and numbers are called row and column headings. As opposed to row and column titles that are printed only on the first page by default, the headings are not printed at all. If you want to see these letters and numbers on your printouts, do the following:

  1. Open the worksheet that you want to print with the row and column headings.
  2. Go to the Sheet Options group on the PAGE LAYOUT tab.
  3. Check the Print box under Headings.

    Check the Print box under Headings on the PAGE LAYOUT tab to see the row and column headings on every page

    Note: In case you still have the Page Setup window opened on the Sheet tab, just check the Row and column headings box in the Print section. It also makes the row and column headings visible on every printed page.

     Check the Row and column headings box in the Page Setup dialog box

  4. Open the Print Preview pane (FILE -> Print or Ctrl+F2) to check the changes.

    Go to the print preview to check the worksheet before printing

Does it look as you desired now? :)

The Print Titles command can really simplify your life. Having header rows and columns printed on every page allows you to understand the information in the document more easily. You won't lose your way in the printouts if there are row and column titles on each page. Try it out and you can only benefit from it!

48 Responses to "How to print row and column headers of Excel spreadsheets on every page"

  1. Dinesh Patil says:

    Thats a Amazing.

    Thank u so much.

    this help for me.

  2. Anna V says:

    Hi,
    I would like to know if there is a way to repeat row and column titles not on each page, but some selected pages.

    Thanks,
    Abba

  3. Tom says:

    I've done this on several versions of Excel but in 2013 that optiion is grayed out.

    • Intan says:

      The same thing happened to me, but that was when I used Print>Page Setup>Sheet tab. That way the field is greyed out.

      But I tried using Ekaterina's method, using the Page Layout ribbon, that works well on my Excel 2013.

      Hope that helps.

  4. Shay Ben-Abu says:

    Thanks a lot! searched for it a lot of time!

  5. stenili says:

    Hi
    I still cannot repeat rows, the the rows to repeat field is greyed out.

  6. Phathead says:

    I'm with stenili. "I still cannot repeat rows, the the rows to repeat field is greyed out". Clicking on the Collapse Dialog icon does nothing. Odd how this worked for me last week in another file. Thanks.

  7. Anonymous says:

    Thank you very much for that

  8. M:Reza says:

    thanks alot

  9. Brenda Owen says:

    Thank you - was just what I wanted.

  10. jen says:

    I would like to have the columns repeat on every page break, as I have a years worth of recerts I am trying to input and have to keep going back to original page to see the column I am in.

  11. Donna says:

    If you have more than one workbook open. Close the other file, then it should work

    • Ken B. says:

      This is the first suggestion that worked. Why is it not possible to have multiple workbooks open and select rows to repeat at the top? Is there a technical reason this happens?

  12. Brittany says:

    Hi everyone. Excel "greys out" or does not let you select rows to repeat if you are in page break preview View. I changed my view to NORMAL and it allowed rows to be selected.
    Hope this helps!

  13. Robin says:

    I am in Excel 12.3.6. I follow the instructions exactly and lines 1 and 2 that I selected do not end up on each page. Any suggestions?

  14. Shannon says:

    The "greyed out" issue persisted until I only had one Excel file open. Once I closed the other workbooks, my print titles option opened up!

    • Jeff says:

      Thank you! Closing the other workbooks was key for me!

    • Ken B. says:

      This is the first suggestion that worked. Why is it not possible to have multiple workbooks open and select rows to repeat at the top? Is there a technical reason this happens?

  15. dip shekhar nath says:

    Thank you - soo much for that... ♥ ♥ ♥

  16. Raj says:

    Dear friends..Can any one please help me. I need the Header on the left side and footer on the right side (in Column)..Traditionally header and footer are on top and bottom and on top header u can align left, center and right..But I want the header to be vertical columns..please help me

    • Occam's Razor says:

      Take the printed document, insert into copy machine Top edge into the feeder, Tell the copy machine that the Left edge is in the feeder, then press Copy. Your headers will now be vertical columns.

  17. HoHoHo says:

    It's nuts, but really as simple as closing all the other files to get rid of the grey and then set the "Rows to Repeat".

  18. carol g says:

    yes, i agree. i spent hours doing research online and finally see this post about closing all other workbooks!!! microsoft needs to fix this bug in their programming!! thx for your help.

  19. Lee says:

    My Rows to repeat at top: is still grayed out. I've done this countless times for me to repeat rows and columns. Now for some reason it's won't work. Any other suggestions?

  20. Mohsin says:

    Really Informative Thanks Please Keep updates more tricks as we highly expecting from now.....

  21. sudipta says:

    Dear Friend,

    I want repeat a row 2 3 times in same page. How it 's possible.

  22. mohamed says:

    Add duplicate rows down the page when printing

  23. Anonymous says:

    I'd like to repeat information in a single cell (not a row or column; is there a way to do that?

    I'd appreciate your help.

  24. Anonymous says:

    I'd like to repeat information in a single cell (not a row or column; is there a way to do that?

    I'd appreciate your help

    Sorry, I meant to ask: I'd like to repeat information in a single cell (not an entire row or column) to show on all pages I want to print within the same worksheet.

    I'd appreciate your help

  25. Philip John says:

    I didn't have to close any workbooks but closing a page layout menu in another workbook worked.

  26. Md. Ikhtiar Rahman says:

    Hello There?
    Can you help me please?

    I'm Md. Ikhtiar Rahman, Asst. Account of the Company. My major work is every month salary making with over time, maintain attendance and etc. I work everything fine. But Some are formatting is very difficult (I don't know about that). I know and properly use Header and footer, Heading row repeat with Freeze Panes.

    Actually I need to know "HOW I MAKE HEADER ROW REPEAT WITH FOOTER ROW REPEAT" EVERY PAGES (EXAMPLE PAGES link :

    https://drive.google.com/open?id=0B2qWhLQblwjzRUx4WldDcDN4enM

    GREEN AREA IS HEADER AND FOOTER
    AND I NEED TO KNOW HOW I MAKE (RED AREA)

    Best Regards
    IKHTIAR

    • BERK says:

      ya i have the same problem. havebeen trying to solve for 2 weeks. I found out a code like this but now i need my write to fit the page, not left centre or right footer. Is there another way to do it or how can i compound the footers?? Thanks a lot.

      Sub berk()
      '
      ' berk Macro
      '
      Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
      Set Sh = Worksheets("Sheet1")
      Set Rng = Sh.Range("A166:K166")

      For Each c In Rng
      StrFtr = StrFtr & c & " "
      Next c

      ActiveSheet.PageSetup.CenterFooter = StrFtr
      End Sub

  27. Qader says:

    its really an amazing way to save our time. it really works
    Thanks a lot for sharing this useful information.

  28. Qader says:

    its really an amazing and useful way to save our time. it really works. thanx a lot for sharing this useful information.

  29. Swapnil says:

    Wonderful. Thank you so much for the information.

  30. Rina says:

    Thanks. It's really useful and helpful. Thanks again.

  31. Carrie says:

    Exactly what I needed - thank you!

  32. atiar says:

    Very helpful this tunes. thank you.

  33. jjowitzki says:

    GREAT JOB!!!

  34. THANGAMANI says:

    If i need two sub title means how can do. please explain

  35. Komali says:

    Thanks!!

  36. margy.gicheha@gmail.com says:

    help me to restart my mouse

  37. Daniel says:

    Very helpful, thank you!

  38. Leangseng says:

    Hello everyone,
    I want to set print headings or title on some specific pages not all page, so any solution i can do this?

    thanks

  39. Claire says:

    How to remove the header logo on every page which it is not necessary.
    Only the first page. How can I solve that issue?

  40. Jerry says:

    I have a problem where the next row is printing as part of the print title but it is not a selected row?

    Been searching everywhere but can't find where anyone else has had this problem.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard