How to Freeze Cells, Rows, and Columns in Excel Using “Freeze Panes“
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
I bet you’ve been working in a spreadsheet that had so many rows, that as you scrolled down, you couldn’t see your headers?
Remember the frustration of not remembering which column was what?
It sucks!
Well, good thing you stopped by because I’m going to show you the solution 🙂
It’s called “freeze panes” and it’s super easy to use.
I’ll show you everything there is to freezing panes in this guide. Be sure to bookmark it, it’s a huge guide!
Table of Content
- Freezing Rows and Columns
1: How to freeze the top row
2: How to freeze multiple rows
3: How to freeze columns
4: How to freeze the first column
5: How to freeze multiple columns
6: Freeze columns and rows at the same time - Solving Common Pain Points
7: How to unfreeze panes
8: Freezing panes versus “Splitting Panes”
9: Freeze Panes is disabled! - Freeze Panes Tips for Advanced Excel Users
10: Prevent hidden rows or columns
11: Did Excel freeze the wrong panes?
12: How to hide the freeze panes line
13: Alternative to freeze top row? A table!
Free video on Freeze Panes
Watch my video and learn how to freeze multiple rows and columns in less than 4 minutes.
Prefer text over video? Then continue below!
How to Freeze the Top Row
Let me introduce you to the Freeze Panes feature.
This is one of the coolest features Excel offers!
It’s found in the Window group on the View tab in the Ribbon.
There are quite a few options with the Freeze Panes feature.
Right now, we will focus on the Freeze Top Row selection in the drop-down list.
Now that you see where to find this feature, let’s look at putting it to use!
Let’s consider the case where you run 5 hot dog stands on a few city blocks. You have a worksheet listing quarterly sales from Q1 2013 through Q3 2016.
Your worksheet contains over 500 rows of 10 different data points (columns).
When you scroll below row 25, you better have memorized your headers if you want to know which column is what *drumroll*…
Okay, I’m just kidding… you don’t have to memorize anything!
You are about to learn how to (easily) freeze your header row in place.
Begin by going back to the View tab. In the Window group click the down arrow by Freeze Panes and select Freeze Top Row.
Now scroll down your worksheet and note that your header row never goes out of view!
How simple is that, right?
In the picture below, the worksheet is showing row 546 as the topmost row of data while row 1 is shown as the top row! Mission accomplished 🙂
How to freeze multiple rows
Let’s say you want to do some quick comparison of your data.
In this case, you want to compare a single hot dog stand and ProductID.
In this example, we’ll go with 1001 and A10011.
Also, let’s isolate things to ‘2013’ in the Year column.
In this case, you will need to let Excel know how many rows to freeze by highlighting cell A6.
The next step is to go back to Freeze Panes and choose Freeze Panes this time instead of Freeze Top Row.
This will now freeze all the rows above row 6.
Now you can scroll through the data while the four rows of data for StandID ‘1001’, Year ‘2013’, and ProductID ‘A10011’ remain visible.
Same goes for the header row!
Also, now there’s a visible (horizontal) line between row 5 and the rest of the worksheet rows. This line marks the frozen pane.
How to Freeze Columns
Much in the same way that you can freeze rows of data in Excel, there is also a method to freeze columns.
You’ve already learned how to freeze rows. This will come in handy, as freezing columns is very much alike.
How to Freeze the First Column
So, let’s imagine you have a worksheet that has so many columns of data that some of them go off your viewable window or screen.
You might have split your screen to view Excel side by side with another application. If so, it looks something like this:
You want to keep the Region column visible at all times while you scroll to the right.
Then you can view the columns that are otherwise out of view like the Qty and Total $ columns.
To do this, follow the same procedure outlined before for freezing the top row.
But, this time you need to select “Freeze First Column” in the Freeze Panes drop-down.
Now you can scroll over to view the Qty and Total $ columns while the first column for Region remains in view.
Note in the following figure how columns B through I hides as you scroll as far to the right as possible… Yet column A remains in view!
How to Freeze Multiple Columns
Now let’s take things a step further…
Let’s say you want not only the Region column to remain in view as you scroll to the right, but also Location Code and Location.
Again, this is very simple since you have already learned how to freeze multiple rows.
With cell D1 selected, click on Freeze Panes and all columns to the left of D will be frozen.
Now scroll to the right to view the Qty and Total $ columns.
See it?
Columns A through C remain in view on the far left of your worksheet. Awesome!
Freeze Columns and Rows at the Same Time
You’ve learned how to freeze both single and multiple rows and columns independently.
Now, let’s turn our attention to the method of freezing both rows and columns simultaneously.
Let’s go back to our previous example where you learned how to freeze columns.
Now let’s look at how to freeze the first column and the first row at the same time in a single step.
You have essentially already learned how to do this; you just may not realize it yet.
Remember:
For multiple rows, we chose a cell in the first column. For multiple columns, we chose a cell in the first row.
Recall that for multiple rows we chose a cell in the first column and for multiple columns, we chose a cell in the first row.
To freeze both the first three columns (as in the previous example) as well as the first row (our header row), we select cell D2.
Can you see why?…
… the Freeze Panes feature freezes all rows and columns above and to the left, respectively, of the selected cell.
In a single step, you’ve made the header row and the first three columns of your table always viewable as you scroll over and down.
Bear in mind, you can also freeze multiple rows if you would like.
It’s simply a matter of which cell you select before you enable Freeze Panes as to the number of rows and columns you want to freeze.
How to Unfreeze Panes
Let’s say you receive a worksheet from somebody and they left frozen panes enabled in that file.
You don’t want to have to figure out where they froze panes… and you don’t have to!
Simply click on the Freeze Panes drop-down.
If there are currently any panes that are frozen, Excel will tell you automatically.
Just look at whether the first selection in the drop down says Freeze Panes or Unfreeze Panes.
By default, Excel will change the selection for Freeze/Unfreeze Panes accordingly.
Freezing Panes versus Splitting Panes
Another feature in Excel related to viewing worksheets in alternative ways is “Split”.
Split is in the Window group on the View tab.
While Split has its uses, it is very different from Freeze Pains in 2 ways:
1: Split allows you to view the entire worksheet contents in each of its panes. Contrast this with Freeze Panes where you can only view a slice of the worksheet in each pain.
2: Split allows you to change the position of the split on the fly by grabbing and moving it with your mouse. Contrast this with Unfreeze/Freeze by highlighting the cell where you want the “freeze point”.
One advantage to splitting panes versus freezing them is that you have multiple scrollable views of the exact same sheet.
So, if you wanted to view data for AUSTIN and TAMPA for Q1 and Q2 2015, you could split the sheet horizontally and do so.
Note that each split window has its own independent horizontal scroll control.
If you were to Freeze Panes instead to isolate the AUSTIN data, the frozen pane is not scrollable. This could prove to be less than useful if the pane was not large enough to contain all AUSTIN data.
What to Do When the
Freeze Panes Command is Disabled in Excel
You may run into an issue where Freeze Panes is disabled (grayed out).
There are 2 reasons why you might encounter this…
1: The most likely is that your worksheet is in Page Layout (found in the group “Workbook Views”). Freeze Panes is not enabled in this view.
The solution?
Simply change the workbook view to either Normal or Page Break Preview. You can do this by going to the Workbook Views group on the View tab and make your selection accordingly.
2: Freeze Panes might also be disabled if the workbook has been protected for Windows. This feature is disabled in Excel 2016 (and 2013).
You will only run into this issue if the sheet was protected by an earlier version of Excel (and Windows protection was checked).
Freeze Panes Tips
for Advanced Excel Users
Now you know the basics of how to freeze panes in Excel.
Let’s look at some more advanced tips!
Excel may Freeze Panes Totally
Different from what you Expected
Another problem that you may run into is when Excel freezes panes in a manner that you don’t expect.
This is another effect of not having all the rows or columns in view at the time you select Freeze Panes.
This will happen if you have more than one row or column outside the viewable area of the sheet.
For example:
Say you select Freeze Panes when the three rows that you are attempting to freeze are scrolled off the sheet view.
Excel will Freeze Panes in an unpredictable way in which you did not intend.
Note the lines indicating that rows and columns above and to the left of I12 are frozen.
This is by far nothing close to what we intended. So, be sure that all rows above your freeze point are in view before selecting Freeze Panes.
How to Hide the Freeze Panes Line
You have probably noticed in the previous figures that Excel inserts a thin line at the freeze point(s).
Excel doesn’t allow these lines to be removed, but… I found a workaround!
You can “camouflage” them. Here’s how:
The easiest thing to do is to use a border to cover this line.
For example, if you Freeze Top Row, first set a bottom border while row 1 is selected.
This doesn’t really remove the Freeze Panes line, but rather hides it.
But, it’s pretty close, right?!
Alternative to Freeze Top Row:
Use a Table
There is one alternative to Freeze Top Row that is worth a mention here.
That is if you format your data as a table! As a table, Excel will replace the column letters with the contents of your top row as you scroll down.
Here’s how to do that:
Go to Format as Table in the Styles group on the Home tab and select a table style.
As you scroll down, note that the column numbers become the header titles.
Also, they stay that way as you continue to scroll down.
(If you select/activate a cell outside the table, the headers will disappear and the column letters will reappear.)
There you have it: a complete run-down of the Freeze Panes features in Excel.
Freezing Panes is a fantastic way to quickly, and easily, gain overview when you have a huge dataset.
No more will you be bothered by “hidden” columns and rows.
Further, Freeze Panes is a great tool to have handy when filtering and sorting just isn’t enough.
Did you enjoy this tutorial?
Then you’re going to love our free training.