When working with complicated Excel spreadsheets that have dozens of rows or columns, you may need to pin the row and/or column headers to make them stay on the screen, so that you will be able to see the headers even when you scroll away (see below left picture). Or maybe you just want to quickly compare two different sets of data that are widely separated (see below right picture). These cases are where freezing rows / columns can help you.
In this section, I will teach you how to keep rows of a worksheet visible when you scroll to another area of the worksheet with Excel’s built-in functionality.
Step 1: Select View > Freeze Panes > Freeze Top Row
Result
Step 1: Select the row (or the first cell in the row) below the row you want to freeze
For example, if your header is on row 4, then select row 5, or the first cell in row 5, which is cell A5.
Step 2: Select View > Freeze Panes > Freeze Panes
Result
With Excel’s built-in Freeze Panes feature, you can only freeze rows in one worksheet, which could make it tedious if you have a large number of worksheets with the same specific rows to freeze. In order to freeze rows across all worksheets quickly and easily, you can use the help of Kutools for Excel’s Freeze Panes Multiple Worksheets feature.
Step 1: Select the row (or the first cell in the row) below the row you want to freeze
For example, if you want to freeze the top row, select row 2, or the first cell in row 2, which is cell A2; if your header is on row 4, then select row 5, or the first cell in row 5.
Step 2: Select Kutools Plus > Worksheet > Freeze Panes Multiple Worksheets
Result
The same rows across all worksheets in the active workbook are pinned at top in no time.
Now, let’s learn how to lock the Excel columns, so that you can see which rows the values correspond with when you scroll to the right.
Step 1: Select View > Freeze Panes > Freeze First Column
Result
The leftmost column is now visible even when you scroll to the right.
If you want to freeze more than one column, please do as follows:
Step 1: Select the column (or the first cell in the column) to the right of the column you want to freeze
For example, if you want to pin the first two columns, you should select column C, or the first cell in column C, which is cell C1.
Step 2: Select View > Freeze Panes > Freeze Panes
Result
Instead of locking columns for each sheet one by one, you can also use Kutools for Excel’s Freeze Panes Multiple Worksheets feature to batch freeze columns across all worksheets in the active workbook.
Step 1: Select the column (or the first cell in the column) to the right of the column you want to freeze
For example, to freeze the leftmost column, select column B, or the first cell in column B, which is cell B1; to freeze the leftmost 2 columns, select column C, or the first cell in column C.
Step 2: Select Kutools Plus > Worksheet > Freeze Panes Multiple Worksheets
Result
The same columns in all worksheets of the active workbook are frozen immediately.
To lock one or more rows and columns at a time, please do as follows:
Step 1: Select a cell below the last row and to the right of the last column you want to freeze
For example, to pin the top 4 rows and first 2 columns, select the cell below the row 4 and to the right of column B, which is the cell C5.
Step 2: Select View > Freeze Panes > Freeze Panes
Result
To freeze rows and columns across all worksheets, you can use Kutools for Excel's Freeze Panes Multiple Worksheets feature, which enables you to freeze rows and columns across all worksheets with few clicks.
Step 1: Select a cell below the last row and to the right of the last column you want to freeze
For example, to lock the top 4 rows and leftmost 2 columns, select the cell below the row 4 and to the right of column B, which is cell C5.
Step 2: Select Kutools Plus > Worksheet > Freeze Panes Multiple Worksheets
Result
The exact rows and columns in all worksheets are frozen right away.
If you do not want to freeze rows and/or columns in the current worksheet anymore, tab View > Freeze Panes > Unfreeze Panes.
To unfreeze rows and/or columns across all worksheets in bulk, click Kutools Plus > Worksheet > Unfreeze Panes Multiple Worksheets.