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

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