Shortcut to Lock Cells in Excel and Hide Formulas

GigabyteKingdom is audience-supported. When you purchase through links on our website, we may earn an affiliate commission. Learn more.
Shortcut to Lock Cells in Excel

The shortcut to lock cells in Excel lies in the F4 key on your keyboard. Once you’ve entered a cell reference in a formula, press F4. This adds a “$” symbol in the field, indicating that the row and column are now locked. By pressing F4 again, only the column will be locked. And by pressing F4 yet again, only the row will be locked. You’ve worked hard on your spreadsheet and now this will ensure anyone you share it with can’t make changes to critical data or change the formula in a cell. Below are other ways to protect cells from being modified.

How to Lock All the Cells in a Worksheet in Excel

Did you know that you can use shortcut keys to not only freeze (lock) cells in Excel, but you can lock one or multiple cells, including cells with formulas? Freeze one row or column or lock multiple rows and columns. You can even freeze the entire table! And the method is pretty much the same no matter what you choose to do.

Moreover, all cells in Excel are locked by default. But not until activated to protect them. You can only prevent changes from being made by locking and protecting the cells.

Follow the instructions below to lock the entire worksheet. Since all cells are locked by default, what we’re actually doing here is protecting the entire worksheet. What we’re actually doing here is protecting the entire worksheet, ince all cells are locked by default

  1. Click the Review tab.
  2. In the Changes group, click Protect Sheet.
  3. In the Protect Sheet dialogue box: click the Protect worksheet and contents of locked cells box (it should be checked by default).
  4. You can enter a password so that changes can only be made with a password.
  5. By default: Select locked cells and Select unlocked cells are checked. Here, you can allow options to specify what other users can do.
  6. Click OK once you’ve selected all the options you want.
  7. If you’ve entered a password, you might be asked to reconfirm the password.

Notice that once locked, most of the options on Excel are no longer available. And if someone tries to make changes, they’ll be shown a prompt that says: The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

To unlock the worksheet, they’ll need to go to Review > Changes > Protect Sheet. If a password was used to lock the sheet, they’ll need it to make any changes.

How to Lock Cells in Excel

In some instances, you may just want specific cells locked within an Excel worksheet that contain crucial data points or formulas you don’t want altered. You can protect those cells, while allowing changes to be made to all other areas of the worksheet.

  1. Use the keyboard shortcut Ctrl + 1 to select the entire worksheet. And click the dialogue box launcher in the Alignment group in the Home tab.
  2. In the Format Cells dialogue box under the Protection tab, uncheck the Locked box.
  3. Then, click OK.
  4. Select the cells you want to lock.
  5. Use the keyboard shortcut Ctrl + 1 again to select the dialogue box launcher in the Alignment group within the Home tab.
  6. In the Format Cells dialogue box under the Protection tab, check the Locked box.

So far, what we’ve done is unlock all the cells in the worksheet, except the ones you want locked.

  • Now, go to the Review tab.
  • In the Changes group, click Protect Sheet.
  • Then in the Protect Sheet dialogue box, ensure that the Protect worksheet and contents of locked cells is checked (it’s checked by default).
  • At this time, you can enter a password to protect the sheet.
  • Now you can specify everything you want any other user to do. By default, Select locked cells and Select unlocked cells are checked. But you can allow other options.
  • Click OK.
  • If you created a password, you’ll be asked to reconfirm it here.

How to Lock an Entire Worksheet (Except Specific Cells)

You can also lock an entire Excel sheet and keep just a few cells unlocked for alterations by other users. This is a perfect option when your Excel sheet has an interactive feature, like a drop-down list. But there’s two steps involved to this, which will take you through one by one.

Step 1: Here’s how to keep the cells you want other users to manipulate unlocked:

  1. Select all the cells you want to keep unlocked.
  2. Press Ctrl + 1.
  3. Once the Format Cell box opens, click the Protection tab.
  4. Uncheck the Locked option.
  5. Click OK.

By doing this, when you lock the entire worksheet. Only the cells you selected can be altered. In the case of a drop-down list, now any user can make alterations to just those cells.

Step 2: Here’s how to lock the entire sheet except those selected cells:

  1. Click the Review tab.
  2. In the Changes group, click Protect Sheet.
  3. Ensure that Protect the worksheet and contests of locked cells box is checked in the Protect Sheet dialogue box (it should be checked by default).
  4. Then, enter a password (if you want to protect the sheet).
  5. You can specify what a user is allowed to do. By default, Select locked cells and Select unlocked cells are checked. Click the other options you want at this time.
  6. Click, OK.
  7. If you entered a password, you’ll be asked to reconfirm it.

How to Hide Formulas Within a Cell

So, once you’ve locked a cell with a formula in Excel. The formula will still be visible in the formula bar when the cell is selected. If you don’t want other user seeing the formula, you can hide it.

Here are the steps for hiding formulas in locked cells:

  1. Select the cells with the formula you want to hide.
  2. Use the keyboard shortcut Control +1. Or click the dialog box in the Alignment group in the Home tab.
  3. In the Format Cells dialogue box in the Protection tab, click Hidden.

Protecting the cells hides the formula in the formula bar from other users. So, there you have it, these are some of the way you can lock cells in Excel.