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.

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

11 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


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

      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

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
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
Sheila Blanchard