How to Freeze Cells, Rows, and Columns in Excel Using “Freeze Panes“

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

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!

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

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?

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.

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.

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.

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

Now let’s take things a step further…

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!

You’ve learned how to freeze both single and multiple rows and columns independently.

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.

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.

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.

Let’s say you receive a worksheet from somebody and they left frozen panes enabled in that file.

Simply click on the Freeze Panes drop-down.

If there are currently any panes that are frozen, Excel will tell you automatically.

By default, Excel will change the selection for Freeze/Unfreeze Panes accordingly.

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

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.

You may run into an issue where Freeze Panes is disabled (grayed out).

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

Now you know the basics of how to freeze panes in Excel.

Let’s look at some more advanced tips!

One thing to be aware of when you freeze multiple rows or columns in Excel is:

Let’s look at an example where we want to freeze the top 4 rows, yet we have scrolled the top/header row out of the sheet’s view.

Note that in the previous figure, that row 1 is out of the view of our worksheet.

As we select row 4 and Freeze Panes, Excel will freeze everything above row 4 as expected.

If you attempt to scroll up to view the header row, you will not be able to!

Despite rows being hidden, you can still navigate to those hidden cells and view the contents in the formula bar with arrow buttons.

In the following figure, the Name Box indicates cell A1 is active. The contents are StandID as indicated in the Formula Bar.

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.

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.

You have probably noticed in the previous figures that Excel inserts a thin line at the freeze point(s).

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

There is one alternative to Freeze Top Row that is worth a mention here.

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

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.