May
24

How to freeze header rows in Excel

To freeze or not to freeze

With new Excel capacity to hold a million rows and numerous columns with numbers, it is easy to lose the connection between the value and its meaning on line 26935. This is one of the reasons Excel keeps the commonsense "Freeze" feature for you. It allows you to keep track of the data you are looking at without having to scroll back and forth to see the column or row labels.

So, what's the button to click, and what are the pitfalls? Step number one is to look at your data and see if you need to lock just one row with the labels, several top rows, or columns.

How to keep your labels visible

If you have a usual table with one header row, the steps are very easy:

  1. Scroll to the top and make sure you see the row with the labels. Otherwise you'll quickly notice one peculiarity - this option does exactly what it promises, it freezes the top visible row.
  2. Open the View tab in Excel and find the Freeze Panes option in the Window group.
  3. Click on the little arrow next to it to see all the options, and choose to Freeze Top Row (or to freeze the first column, depending on the way your data are organized).

Freeze Top Row or First Column

Whenever you lock rows or columns, the Freeze Panes option turns into Unfreeze Panes for you to quickly unlock the row or column.

Lock several top rows, get hold of the columns

More and more often I come across tables that have several header rows. Those are complex structures, but they help you have some context to better understand the data. The question of freezing several rows can also come up when you compare one set of data to another, a couple of thousands rows away. The "Freeze top row" feature is obviously not very useful in this case. But the "Freeze panes" option is!
Here is how you do it:

  1. This moment is the key - select the cell just below the rows you want to freeze, and to the right of such columns if needed.
  2. Open the View tab in Excel and find the Freeze Panes option in the Window group.
  3. Click on the little arrow next to it to see all the options, and choose to Freeze Panes.

Freeze Panes option lets you lock several rows and columns

As always, this is not the end of the story. Because just like some users on this forum, one day you may find out it simply doesn't work for you. This may happen because you already have the top row locked. I don't know if I can call it a trick, the simple solution (and one thing to remember) is to unfreeze it first.

If any of the rows or columns are locked in the table, you'll see Unfreeze panes in place of the Freeze panes option. Peek at the option name before trying to lock the rows, and everything will work as you need it to.

Unfreeze the rows or column

Use this little handy feature to have the headers visible when you scroll and always know what figures you are looking at.

You may also be interested in:

Show entire row - try this free tool to see row of any length as a card, with data and headers side-by-side.

30 Responses to "How to freeze header rows in Excel"

  1. Kevin says:

    I get how to "freeze pane" to lock down the header row, while I'm in the spreadsheet. My question is, How do I keep it that way, so when I send it to others that open it, the header row remains frozen?

    • James says:

      Using "Split" right next to the "Freeze Panes" option will allow you to have the same spreadsheet in different views, so you would be able to move the split so that you can only see the first row on one of the split screens, while still being able to scroll through the other version of the sheet.

  2. Bridget says:

    Thank you for the information!

  3. Abdul sani crc says:

    Very interested. I have tested it and it works im now enjoying it. Thanks

  4. abdul rehman says:

    I am facing a problem in attendance sheet, i need visible top row and left column while scrolling. page left and right up and down ,using excell 2013

    thanks

    • Hello Abdul,
      When you click "Freeze panes", it freezes all rows above the selected cell and all columns to the left of it.
      Just select the cell to the right of the column you want to keep visible, and right below the row before clicking the button. E.g. select cell B2 to always have row 1 and column A on the screen.

  5. Phil says:

    Thank you, thank you, thank you!!! Super helpful information!

  6. Mila says:

    I was wondering how to freeze the first row but with a bunch of the comments, that are included in the first row? When I do it like you decribed previously, only first row of the comment is freezing but not the rest of it.

    • Hello Mila,
      Thank you for your question.
      This issue in Excel doesn't have a simple solution, but there is a workaround you can use.

      Click on the cell with a comment, go to Data tab and click on Data Validation. You can enter the information from the comment on the "Input Message" tab and select the check box "Show input message when cell is selected". Now whenever you click on the cell in the frozen row, this message will appear. You can move the box to any location you prefer. There is a nice video that describes the process:
      https://www.youtube.com/watch?v=D1-IuXmiSAI

      I hope you'll find this helpful.

  7. Sandeep says:

    Thank you

  8. Sultan says:

    Thank you so much. and thanks Google for leading me to you...

    may all your dreams come true

    wish you all the best...

    keep up helping ppl you beautiful lady

  9. Anonymous says:

    Thank you so much it worked well

  10. Danielle says:

    I have my top row frozen but as soon as I sort and filter then my frozen top row ends up somewhere other than the top. How do I keep it up at the top?

    • Hello Danielle,
      Please make sure you select the check-box "My data has headers" when sorting; you can see it if you select the "Custom sort" option on the Home tab or click on the "Sort" icon on the Data tab instead of clicking the standard Sort A->Z options.
      If this doesn't help, could you please specify what version of Excel you have and what steps you follow? I'll do my best to assist you.

  11. erin says:

    If I have the first 2 rows frozen, how can I make the first counted row start AFTER the frozen rows? I want the frozen rows NOT to be counted, and for my first counted row to start counting after the frozen row (that is, i want the third row to be the first counted row and say 1 on it, not 3). Thank you.

    • Hello Erin,
      I'm sorry, but there is no way to change the standard headings in Excel. You can only disable them on the View tab. If you want to number the rows below your headers, you can create an additional column and either start entering the numbers where you need, or use a formula in A3, e.g.
      =ROW()-2

      and copy it down.

  12. Firstlady says:

    Please if i want to numbering a large volume of data without having to do it manually what could it be done with

  13. Firstlady says:

    What work can be used when you to passwords a document

  14. apple says:

    hi! i'm having a hard time with this freeze panel thing.i hope you can help me with this, coz everytime i open my file, i have to freeze them again..thanks in advance..

  15. RAVIRAJ says:

    THANK YOU IRINA

  16. Taindra LImbu says:

    Thanks for the amazing idea...
    so happy :)

  17. Taindra Limbu says:

    Thank you so much once again. I learned even more from all of yours comments. it helped me a lot
    thank you so much

  18. SRK says:

    Thank you sooooo much love you

  19. Meena says:

    Thanks alot

  20. Elize Mostert says:

    Thank you it worked :)

  21. krish says:

    Thanx!! It helps a lot....

  22. Kelly says:

    I am using Excel 2016 for Mac. I have a workbook set up with 12 monthly columns with a grand total column and an avg per month for each row (employee). I also have company wide totals at the end of each month column and then the avg for each month. The totals are unaffected by re-sorts, as are the row averages, but when I re-sort, my avg on each column gets moved up into the table. I had thought I could move the column avg to the top and then freeze the panes but they still get re-sorted. My header row with the month names is unaffected. Is there a way to set the column avg row as a header row as well, so they will remain at the top regardless of the sort?

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 for Excel Professionals
 
 
40+ 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