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 locks all cells, stopping editing, deletion, or reformatting.

Steps to Lock Your Worksheet

  • Go to the Review Tab: It’s at the top of Excel.
  • Protect the Sheet: Click on ‘Protect Sheet.’ Here, you can set a password to unlock the sheet later (this is optional). You can also choose what you want to allow others to do.
Lock Cells in Excel
  • Confirm: Click ‘OK’ to apply the protection.

If someone tries to change a locked cell, they’ll see a message saying the cell can’t be changed unless the sheet is unprotected.

Lock Cells in Excel

To unlock, go back to the ‘Review’ tab, click on ‘Unprotect Sheet,’ and enter the password if you’ve set one.

Lock Cells in Excel

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

To start, adjust the protection settings for cells you want to keep editable. These cells will not be locked, which means they can still be changed.

  • Select the cell or cells you want to modify.
  • Right-click, and select ‘Format Cells’:
Lock Specific Cells in Excel
  • Go to the ‘Protection’ tab, uncheck the ‘Locked’ box, and then click ‘OK’.
Lock Specific Cells

💡If multiple cells are selected and show a filled checkbox, it indicates a mix of locked and unlocked statuses among the selected cells.

Lock Specific Cells

Step 2: Activate Sheet Protection

After you’ve configured the cells:

  • Go to the “Review” tab and select “Protect Sheet” under the “Protect” group.
  • You’ll see the “Protect Sheet” dialog box. Here, you can set a password to prevent others from removing the protection (optional).
  • After setting the password (if desired), click “OK” to activate the protection.

This method locks the specified cells, preventing edits, while allowing changes to other parts of the worksheet as needed.

You can use the same steps to lock a column or a row. To lock a column in Excel, start by selecting the column or columns you want to protect. Simply click on the header of the column you wish to lock. Once selected, right-click on the highlighted columns and choose “Format Cells” from the context menu that appears. In the Format Cells dialog box, navigate to the ‘Protection’ tab and check the “Locked” box. This marks these columns for locking. Finally, activate sheet protection. You can apply the same method to lock specific rows by selecting the row headers instead of column headers.

❗ Remember, locking the cells only takes effect after you activate sheet protection, which ensures your critical data remains secure.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Unlock Specific Cells Based on Color

1. Open Find and Replace

Press Ctrl+F to open the ‘Find and Replace’ dialog box. This tool is typically used for locating and replacing text, but it can also find cells based on their format.

Unlock Specific Cells Based on Color

2. Locate Cells by Color

  • Click the small arrow next to the ‘Format…’ button and choose ‘Choose Format From Cell…’.
Unlock Specific Cells Based on Color
  • 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.
Unlock Specific Cells Based on Color

3. Select the Cells

To select all discovered cells, click on any cell reference in the list and press Ctrl+A to select all the references at once.

Unlock Specific Cells Based on Color

4. Adjust for Incomplete Selections

If not all desired cells are found, it might be due to additional formatting settings in some cells.

  • 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 some cells you do not want to lock are selected, hold the Ctrl key and click on these cells to remove them from your 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’.

By following these steps, you can efficiently unlock cells based on color, ensuring only specific parts of your worksheet are protected from changes.

💡 Ctrl + F will retain your last search settings. To clear these before a new search, click the arrow next to the ‘Format…’ button and select ‘Clear Find Format’.

Lock Only Cells with Formulas

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

Follow these streamlined steps to lock these critical cells effectively:

1. Unlock All Cells

By default, all cells are locked when you activate sheet protection. Begin by unlocking all cells to allow selective locking later:

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

2. Identify Formula Cells

Find all cells with formulas to lock them specifically:

  • Go to the ‘Home’ tab, choose ‘Find & Select’, then ‘Go To Special’.
Lock Cells with Formulas
  • Select ‘Formulas’ and click ‘OK’. This highlights all cells with formulas.
Lock Cells with Formulas

3. Lock Selected Cells with Formula

Now lock only the formula cells identified in the previous step:

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

4. Activate Sheet Protection

Finally, to ensure the settings take effect, protect the entire worksheet:

  • 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.

Download the Workbook

To enhance your learning, download our workbook. It includes hands-on examples that allow you to practice how to lock cells in Excel. Start applying what you’ve learned immediately—download the workbook here and dive into Excel practice.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.