How to lock cells in Excel: freeze rows, protect cells and lock formulas

"Lock" means three different things in Excel, and people searching for it usually want one of them without knowing which word Excel uses. You may want a row to stay in place while you scroll, so the headings never disappear. You may want cells that nobody can change, so a template survives being shared. Or you may want a cell in a formula that stays put when you copy the formula, so every row uses the same rate.

Excel calls these Freeze Panes, protection and absolute references. They have nothing to do with each other, and they live in three different places. This guide takes them one at a time, with the steps, the traps and the shortcuts for each.

Which lock do you need?

If you're not sure, ask what should happen when you scroll, type or copy. Scrolling is Freeze Panes, typing is protection, and copying a formula is $.

Freeze the top row

A long list is hard to read once the headings scroll out of view. Freezing the top row keeps row 1 on screen while the rest of the sheet moves under it.

1. Click anywhere on the sheet.
2. Choose View > Freeze Panes > Freeze Top Row.

A thin line appears under row 1. Scroll down, and the headings stay. Freezing only changes what you see. Nothing in the cells changes, and it works the same whether the sheet has 20 rows or 20,000.

Freeze First Column under the same button does the same for column A. That's useful when the first column holds names or IDs and the data runs far to the right.

Freeze several rows or columns

Freeze Top Row and Freeze First Column only freeze one row or one column. For more, or for rows and columns together, you choose the cell where the frozen area ends.

1. Select the cell just below the last row you want to keep and just right of the last column you want to keep.
2. Choose View > Freeze Panes > Freeze Panes.

Everything above and to the left of the selected cell is frozen. Some common cases:

• Two heading rows: select A3. Rows 1 and 2 stay on screen.
• The header row and the first column: select B2. Row 1 and column A stay.
• Three rows and two columns: select C4.

The cell you select is the first cell that scrolls. If you freeze in the wrong place, unfreeze and try again.

Freezing works from the top of the visible window, so scroll to the top of the sheet before you freeze. If row 1 is scrolled out of view when you freeze row 3, rows 1 and 2 are hidden above the frozen area and you can't scroll back to them until you unfreeze.

Unfreeze, and Split as an alternative

To remove the freeze, choose View > Freeze Panes > Unfreeze Panes. The button changes name once something is frozen, so it's always in the same place.

View > Split looks similar but works differently. It divides the window into two or four panes that each scroll on their own, so you can look at row 10 and row 5,000 at the same time. Use Split to compare two far-apart parts of a sheet, and Freeze Panes to keep headings in view. You can't use both at once.

On Windows, the keyboard route is the Alt key tips: press Alt, then the letters shown on the ribbon. In English Excel that's Alt, W, F, F for Freeze Panes. The shortcuts guide explains how key tips work.

Headings on every printed page

Freeze Panes only affects the screen. A frozen header row doesn't repeat on a printout, so page two starts with a row of numbers and no headings. To repeat it on paper, use Page Layout > Print Titles and set Rows to repeat at top to $1:$1. The headings then print at the top of every page.

An Excel table gives you something similar on screen without freezing anything. Press Ctrl+T to make the data a table, and when you scroll down, the column letters A, B and C are replaced by the table's headings. Many people freeze the top row anyway, since the table trick only works while a cell inside the table is selected.

Hide rows and columns instead

Sometimes the goal isn't to keep headings in view but to get helper columns out of the way: lookup tables, calculation steps, old data. Hiding is the tool for that. Select the rows and press Ctrl+9, or select the columns and press Ctrl+0. To bring rows back, select the rows on both sides of the gap and press Ctrl+Shift+9, or right-click the row numbers and choose Unhide.

Hidden cells still calculate, and formulas that point at them keep working, so hiding a helper column is safe. It isn't a lock, though. Anyone can unhide the column again, and SUM still adds up hidden rows, while SUBTOTAL and AGGREGATE can skip them. If the hidden columns must stay hidden, protect the sheet and leave Format columns unticked, so people can't unhide them.

Grouping is the friendlier version of hiding. Select the rows and press Alt+Shift+Right Arrow, and Excel adds a small minus button in the margin that collapses and expands the group. People can see that something is folded away, which hidden rows never tell them.

Lock cells so nobody can change them

Protection is the second kind of lock. It stops people from typing in cells, deleting formulas or changing the layout. It's what you want for a template, a budget or a form that other people fill in.

The part that surprises everyone: every cell in Excel is locked from the start. You never notice because the lock does nothing until the sheet is protected. Protection is a switch for the whole sheet, and the Locked setting on each cell decides which cells the switch covers.

So locking specific cells is really about unlocking the others:

1. Select the cells people should be able to change, such as the input cells of a form.
2. Press Ctrl+1 to open Format Cells, go to the Protection tab and untick Locked.
3. Choose Review > Protect Sheet.
4. Type a password if you want one, choose what people may still do, and click OK.

Now the unlocked cells accept typing and every other cell refuses it with a message that the cell is on a protected sheet. A good habit is to give the unlocked cells a light fill color, so people can see where to type.

Test the protection before you send the sheet on. Try typing in a formula cell and in an input cell. The first should refuse you and the second should accept. It takes ten seconds and catches most mistakes.

Choose what people may still do

The Protect Sheet dialog box has a list of things users may still do. By default they can select locked and unlocked cells and nothing else. Tick more options when the sheet needs them:

• Format cells, columns or rows lets people change number formats and column widths, without letting them change the values.
• Insert rows and Delete rows let people add to a list without unlocking the formulas.
• Sort and Use AutoFilter let people sort and filter, as long as the cells involved are unlocked, or the filter was set up before you protected the sheet.

Untick Select locked cells if people shouldn't even click on the formulas. Then Tab and the arrow keys jump straight between the input cells, which makes a form much quicker to fill in.

Lock only the formulas

A common wish is the opposite of a form: people may change everything except the formulas. Rather than selecting the formulas by hand, let Excel find them:

1. Select the whole sheet with Ctrl+A, press Ctrl+1 and untick Locked. Now nothing is locked.
2. Choose Home > Find & Select > Go To Special, pick Formulas and click OK. Excel selects every cell with a formula.
3. Press Ctrl+1 and tick Locked again, for just those cells.
4. Protect the sheet.

The data can now be edited, and the formulas can't. If you also want to hide how the formulas work, tick Hidden on the Protection tab in step 3. A hidden formula still calculates, but the formula bar stays empty when the cell is selected on a protected sheet.

Different cells for different people

Review > Allow Edit Ranges lets you name ranges that can be edited with their own password, even on a protected sheet. One department gets the password for its rows, another for its own. It's handy in a shared budget, though for real access control a shared workbook in SharePoint or OneDrive with separate files or permissions is usually easier to manage.

Unprotect a sheet

Review > Unprotect Sheet turns protection off again, after asking for the password if one was set. The Locked settings on each cell stay as they were, so you can make your changes and protect the sheet again with the same setup.

There is no official way to recover a forgotten sheet password. Keep it somewhere safe, or keep an unprotected master copy of the file.

Protect the workbook and the file

Sheet protection covers the cells on one sheet. Two other kinds of protection cover the rest:

• Review > Protect Workbook protects the structure: nobody can add, delete, rename, move or unhide sheets. Use it together with sheet protection on a template.
• File > Info > Protect Workbook > Encrypt with Password sets a password to open the file at all. That is real encryption. Without the password, the file can't be read.

Sheet and workbook protection are there to prevent mistakes, not to keep secrets. The passwords are weak, and tools to remove them are easy to find. If the file holds something private, such as salaries, encrypt it with a password to open, or don't put that data in the shared file.

Lock a cell in a formula with $

The third kind of lock is inside formulas. When you copy a formula, every reference in it moves along. That is usually what you want, but not for a cell that every row should use, such as an hourly rate, a tax rate or a total. A $ in front of the column letter and the row number locks the reference:

Without the $, the formula still works in the first row, then goes wrong as soon as you copy it down:

That is the most common silent error in Excel: no error message, just a wrong number. The same goes for a share of a total, where every row must divide by the same total cell:

You don't need to type the $ signs. Click in the reference while editing the formula and press F4. Each press cycles through $E$1, E$1, $E1 and E1. Practice it in lock a rate cell with $.

Lock only the row or only the column

A $ before the column letter locks the column, and a $ before the row number locks the row. Locking one part is called a mixed reference, and it's what makes a grid with one formula possible:

Copy that formula across and down, and every cell multiplies the quantity in column A by the price in row 1. Try it in a price grid with mixed references.

Locking just the start of a range gives a running total. The start stays on B2, while the end moves down one row at a time:

That one is practiced in a running total with a growing range. The topic page on absolute references has all three exercises.

A name instead of $

For a single fixed cell, such as a rate, a name can replace the $ signs. Select E1, type Rate in the Name Box to the left of the formula bar and press Enter. Now the formula can say =B2*Rate. A name always points at the same cell, so it never moves when you copy the formula, and anyone reading it can see what the number is. Names work best for a handful of inputs that are used all over the workbook, like a tax rate or an exchange rate. For ranges inside one table, $ is quicker.

When a lock doesn't work

• Freeze Panes is grayed out. Excel is probably still in edit mode, so press Enter or Esc first. It's also unavailable in Page Layout view, so switch to Normal view, and on a protected sheet.
• The freeze is in the wrong place. Unfreeze, scroll to the top, select the right cell and freeze again.
• Protected cells can still be changed. The sheet isn't protected, or the cells were unlocked. Check the Protection tab in Format Cells.
• Nothing can be changed after protecting. You protected the sheet before unlocking the input cells. Unprotect, unlock them and protect again.
• Sorting or filtering stops working. Allow Sort and Use AutoFilter when you protect the sheet, and set up the filter before you protect it.
• A copied formula gives 0 or the wrong number. A reference that should be locked has moved. Check the formula in the second row and add the missing $.

Locking in Google Sheets

Google Sheets has the same three kinds of lock with different names. View > Freeze freezes rows and columns, up to a chosen row or column. Data > Protect sheets and ranges protects a sheet or a range and lets you pick exactly which people may edit it, which is stronger than Excel's sheet passwords, since it uses Google accounts. The $ signs in formulas work exactly as in Excel, and F4 toggles them in Sheets too.

Locking in Excel tests

Employer Excel tests rarely ask you to protect a sheet, but they nearly always test $. A typical task gives you a rate or a total in one cell and asks for a formula you can fill down. Write the formula in the first row, add $ to the fixed cell with F4, fill down and check the second row before you move on. Freeze Panes doesn't score points, but freezing the top row of a long data set makes the rest of the test faster.

In short

To keep headings on screen, use View > Freeze Panes: Freeze Top Row for row 1, or select the first cell that should scroll and choose Freeze Panes for more. To stop people from changing cells, unlock the cells they may edit with Ctrl+1 > Protection, then turn on Review > Protect Sheet, remembering that every cell is locked until you say otherwise. And to keep a cell fixed when you copy a formula, put $ in the reference with F4: $E$1 for a single cell, $A2 or B$1 for a column or a row.

The $ is the lock you'll use every day, and the one employer tests check. The three exercises below train it with a rate cell, a price grid and a running total. Try the first one for free and add the $ yourself.