Home › Knowledgebase › KB-1537

How To Lock Cells In An Excel Worksheet

Summary

This guide helps you troubleshoot and resolve: How To Lock Cells In An Excel Worksheet. Follow the steps below to fix the issue.

Quick Tip: Need immediate assistance? 💻 Open a Ticket

Common Causes

Shared spreadsheets are a common tool for trades and construction businesses to track job costs, material lists, and project schedules. However, when multiple people access the same file, it is easy for someone to accidentally overwrite a formula or delete a critical value. Locking specific cells in Microsoft Excel allows you to protect important data while still permitting edits to designated input areas, such as quantities or dates.

This guide is written for trade businesses using Microsoft 365. For example, you can protect job costing formulas and material price lists while allowing site foremen to edit quantities and project schedule dates.

Quick Fix Steps

  1. Unlock all cells in the worksheet.
  2. Select the cells that users should be allowed to edit and ensure they remain unlocked.
  3. Lock all other cells that should not be edited.
  4. Protect the worksheet to apply the locking rules.
  5. Optionally, use Allow Edit Ranges to permit specific users to edit designated cells.

Detailed Instructions

Step 1: Unlock all cells first

  1. Open your Excel workbook and select the worksheet you want to protect.
  2. Click the Select All button (the corner where row numbers and column letters meet) or press Ctrl+A to select all cells.
  3. Right-click the selection and choose Format Cells from the context menu.
  4. In the Format Cells dialog box, click the Protection tab.
  5. Clear the Locked checkbox.
  6. Click OK.

Step 2: Lock the cells that should remain protected

  1. Highlight the cells or ranges that users should NOT modify (for example, job costing formulas, material price lists, or header rows).
  2. Right-click the selection and choose Format Cells from the context menu.
  3. In the Format Cells dialog box, click the Protection tab.
  4. Select the Locked checkbox.
  5. Click OK.

Note: By default, all cells in an Excel worksheet are locked. However, locking has no effect until you protect the worksheet.

Step 3: Protect the worksheet

  1. Go to the Review tab on the Excel ribbon.
  2. Click Protect Sheet.
  3. Optionally, enter a password to prevent unauthorised users from unprotecting the sheet.
  4. Under Allow all users of this worksheet to, select the actions you want to permit, such as Select unlocked cells and Select locked cells.
  5. Click OK.
  6. If you entered a password, re-enter it to confirm and click OK.

Warning: Excel worksheet protection prevents accidental changes but is not security. Passwords can be removed with third-party tools. Do not rely on a protected sheet to secure sensitive financial data; store sensitive information in a secure system with proper access controls.

Step 4: Allow specific users to edit designated ranges

  1. With the worksheet protected, go to the Review tab.
  2. Click Allow Edit Ranges.
  3. In the Allow Users to Edit Ranges dialog box, click New.
  4. Enter a title for the range.
  5. Click the range selector button and select the cells on the worksheet that specific users should be able to edit (for example, quantity columns or date fields).
  6. Optionally, enter a password for the range.
  7. Click Permissions to add specific users who can edit the range without a password.
  8. Click OK to save the range.
  9. Click OK to close the Allow Users to Edit Ranges dialog box.

Note: Allow Edit Ranges is useful for shared trade spreadsheets where site foremen need to update quantities or dates while job cost formulas and material prices remain locked.

Step 5: Test the protection

  1. Click on an unlocked cell and enter data to confirm it can be edited.
  2. Click on a locked cell. Excel should display a message stating that the cell or chart you are trying to change is protected.

Troubleshooting

I cannot edit cells that should be unlocked

The Protect Sheet button is greyed out or unavailable

I forgot the worksheet password

Allow Edit Ranges is not available

If you need further assistance, Open a Ticket.

Still Having Issues?

Need more help? If the issue persists after trying these steps, please open a support ticket and our team will assist you.

💻 Open a Ticket