How to Lock Cells in Excel - Secure Your Data

How to Lock All Cells in Excel

Locking all cells and protecting the worksheet ensures your data stays safe. It takes just a few simple steps to do this:

Step 1: Select the Entire Sheet

Press Ctrl + A or click on the triangle at the top-left corner of the sheet to select all cells.

Step 2: Check to See If Cells Are Locked

Right-click anywhere on the selected sheet and select Format Cells (or hit Ctrl + 1). Click on the Protection tab and make sure the Locked box is checked. By default, it usually is, but it is always good to double-check.

Step 3: Protect the Sheet

So now, you will click on the Review tab and click on Protect Sheet. Enter a password when prompted (you’ll be asked to confirm it).

Step 4: Select What Users Can Still Do

After selecting the password, Excel will present you with a number of possible actions, like selecting or formatting cells, that users are allowed to do. You will be able to check and uncheck what you would like to allow users to do.

Step 5: Click OK to Lock It

Click OK, and now you are done! Your sheet is protected. All cells are locked, and only the permissions you allow will work.

How To Protect a Worksheet in Excel

Let us now see how you can protect a work sheet in Excel from unwanted actions:

Step 1: Right Click the Sheet Tab

At the bottom of Excel, locate the worksheet tab. Right click it, and choose Protect Sheet from the drop down menu.

Step 2: Enter a Password

In the Protect Sheet window that appears, enter a password that will be used later to unlock the sheet.

Step 3: Choose Allowed Actions

Excel provides you with options to permit the user to perform a number of tasks such as selecting or formatting cells. Mark the boxes corresponding to the actions you would like to grant.

Step 4: Confirm and Lock

Click OK and verify the password when asked, your worksheet is now protected. You will only be able to perform the actions that were allowed.

How to Lock Specific Cells in Excel

If you want to keep certain cells from being changed, you can lock just those. Here's how to do it:

Step 1: Unlock All Cells First

• Press Ctrl + A to select the whole sheet

• Right-click and then click format cells.
• On the Protection tab, uncheck the locked checkbox and then click OK.

Step 2: Lock Only the Cells You Need

• Select the cells you want to lock.
• Right-click and choose Format Cells.
• In the Protection tab, check Locked, and click OK.

Step 3: Protect the Sheet

• Go to the Review tab and click Protect Sheet.
• Set a password and click OK.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

How to Lock Formula Cells in Excel

To lock only the formula cells in your worksheet while keeping the rest of the cells editable, follow these steps:

Step 1: Unlock All Cells

• Select the entire sheet by pressing Ctrl + A, then right-click and choose Format Cells.
• Go to the Protection tab, uncheck Locked, and click OK.

• On the Home tab, click find and select.
• Click Go To Special.

• Select Formulas and Click OK. Excel will select all the formulas in the sheet.

1. Go back to the Protection tab and checkmark the locked check box.

How to Unlock Ranges for Specific Users

With Excel’s “Allow Users to Edit Ranges” feature, you can unlock specific areas for specific people while keeping everything else locked and secure. Follow the steps below to do it:

Step 1: Open the ‘Allow Users to Edit Ranges’ Feature

Go to the Review tab on the Excel ribbon. Click Allow Edit Ranges, then click New when the window opens.

Step 2: Set Up a Range for the Sales Team

Let's assume the Sales team needs to update client orders:

• Enter a name for the cells tagged as SalesData in the New Range window.
• Under Refers to cells, type B3:E10.
• Use a password like sales789, re-enter that password to confirm and click OK to finish this step.

Step 3: Create a Range for the Inventory Team

Now allow the Inventory team to update stock levels:

• Click New again
• Name the range InventoryUpdate
• Use G3:G20 for the cell range
• Choose a password like inv123, and confirm it

Step 4: Protect the Sheet

Navigate to Review > Protect Sheet, set a password, then click okay. This means anyone trying to edit Sales or Inventory portions will now be required to provide the pertinent password for each section. 

What Sheet Protection Settings Can Control

When you protect a sheet in Excel, you can still let others do certain tasks. Excel shows a list of actions you can allow while keeping the rest of the sheet locked. The table below explains what each option means:

Common Mistakes and Tips

You can take a few precautions before locking your Excel sheet. Here are some quick tips to avoid common pitfalls:

• Protection doesn’t mean privacy

While protecting a sheet will prevent changes, it certainly does not protect the data from a user viewing it. Users will still see everything, including those who are savvy enough to see your data even if it is locked, as through the use of VBA code. Never put confidential information in a shared Excel file. 

• Don't Forget Your Passcode

In case you are using a password to protect your sheet, do remember it, as there is no facility to retrieve it from Excel. So be careful not to lose your password as Excel does not provide options for its retrieval.

• Unlock input areas first

In case there is a certain cell or cells in which you want other users to input data, make sure you unlock that cell prior to sheet protection. Otherwise, the user will not be able to enter the information you require and may think that the document is damaged or unusable.

Conclusion

While locking and unlocking Excel cells is a small step, it is an important one, it is what keeps your data organized and prevents making any changes by mistake. Whether it’s for a team sheet or for an approval workflow, controlling who can access cells can help keep people on track, and keep sensitive information safe.

If you are looking to build on your excel knowledge or want to learn how to use data in a business, consider the Business Analyst Certification offered by Simplilearn. This is a great course for beginners and it covers Excel, data tools and everything you’ll need to go get started as a business analyst.