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!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

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!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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.

Freeze Panes Button

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.

freeze top row

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.

Kasper Langmann, Co-founder of Spreadsheeto

Your worksheet contains over 500 rows of 10 different data points (columns).

Data set

When you scroll below row 25, you better have memorized your headers if you want to know which column is what *drumroll*…

Scrolling the dataset

Okay, I’m just kidding… you don’t have to memorize anything!

Kasper Langmann, Co-founder of Spreadsheeto

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 🙂

Kasper Langmann, Co-founder of Spreadsheeto
Row 1 as the top row

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.

Data comparison

In this case, you will need to let Excel know how many rows to freeze by highlighting cell A6.

Highlight A6

The next step is to go back to Freeze Panes and choose Freeze Panes this time instead of Freeze Top Row.

Freeze panes

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.

Kasper Langmann, Co-founder of Spreadsheeto

Same goes for the header row!

Four rows remain visible

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:

Kasper Langmann, Co-founder of Spreadsheeto
Screen 1
Screen 2

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.

Freeze first column

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!

Kasper Langmann, Co-founder of Spreadsheeto
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.

Kasper Langmann, Co-founder of Spreadsheeto

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!

Columns A through C remain in view

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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Columns above and to the left are frozen

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.

Kasper Langmann, Co-founder of Spreadsheeto
Columns A through C remain in view

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!

Kasper Langmann, Co-founder of Spreadsheeto
Unfreeze panes

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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Example of multiple scrollable views of same sheet

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

Freeze panes disabled

There are 2 reasons why you might encounter this…

Kasper Langmann, Co-founder of Spreadsheeto

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.

Page layout

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!

Prevent Hidden Rows or Columns
when Freezing Excel Panes

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

Any of the rows/columns in the pane you are attempting to freeze that are not in the viewable area at the time will become hidden.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Freeze 4 top rows

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!

The only way to avoid these hidden rows/columns is to make sure they are in the viewable area when you select Freeze Panes.
Kasper Langmann, Co-founder of Spreadsheeto

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.

Cell A1 is active

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.

Kasper Langmann, Co-founder of Spreadsheeto
Rows outside viewable area

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.

Not intended hidden rows

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.

Kasper Langmann, Co-founder of Spreadsheeto

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!

Kasper Langmann, Co-founder of Spreadsheeto

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.

Thick bottom border

This doesn’t really remove the Freeze Panes line, but rather hides it.

But, it’s pretty close, right?!

Hidden freeze panes line

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.

Kasper Langmann, Co-founder of Spreadsheeto

Here’s how to do that:

Go to Format as Table in the Styles group on the Home tab and select a table style.

Format as a table

As you scroll down, note that the column numbers become the header titles.

Also, they stay that way as you continue to scroll down.

Data formatted as a table

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

Kasper Langmann, Co-founder of Spreadsheeto

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.

2019-09-02T13:54:32+00:00