Contextures

Freeze Excel Rows and Columns

Easy steps to freeze Excel rows and columns, and which option to use. How to fix and avoid problems with hidden rows and columns when freezing.

Lock Rows and Columns in Place

If you want to scroll down the worksheet, and lock the heading rows in place, so they're always visible, you can use one of the Freeze Panes commands.

  • On the Excel Ribbon, click the View tab
  • To see the Freeze Pane options, click the arrow on the Freeze Panes button

The 3 Freeze Pane options work differently, and each option is explained below.

-- Freeze Top Row

-- Freeze First Column

-- Freeze Panes

freeze panes commands

Freeze Top Row

If you select the Freeze Top Row command, it does not automatically freeze Row 1 on the worksheet.

  • Any cell on the worksheet can be selected
  • A single row is frozen at the top of the worksheet
  • The top VISIBLE row in the Excel window is frozen (row 7 is frozen in the screen shot below)
    • freeze Top Row

After freezing the top row:

  • The frozen row stays in place if you scroll up or down on the worksheet
  • If the frozen row has other rows above it, you can't scroll up to see those rows.
  • To see any rows that are above the frozen row, use the Unfreeze command.

Freeze First Column

If you select the Freeze First Column command, it does not automatically freeze Column A on the worksheet.

  • Any cell on the worksheet can be selected
  • A single column is frozen at the left side of the worksheet
  • The first VISIBLE column in the Excel window is frozen (column C is frozen in the screen shot below)
    • freeze First column

After freezing the first column:

  • The frozen column stays in place if you scroll left or right on the worksheet
  • If the frozen column has other columns to the left of it, you can't scroll left to see those columns.
  • To see any column to the left of the frozen column, use the Unfreeze command.

Freeze Panes

The Freeze Panes command gives you more options on what should be frozen on the worksheet. Instead of a single row or a single column, you can freeze one or more rows at the top, as well as one or more columns at the left.

With Freeze Panes command, the freeze point is set based on what is selected on the worksheet,

The following table shows which VISIBLE rows and VISIBLE columns will be frozen, based on what you've selected on the worksheet, and which cell is active.

Selection Active Cell Frozen VISIBLE Rows Frozen VISIBLE Columns
Cell(s) A1 All visible rows above the centre of the Excel window All visible columns to the left of centre in the Excel window
Cell(s) Any cell except A1 All visible rows above the active cell All visible columns to the left of the active cell
Entire row(s) A1 All visible rows above the centre of the Excel window All visible columns to the left of centre in the Excel window
Entire row(s) Any cell except A1 All visible rows above the active cell None
Entire column(s) A1 All visible rows above the centre of the Excel window All visible columns to the left of centre in the Excel window
Entire column(s) Any cell except A1 None All visible columns to the left of the active cell
Entire worksheet A1 All visible rows above the centre of the Excel window All visible columns to the left of centre in the Excel window
Entire worksheet Any cell except A1 All visible rows above the active cell All visible columns to the left of the active cell

After using the Freeze Panes command:

  • Any frozen columns stay in place if you scroll left or right on the worksheet
  • Any frozen rows stay in place if you scroll up or down on the worksheet
  • If frozen columns have other columns to the left of them, you can't scroll left to see them.
  • If the frozen rows have other rows above them, you can't scroll up to see them.
  • To see those off-screen rows or columns, use the Unfreeze command.

Unlock Rows and Columns

After any type of Freeze Panes command has been used, you can use the Unfreeze Panes command to remove the freezing.

  • Select any cell on the the worksheet
  • On the Excel Ribbon, click the View tab
  • Click the Unfreeze Pane command (it replaces the Freeze Pane command)
  • NOTE: You can also change the freezing to Top Row or First Column, instead of removing the freezing completely

Unfreeze Panes command

Cannot Unhide Rows or Columns

Usually it's easy to unhide rows or columns in an Excel worksheet, but occasionally you can run into problems. In these cases, try one of the following techniques:

-- Unfreeze Panes

-- Extend Selection

Unfreeze Panes

In some cases, the rows or columns aren't really hidden. There are frozen rows or columns, and the "hidden" sections are out of view.

This short video shows the problem, and how to fix it. There are written steps below the video.

Show Rows Hidden by Freezing

To see if freezing is the problem of hidden rows, follow these steps:

  • Select any cell on the the worksheet
  • On the Excel Ribbon, click the View tab
  • If there is an Unfreeze Pane command in the drop down list, click that, to unfreeze the locked sections

The "hidden" rows or columns might reappear after that, when you scroll to the top or left of the worksheet.

Extend Selection

If unfreezing didn't fix the problem of hidden rows, this extend selection trick might help. This short video shows the "Extend Selection" technique, and the written steps are below the video.

Extend Selection Steps

When the hidden rows are at the very top of the sheet, it's hard to select the hidden rows, in order to unhide them.

Try the following steps, to extend the selection, and show the hidden rows:

  • Press on the row button for the first visible row
  • Drag up, onto the Select All button
  • Right-click on the first visible row button, and click Unhide

NOTE: For hidden columns, press the first visible column button, and drag to the left.

extend selection

Download the Sample File

To try the freeze pane tips on this page, download the Excel Freeze Rows and Columns sample file. The file is in xlsx format, and zipped. There are no macros in the workbook.

Related Links

Column Headers Show Numbers

Change Column Header Font

Freeze Pane Settings disappear

Format Rows Based on Cell Value

 

 

 

Get weekly Excel tips from Debra

 

 

 

30 Excel Functions in 30 Days

 

 

 

Pivot Power Premium

 

 

Excel Chart Tools

 

 

 

 

Last updated: January 24, 2020 11:59 AM