Running problems in shared Excel spreadsheets? Learn how to lock cells

Have you ever left something perfectly fine and returned to a total disaster? Any pet owner will likely say “yes”.

At work, the same thing can happen when you collaborate on a shared document. It’s usually an accidental keystroke that does this. In this case, if it isn’t done by your cat, it is no accident – it is definitely sabotage.

While such an error is understandable, it can be frustrating and time-consuming to fix. When working on shared Excel spreadsheets, you can prevent these mishaps completely by locking cells and protecting your worksheets.

Whether you’re working on an upcoming report or planning your budget for the next quarter, here’s how you can keep people from changing or deleting important information in an Excel document.

Can you lock cells in Excel?

Yes, you can lock cells in Excel by following a few simple steps. When you lock a cell in Excel, users won’t be able to make changes to your worksheets. This is especially useful when you’re working on a project that involves multiple team members.

Let’s say you’re the head of marketing and you’ve asked each channel lead (email, website, social media) to report their quarterly numbers for an upcoming meeting.

You don’t want someone accidentally deleting important information or changing formulas or conditional formatting before consulting with key stakeholders. This process ensures that only pre-approved users can edit the cell, saving you headaches in the future.

Another method is to lock your formula cells so that the numbers are filled in correctly. Jump to this section here.

How to lock cells in Excel

  1. Select the cell (s) you want to lock.
  2. Click the “Home” tab of your Excel spreadsheet.
  3. Click Format on the right side of the screen.
  4. Scroll down and click “Lock Cell”.
  5. When you’ve locked your cells, click the Review tab.
  6. Click Protect Sheet.
  7. Select the permissions you want to allow on the sheet, and then click OK.
  8. Make sure your cells are locked by looking for the Protect Sheet icon on the toolbar.

How to lock cells in Excel

1. Select the cell (s) you want to lock.

How to lock cells in Excel

2. Click the “Home” tab in your Excel spreadsheet.

How to lock cells in Excel step 1

3. Click “Format” on the right side of the screen.

How to lock cells in Excel step 2

4. Scroll down and click “Lock Cell”.

How to lock cells in Excel step 3

Once you’ve followed these steps, your cells will be locked but will still be editable. To make sure the cells cannot be edited, you need to protect your worksheet as well. You can find these steps in the next section.

How to protect a worksheet in Excel

1. With your cells locked, click the Review tab.

How to protect a worksheet in Excel, step 1

2. Click Protect Sheet.

How to protect a worksheet in Excel, step 2

3. Select which permissions you want to allow on the sheet, then click OK. You can also add a password for additional protection.

How to Protect a Worksheet in Excel Step 3

Note: If you don’t add a password, anyone can click Protect Sheet to make changes to the sheet. If you add a password, only those with the code can do it.

4. Make sure your cells are locked by looking for the Protect Sheet icon on the toolbar.

How to protect a worksheet in Excel, step 4

You can also check that your worksheet is protected by trying to write something in the locked cells. You should get this warning.

How to protect a worksheet in Excel, step 5

How to protect specific cells in Excel (and get a cell lock shortcut)

1. Select the cells you want to lock, then press Ctrl + Shift + F (Windows) or Ctrl + 1 (Mac) to open the Format Cells box. Then click the Protection tab.

How to Protect Specific Cells in Excel Step 1

2. Activate the “Blocked” checkbox and click “OK” in the lower right corner to complete the process.

These simple steps provide both a shortcut to lock cells in bulk and a way to protect specific cells in Excel.

How to protect formulas in Excel

A green triangle indicates an error in your cell’s formula. The error sign will appear when your formula is unprotected.

Excel formula error warning

Any unauthorized or accidental change to a formula can change the integrity of the data on the sheet. Hence, it is important that you always lock your formulas to avoid mistakes.

How to protect your formulas in Excel:

1. Click the cell with the green triangle and look for the yellow triangle warning sign.

How to Protect Formulas in Excel Step 1

2. Click the warning label and select “Lock Cell”.

How to Protect Formulas in Excel Step 2

3. You then need to protect your worksheet by clicking Protect Sheet on the Review tab.

How to Protect Formulas in Excel Step 3

Another way to protect your formulas is to lock each formula cell individually as you create your worksheet (as described here) and protect your sheet once all the formulas have been locked.

Whether you are the person who fixed the error or the person who made it, these steps can help you ensure that it never occurs again. And your cat needs to find something else to sabotage.

New call to action

Leave a Reply

Your email address will not be published. Required fields are marked *