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 to 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
- Select the cell (s) you want to lock.
- In your Excel spreadsheet, click the Home tab.
- Click Format on the right side of the screen.
- Scroll down and click “Lock Cell”.
- When you’ve locked your cells, click the Review tab.
- Click Protect Sheet.
- Select the permissions you want to allow on the sheet, and then click OK.
- 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.
2. Click the “Home” tab in your Excel spreadsheet.
3. Click “Format” on the right side of the screen.
4. Scroll down and click “Lock Cell”.
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.
2. Click Protect Sheet.
3. Select which permissions you want to allow on the sheet, then click OK. You can also add a password for additional protection.
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.
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 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.
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.
Any unauthorized or accidental change to a formula can alter 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.
2. Click the warning label and select “Lock Cell”.
3. You then need to protect your worksheet by clicking Protect Sheet on the Review tab.
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.