How to Lock Cells in Excel or Protect Sheet

How to Lock All Cells in an Excel Worksheet

When you have crucial data in Excel, you might want to prevent changes to keep it accurate. A good method is to protect your entire worksheet. This lock cells in excel, stopping editing, deletion, or reformatting.

Steps to Lock Your Worksheet

1. Open the Review Tab

At the top of your Excel window, click on the Review tab. This is where you’ll find options to protect your sheet.

2. Choose ‘Protect Sheet’

Click on Protect Sheet. You’ll have the option to set a password (optional).

This way, only those with the password can unlock and make changes to the sheet.

You can also decide what other users are allowed to do, like selecting or viewing cells.

3. Confirm Protection

After choosing your settings, click OK. Your worksheet is now locked! If someone tries to edit a locked cell, they’ll get a message telling them it’s protected.

How to Unlock Your Worksheet

Need to make changes? Go back to the Review tab, click Unprotect Sheet, and enter your password if you’ve set one.

How to Lock Specific Cells

In other cases, you may need to protect only specific cells. This prevents changes while allowing edits to others.

For example, in a budget spreadsheet, you might allow changes to the expense values. But, you would not allow changes to the categories or the formulas that calculate the totals.

Here’s a simple way to lock only specific cells in your worksheet:

Step 1: Set Cell Protection Status

First, decide which cells you want to remain editable. By default, all cells in Excel are “locked,” but that doesn’t take effect until sheet protection is activated.

• Select the cell or cells you want to keep editable.
• Right-click, and select ‘Format Cells’:

• Go to the ‘Protection’ tab, uncheck the ‘Locked’ box, and then click ‘OK’.

💡Pro Tip: If you see a mixed checkbox in the Locked option, it means some of your selected cells are already locked while others are not.

Step 2: Activate Sheet Protection

Now, it’s time to lock the specific cells.

• Go to the “Review” tab and select “Protect Sheet” under the “Protect” group.

• You can set a password to ensure only authorized users can unlock the sheet (this step is optional).

• Click OK to activate protection.

Once done, only the cells you didn’t unlock can be edited.

Locking Columns and Rows

You can also lock entire columns or rows using the same steps:

• To lock a column: Select the column header, right-click, and choose Format Cells. Under the Protection tab, make sure the Locked box is checked. Then, activate sheet protection as before.
• To lock a row: Follow the same steps but select the row header instead.

❗ Remember, locking the cells only takes effect after you activate sheet protection.

Unlock Specific Cells Based on Color

In this example, our goal is to keep all blue cells accessible for input. Manually selecting all these cells can be tedious and time-consuming.

Instead, we’ll utilize Excel’s built-in features to automatically identify and select all the blue cells for us.

1. Open Find and Replace

Press Ctrl + F to open the ‘Find and Replace’ dialog box. While this tool is usually for finding text, it can also locate cells based on their format, including color.

2. Find Cells by Color

• Click the small arrow next to the ‘Format…’ button and choose ‘Choose Format From Cell…’.

• Your cursor will change to a pointer with an eyedropper. Click on a cell with the blue color you want to find.

• Then, click ‘Find All’ in the dialog box.

3. Select All Cells

Once the cells are listed, click on any reference in the list and press Ctrl + A to select all the found cells at once.

4. Adjust for Missing Cells

If some cells are missing from the selection, it may be due to different formatting.

• To search by color only, go back to ‘Format…’ in the ‘Find and Replace’ dialog, select the ‘Fill‘ tab, choose the appropriate color, and click ‘OK’.
• Then click ‘Find All’ again.

5. Deselect Unwanted Cells

If there are cells selected that you don’t want to unlock, hold Ctrl and click on them to remove them from the selection.

6. Unlock the Selected Cells

• With the cells selected, press Ctrl + 1 to open the ‘Format Cells’ dialog.
• Go to the ‘Protection’ tab and uncheck ‘Locked’.
• Click OK, and your selected cells will now be unlocked while the rest of the worksheet remains protected.

💡 Pro Tip: If you’ve previously searched using Find and Replace, it might retain those settings. Before starting a new search, click the arrow next to the Format… button and select Clear Find Format to reset the search parameters.

Lock Only Cells with Formulas

Protecting formula cells in your spreadsheet is crucial. It maintains the integrity of your calculations and prevents accidental changes.

Locking these cells ensures they stay intact, even when the rest of the sheet is editable. Follow this easy guide to lock only your formula cells while keeping everything else accessible.

1. Unlock All Cells First

Excel locks all cells by default when you protect a sheet. To lock only your formula cells, you need to unlock everything else first.

• Press Ctrl + A to select all cells.
• Right-click and select ‘Format Cells’.
• In the ‘Protection’ tab, uncheck ‘Locked’ and click ‘OK’.

2. Find and Select Formula Cells

Now, it’s time to identify the cells containing formulas so you can protect them.

• Go to the ‘Home’ tab, choose ‘Find & Select’, then ‘Go To Special’.

• Select ‘Formulas’ and click ‘OK’. This highlights all cells with formulas.

3. Lock Selected Cells with Formula

With your formula cells highlighted, it’s time to lock them.

• With the formula cells selected, right-click and choose ‘Format Cells’.
• In the ‘Protection’ tab, check ‘Locked’ and then click ‘OK’.

4. Activate Sheet Protection

Now that your formula cells are locked, protect your worksheet to make the changes permanent.

• Go to the ‘Review’ tab and select ‘Protect Sheet’.
• You can set a password here to prevent others from unlocking the sheet without authorization (optional).
• Click ‘OK’ to activate protection.

for more tricks view more

for our channel EasyExcel