Running Into Issues in Shared Excel Sheets? Learn How to Lock Cells

Download Now: 10 Free Excel Templates
Martina Bretous
Martina Bretous

Updated:

Published:

Ever left something perfectly fine and came back to a total disaster? Any pet owner will probably say, "yes."

lock cells excel: image shows an excel spreadsheet and a lock nearby

At work, the same can happen when collaborating on a shared document. It's usually an accidental keyboard stroke that does it. Unless it's done by your cat, in which case, it's not accidental – it's definitely sabotage.

While a mistake like this is understandable, it can be frustrating and time-consuming to fix. When working on shared Excel sheets, you can prevent these mishaps altogether by locking cells and protecting your worksheets.

Download 10 Excel Templates for Marketers [Free Kit]

Whether you're working on an upcoming report or planning out next quarter's budget, learn how to prevent anyone from changing or deleting important information on an Excel document.

Can you lock cells in Excel?

Yes, you can lock cells on Excel by following a few simple steps. When you lock a cell in Excel, you restrict users from making changes to your sheets. It's particularly helpful when working on a project that involves multiple team members.

For instance, let's say you're the marketing director and you've asked each channel lead (email, website, social) to report their quarterly numbers for an upcoming meeting.

You wouldn'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, which will save you a headache in the future.

Another method is locking your formula cells so that the numbers populate correctly. Jump to that section here.

How to Lock Cells in Excel

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

How to Lock Cells in Excel

2. Click on the "Home" tab of your Excel sheet.

How to Lock Cells in Excel step 1

3. Click on "Format," located on the right side of the screen.

How to Lock Cells in Excel step 2

4. Scroll down and click on "Lock Cell."

How to Lock Cells in Excel step 3

Once you complete these steps, your cells will be locked but can still be edited. To ensure the cells aren't editable, you'll also have to protect your worksheet. Find those steps in the next section.

How To Protect a Worksheet in Excel

1. Once you've locked your cells, click on the "Review" tab.

How To Protect a Worksheet in Excel step 1

2. Click on "Protect Sheet."

How To Protect a Worksheet in Excel step 2

3. Select which permissions you'd like 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, any user can click "Unprotect Sheet" to make changes to the sheet. When you add a password, only those with the code can do so.

4. Confirm that your cells are locked by looking for the "Unprotect Sheet" icon in the toolbar.

How To Protect a Worksheet in Excel step 4

You can also double-check that your worksheet is protected by attempting to write something in the locked cells. You should get this alert.

How To Protect a Worksheet in Excel step 5

How to Protect Specific Cells in Excel (& Get a Shortcut to Lock Cells)

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

How to Protect Specific Cells in Excel step 1

2. Select the "Locked" box and click "OK" on the bottom right to finish.

These simple steps offer both a shortcut to locking 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 show up if your formula is unprotected.

Excel formula error warning

Any unauthorized or accidental changes to a formula can alter the integrity of the data reported in the sheet. As such, it's important to always lock your formulas to prevent mistakes.

Here's how you protect your formulas in Excel:

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

How to Protect Formulas in Excel step 1

2. Click on the warning sign and select "Lock Cell."

How to Protect Formulas in Excel step 2

3. You must then protect your worksheet by clicking on "Protect Sheet" under the "Review" tab.

How to Protect Formulas in Excel step 3

Another way to protect your formulas is by locking each formula cell individually as you build your worksheet (as outlined here) and protecting your sheet once all formulas have been locked.

Whether you're the person fixing the mistake or the one who made it, following these steps will ensure it never happens again. And your cat will have to find something else to sabotage.

excel marketing templates

 

Topics: Excel

Related Articles

Tired of struggling with spreadsheets? These free Microsoft Excel templates can help.

    Marketing software that helps you drive revenue, save time and resources, and measure and optimize your investments — all on one easy-to-use platform

    START FREE OR GET A DEMO