Freeze Columns in Excel
MS Excel or Microsoft Excel is powerful spreadsheet software that helps users record large amounts of financial data. An Excel workbook consists of several worksheets where each sheet can store large data records within multiple cells. When working with very large data sets within an Excel worksheet, it often becomes difficult to track, remember or compare the information. When we work around common data sets around the sheet and navigate to the very right side of the worksheet, we may not see the corresponding headings. This is where freeze columns can be helpful.
In this tutorial, we will learn various step-by-step methods that will help us to lock or freeze columns in Excel. We implement these methods using Excel 2010; however, they can be used similarly in other versions of MS Excel as well. Before we move on to the methods, let us first briefly discuss the use of the Freeze Columns feature and how it can help us increase the readability of our Excel worksheets.
What is the Freeze Columns feature in Excel?
Freezing Excel columns is one of the useful features to increase the readability and productivity of data within a worksheet. It helps in comparing or reading data from different parts of the sheet with easy navigation. Freezing an Excel column typically locks or freezes the position of the column so that it doesn’t scroll through the database while navigating the sheet. When a column freezes, it simply locks its place while navigation through the other columns works normally.
For example, suppose we have an Excel sheet with the required headings in the first column and its relevant data in the corresponding columns. If we navigate through the sheet to the right, our headings in the first column will be hidden on the left side of the sheet, making the relevant data difficult to read or compare. Navigating the sheet left and right repeatedly would be time-consuming. If we freeze the entire first column to the left, we can scroll through the sheet, reading the relevant data according to the corresponding headings. In that case, we don’t need to scroll the left side to read the headings.
Note: When we freeze one or more columns within the worksheet, they remain visible or fixed only for the horizontal movements. If we scroll or navigate the worksheet downwards, the column values will also move vertically for the corresponding rows.
How to freeze columns in Excel?
When working with large data sets, there may be cases when we may need to freeze columns. Usually, we may need to freeze either one column or more than one column in a sequence. Fortunately, we can freeze single or multiple columns in Excel without limiting the number of columns to be frozen. Because of this, Excel gives a more productive presentation, allowing us to read or compare data within a worksheet easily.
Let us discuss both the use-cases when we may need to freeze a column in Excel, including all the steps for freezing columns:
Freeze Single (side-Left) Column in Excel
Excel allows us to freeze a single column in an entire sheet. Depending on the requirement, we can choose to freeze the column from the beginning or middle of the sheet. Technically, when we need to freeze a column, Excel gives the option to freeze only the first column. The first column does not literally refer to the first column of the worksheet. Instead, it targets or refers to the first column that appears as the first column (side-left column) in the active or visible area of the Excel window on the screen.
For example, if our Excel window displays Column B as the first column in the visible area, it will be used as the first column for the Freezing tool. Therefore, we must place the desired column in the most side-left area of the visible Excel window before following the steps mentioned below:
- First, we need to scroll the worksheet to the right so that the column we want to freeze becomes the first column in the active or visible area of the Excel window. In our example, we place column B as the first column or side-left column in the view area, as shown below:
- Next, we need to go to the View tab and click on the drop-down button next to the Freeze Panes button under the Window section. Here, we have to click on the option ‘Freeze First Column‘.
- After clicking on the ‘Freeze First Column’ option, the respective column gets fixed immediately. This means that even if we navigate the sheet to the very right of the worksheet, the fixed column will not be scrolled. However, all other columns will scroll as usual.
In the above worksheet, we can see that column B is still visible on the far left side of the Excel window even after navigating to the most right side of the sheet. It’s important to note that columns before Locked/Fixed/Frozen columns may not be visible. As in our example, column A is not visible.
Freeze Set of Columns in Excel
Excel also provides the option to freeze more than one column within a worksheet. However, there are some limitations. We cannot freeze non-contiguous (non-adjacent) columns. If we want to freeze multiple columns, they must be in a sequence. Also, Excel does not offer the flexibility of freezing columns from the middle of worksheets. This means that we cannot freeze any random set of columns in the middle of the sheet. Instead, we need to freeze all the columns between the desired column and the first column of the worksheet. For example, we generally cannot freeze columns B to E. We have to include column A and freeze all columns from A to E, and so on. If we strictly try to freeze column B to E, the data in column A which stays before the selected column set, will not be displayed in the sheet.
To freeze a set of columns or multiple columns (i.e., from column A to E), we can perform the following steps:
- First, we have to select the column right next to our desired set of columns that we want to freeze. Suppose, if we want to freeze column A to E, we have to select the whole column F. To select the entire column, we can click on the column header from the left-most area of the worksheet. Alternatively, we can select the first cell of the column (F1).
If we only want to display columns B to E, we can scroll a sheet to the right and place column B as the first column in the visible area on the screen. However, column A will be hidden after freezing the selected set of columns.
- After selecting the column followed by the desired column set, we need to navigate to the View tab on the ribbon and click on the drop-down arrow next to the Freeze Panes Afterward, we have to select the ‘Freeze Panes‘ option from the list, as shown in the following image:
- After clicking on Freeze Panes option from the drop-down list, we will see a bold border (Grey Line) line between columns E and F. This bold line indicates that the columns before this line are locked/fixed/frozen.
If we scroll the sheet to the right, we will see that columns from A to E are still visible at the far left of the Excel window. However, the other columns starting with column F are scrolling normally.
In the above example sheet, we can see that even after scrolling the sheet to column L, the initial five columns (columns A to E) are fixed to the far left of the Excel window.
Unfreezing an Excel Column or Multiple Columns
There may be cases when we have frozen the wrong columns, or we no longer need frozen columns in the worksheet. In such cases, it’s easy to unfreeze one or more columns in an Excel worksheet. We can follow the below steps and unfreeze the locked column in one go:
- First, we need to select or display the specific worksheet where we want to unfreeze columns.
- Next, we need to go to the ‘View‘ tab, click the ‘Freeze Panes‘ drop-down arrow and select the ‘Unfreeze Panes‘ option. This will unfreeze or unlock all the columns in the selected sheet.
Important Points to Remember
- It is essential to note that Excel does not technically freeze the first column of the worksheet when we use the View tab > Freeze Panes > Freeze First Column. Instead, it freezes the first or most-left column that is visible in the active view area of an Excel window.
- Excel does not allow us to freeze non-contiguous columns in the worksheet.
- When freezing multiple columns, Excel does not allow us to select any random set of columns in the middle of the worksheet. We have to place all the columns from starting to the desired column of the visible area of the active Excel window. The columns that are not visible from the left side of the sheet will not be displayed after freezing the columns.
- When we are freezing multiple columns, selecting the entire column that stays after our desired set of columns is always suggested. Also, all the columns we want to freeze must be visible in Excel active window area. Although we can also select a cell of a particular column, the Freeze tool behaves differently for cell selection. For example, if we select a cell of a column in the first row, all the columns before the selected cell will freeze. But if we select a cell in another row for the desired column, the rows above and the left column(s) will be frozen.