Monitor Your Data With Freeze Panes In Excel!

In this post, we are going to review how to use the Freeze Panes feature in Microsoft Excel.

Freeze Panes are a great tool to help you keep important data in view while scrolling around your sheet.

Excel spreadsheets can become very difficult to manage as they increase in size, but mastering this tool will keep you in control!

If you would like to learn more about other Excel features and built-in tools, then please join one of our top-rated Excel courses.

What Are Freeze Panes?

Freeze Panes are an excel feature that isolates a section of your worksheet, so that it is always in view.When you have a large table of data, it will help you a great deal to freeze specific rows and columns!

So how can you use Freeze Panes? And what different ways can they be applied? Lets start with the most simple application.

Freezing The Top Row Only

Here is how you freeze the top row of your data sheet using freeze panes.

Description of our Data set

So, let’s say we have the data set shown below.

A hypothetical yoghurt company wants to introduce four new flavours to the market. Thirty-nine participants were asked to test and evaluate the four new yoghurt flavours. The participants were asked to give each flavour a rating out of ten.

We have the headings in the first row as shown below. The entire data set encompasses range A1:E40.

The first part of the data set.

 

Screenshot showing the first part of the data set for the Freeze Top Row Example.

The second part of the data set.

Screenshot showing the second part of the data set for the Freeze Top Row Example.

As we scroll down, and we don’t see the headings anymore, it’s difficult to see what is being compared.

Since our headings are in the top row, we can do the following.

1)   Scroll back to the top of the data set. Go to the View Tab (Step 1 in the image) and in the Window Group, click on Freeze Panes (Step 2 in the image).

Screenshot showing the Freeze Panes option in the Window Group in the View Tab, highlighted.

2)   Select Freeze Top Row.

Screenshot showing the Freeze Top Row option highlighted.

3)   You will see a thin grey line along the bottom border of the entire row 1. This means that row 1 is frozen in place.

Screenshot showing the thin grey line that indicates the top row has been frozen.

4)   So as you scroll down, row 1 which is the top row with the headings, will always be visible.

Screenshot showing that the top row is always visible.

Tip: If you want to keep the headings permanently in view as you scroll down, you can also convert your range to a Table.

Looking for something more complex? Read here for a guide to Logical Functions in Excel.

Freeze The First Column Only

Now let’s look at an example of how to freeze a specific column.

Description of our Data set

So, let’s say we have the data set shown below.

An admin assistant working for a hypothetical plant nursery has compiled an information sheet, about the nursery’s seven top selling plants.

Our data is laid out horizontally and we have the headings in the first column and the data spans range A3:H6. The gridlines have been turned off.

The first part of the data set.

Screenshot showing the first part of the data set for the Freeze First Column example.

The second part of the data set.

Screenshot showing the second part of the data set for the Freeze First Column example.

Since our headings are in the first column, we can do the following.

1)   Scroll to the part of the sheet where you can see columns A, B, C, and D and the headings. Go to the View Tab and in the Window Group, click on Freeze Panes.

2)   Select Freeze First Column.

Screenshot showing the Freeze First Column option highlighted.

3)  You will now see the thin grey line along column A. So, as you scroll across, the headings in column A will always be visible.

Screenshot showing the thin grey line along the first column.

If you would like to learn about how to change your data from a horizontal to a vertical layout then please visit our guide on the TRANSPOSE Function.

Freeze Rows or Columns

Finally, now we have some experience we can really customize our freeze panes.

Description of our Data set

So, let’s say we have the following data set – a list of employees of a hypothetical medium-sized game development studio. The data has been sorted by annual salary as shown.

The Annual Salary column has been sorted from largest to smallest. There are ninety employees at the company.

Screenshot showing the source data set for the Freeze Rows or Columns example. The Annual Salary column has been sorted from largest to smallest.

To learn more about how to analyse your data in Excel read here.

As we scroll down the rows we would like to keep headings which are in row 1 and the first six employees with the highest salaries in view, for comparative purposes. So, we want to freeze the first seven rows in other words.

1)   To do this we select the row immediately below row 7, which is row 8.

Screenshot showing row 8 selected.

2)   With row 8 selected, go to the View Tab and in the Window Group, click Freeze Panes.

3)   Select Freeze Panes from the options, as shown.

Screenshot showing the Freeze Panes option highlighted.

4)  The grey line should appear on the bottom border of row 7. Now as you scroll down the data set, the headings in row 1 and the first six rows are always in view.

Screenshot showing that rows 1 - 7 are always in view as one scrolls down the data set.

Let’s say we have a situation where we want to freeze the first two columns so that the EmployeeID column and the Employee Name column are permanently visible instead, as we scroll across the data set.

1)   We would need to select the column to the right of these two columns which is column C.

Screenshot showing the entire Column C selected

2)   With column C selected, go the View Tab and in the Window Group, click Freeze Panes.

3)    Select Freeze Panes.

Screenshot showing the Freeze Panes option highlighted.

4)   Now as you scroll across the data set column A and column B are always in view.

Screenshot showing column A and Column B are always in view as one scrolls across the data set.

Looking for more top tips on Excel? Read the Ultimate Guide to Highlighting Duplicate Values in Excel.

Unfreeze Rows or Columns

To Unfreeze Rows or Columns and return the spreadsheet back to it’s original state follow these simple steps.

1)   Go the View Tab and in the Window Group, select Freeze Panes.

2)  Click Unfreeze Panes.

Screenshot showing the Unfreeze Panes option highlighted.

3)   The thin grey line will be removed, and you can scroll through the spreadsheet in the usual way.

Freeze Panes Use Cases

Let’s look at a couple of situations where you may need to Freeze Panes in Excel.

1) When someone else is using your workbook for data entry purposes, then it makes sense to use freeze panes to reduce the chance of error as the person is entering the data of interest and can see the headings.

2) If you are sharing your workbook with multiple users or colleagues and have large data sets on your worksheets, then freezing panes makes navigation of your worksheets easier.

 

Freeze Panes Not Working as Expected

In a situation where your Freeze Panes aren’t working as expected, this will be due simply to a data selection error.

As a simple tool in Excel, the most common errors that can occur are from a users side.

To ensure this dosen’t happen, follow the steps in this guide to unfreeze your rows and columns, and start the process over carefully using our step by step guide.

Freeze Panes are one of the reasons Excel is so great for Accounting and Bookkeeping.

Learning Objectives

You now know how to:

  • Freeze the first row or column in Excel
  • Freeze rows and columns
  • Unfreeze Panes

Additionally, you have an understanding of:

  • How Freeze Panes facilitate data entry
  • How Freeze Panes assist navigation

Want to learn more about Excels most powerful features? Learn about the UNIQUE Function here.

Conclusion

Freeze Panes are useful for keeping a section of your data set in view all the time. In this way you can monitor and keep a hand on your data.

Freeze Panes are simple to implement and easy to remove if needed. This is a feature that is useful for both beginner and advanced users to know about.

Looking for more top tips on Excel? Read our guide here on How To Make A Stacked Bar Or Column Chart (5 Minutes Or Less!).

Special thank you to Taryn Nefdt for collaborating on this article!

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.