If you are working on a large spreadsheet, it can be useful to “freeze” certain rows or columns so that they stay on screen while you scroll through the rest of the sheet. As you’re scrolling through large sheets in Excel, you might want to keep some rows or columns—like headers, for example—in view.
Excel lets you freeze things in one of three ways:. You can freeze the top row. You can freeze the leftmost column. You can freeze a pane that contains multiple rows or multiple columns—or even freeze a group of columns and a group of rows at the same time.
Freeze the top row and the first column. To freeze the top row and the first column at the same time, click cell B2. Then, on the View tab, click Freeze Panes. Freeze as many rows or columns as you want. Want to freeze multiple rows and/or columns? You can freeze as many as you want, as long as you always start with the top row and the first column.
So, let’s take a look at how to perform these actions. Freeze the Top Row Here’s the first spreadsheet we’ll be messing with. It’s the Inventory List template that comes with Excel, in case you want to play along. The top row in our example sheet is a header that might be nice to keep in view as you scroll down. Switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Top Row.” Now, when you scroll down the sheet, that top row stays in view. To reverse that, you just have to unfreeze the panes.
On the “View” tab, hit the “Freeze Panes” dropdown again, and this time select “Unfreeze Panes.”. Freeze the Left Row Sometimes, the leftmost column contains the information you’ll want to keep on screen as you scroll to the right on your sheet. To do that, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze First Column.” Now, as you scroll to the right, that first column stays on screen. In our example, it lets us keep the inventory ID column visible while we scroll through the other columns of data.
And again, to unfreeze the column, just head to View Freeze Panes Unfreeze Panes. Freeze Your Own Group of Rows or Columns Sometimes, the information you need to freeze on screen isn’t in the top row or first column. In this case, you’ll need to freeze a group of rows or columns. As an example, take a look at the spreadsheet below.
This one is the Employee Attendance template included with Excel, if you want to load it up. Notice that there are a bunch of rows at the top before the actual header we might want to freeze—the row with the days of the week listed. Obviously, freezing just the top row won’t work this time, so we’ll need to freeze a group of rows at the top. First, select the entire row below the bottom most row that you want to stay on screen. In our example, we want row five to stay on screen, so we’re selecting row six.
To select the row, just click the number to the left of the row. Next, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Panes.” Now, as you scroll down the sheet, rows one through five are frozen. Note that a thick gray line will always show you where the freeze point is. To freeze a pane of columns instead, just select the whole row to the right of the right most row you want to freeze. Here, we’re selecting Row C because we want Row B to stay on screen.
And then head to View Freeze Panes Freeze Panes. Now, our column showing the months stays on screen as we scroll right. And remember, when you have frozen rows or columns and need to return to a normal view, just go to View Freeze Panes Unfreeze Panes. Freeze Columns and Rows at the Same Time We have one more trick to show you. You’ve seen how to freeze a group of rows or a group of columns. You can also freeze rows and columns at the same time.
Looking at Employee Attendance spreadsheet again, let’s say we wanted to keep both the header with the weekdays (row five) and the column with the months (column B) on screen at the same time. To do this, select the uppermost and leftmost cell that you don’t want to freeze. Here, we want to freeze row five and column B, so we’ll select cell C6 by clicking it. Next, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Panes.” And now, we can scroll down or right while keeping those header rows and columns on screen. Freezing rows or columns in Excel isn’t difficult, once you know the option is there. And it can really help when navigating large, complicated spreadsheets.
The tutorial shows how to freeze cells in Excel to keep them visible while you navigate to another area of the worksheet. Below you will find the detailed steps on how to lock a row or multiple rows, freeze one or more columns, or freeze column and row at once. When working with large datasets in Excel, you may often want to lock certain rows or columns so that you can view their contents while scrolling to another area of the worksheet. This can be easily done by using the Freeze Panes command and a few other features of Excel.
How to freeze rows in Excel Freezing rows in Excel is a few clicks thing. You just click View tab Freeze Panes and choose one of the following options, depending on how many rows you wish to lock:.
Freeze Top Row - to lock the first row. Freeze Panes - to lock several rows. The detailed guidelines follow below. How to freeze top row in Excel To lock top row in Excel, go to the View tab, Window group, and click Freeze Panes Freeze Top Row. This will lock the very first row in your worksheet so that it remains visible when you navigate through the rest of your worksheet. You can determine that the top row is frozen by a grey line below it: How to freeze multiple rows in Excel In case you want to lock several rows (starting with row 1), carry out these steps:.
Select the row (or the first cell in the row) right below the last row you want to freeze. On the View tab, click Freeze Panes Freeze Panes.
For example, to freeze top two rows in Excel, we select cell A3 or the entire row 3, and click Freeze Panes: As the result, you'll be able to scroll through the sheet content while continuing to view the frozen cells in the first two rows. Notes:.
Microsoft Excel allows freezing only rows at the top of the spreadsheet. It is not possible to lock rows in the middle of the sheet. Make sure that all the rows to be locked are visible at the moment of freezing. If some of the rows are out of view, such rows will be hidden after freezing. For more information, please see. How to freeze columns in Excel Freezing columns in Excel is done similarly by using the Freeze Panes commands. How to lock the first column To freeze the first column in a sheet, click View tab Freeze Panes Freeze First Column.
This will make the leftmost column visible at all times while you scroll to the right. How to freeze multiple columns in Excel In case you want to freeze more than one column, this is what you need to do:. Select the column (or the first cell in the column) to the right of the last column you want to lock. Go to the View tab, and click Freeze Panes Freeze Panes.
For example, to freeze the first two columns, select the whole column C or cell C1, and click Freeze Panes: This will lock the first two columns in place, as indicated by the thicker and darker border, enabling you to view the cells in frozen columns as you move across the worksheet. Notes:. You can only freeze columns on the left side of the sheet. Columns in the middle of the worksheet cannot be frozen. All the columns to be locked should be visible, any columns that are out of view will be hidden after freezing.
How to freeze rows and columns in Excel Besides locking columns and rows separately, Microsoft Excel lets you freeze both rows and columns at the same time. Here's how:. Select a cell below the last row and to the right of the last column you'd like to freeze. On the View tab, click Freeze Panes Freeze Panes. Yep, it's that easy:) For example, to freeze top row and first column in a single step, select cell B2 and click Freeze Panes: This way, the header row and leftmost column of your table will always be viewable as you scroll down and to the right: In the same fashion, you can freeze as many rows and columns as you want as long as you start with the top row and leftmost column. For instance, to lock top row and the first 2 columns, you select cell C2; to freeze the first two rows and the first two columns, you select C3, and so on.
How to unlock rows and columns in Excel To unlock frozen rows and/or columns, go to the View tab, Window group, and click Freeze Panes Unfreeze Panes. Freeze Panes not working If the Freeze Panes button is disabled (greyed out) in your worksheet, most likely it's because of the following reasons:. You are in cell editing mode, for example entering a formula or editing data in a cell.
To exit cell editing mode, press the Enter or Esc key. Your worksheet is protected. Please first, and then freeze rows or columns.
Other ways to lock columns and rows in Excel Apart from freezing panes, Microsoft Excel provides a few more ways to lock certain areas of a sheet. Split panes instead of freezing panes Another way to freeze cells in Excel is to split a worksheet area into several parts. The difference is as follows: Freezing panes allows you to keep certain rows or/and columns visible when scrolling across the worksheet. Splitting panes divides the Excel window into two or four areas that can be scrolled separately. When you scroll within one area, the cells in the other area(s) remain fixed.
To split Excel's window, select a cell below the row or to the right of the column where you want the split, and click the Split button on the View tab Window group. To undo a split, click the Split button again.
Use tables to lock top row in Excel If you'd like the header row to always stay fixed at the top while you scroll down, convert a range to a fully-functional Excel table: The fastest way to create a table in Excel is by pressing the Ctl + T shortcut. For more information, please see. Print header rows on every page In case you'd like to repeat top row or rows on every printed page, switch to the Page Layout tab, Page Setup group, click the Print Titles button, go to the Sheet tab, and select Rows to repeat at top. The detailed instructions can be found here:. That's how you can lock a row in Excel, freeze a column, or freeze both rows and columns at a time. I thank you for reading and hope to see you on our blog next week! You may also be interested in:.