Get the Lock & Unlock Cells Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice locking a full sheet or only specific cells
- See how to keep input cells editable while protecting the rest
- Includes examples for unlocking by color and locking formula cells
Enter your email below for instant access.
How to Lock All Cells in Excel
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
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.
💡 Want to protect your entire Excel file with a password? Check out our detailed guide here: How to Password Protect an Excel File. Keep your data secure in just a few easy steps!
Forgot the password? See our guide on how to unprotect an Excel sheet without a password.
How to Lock Only Specific Cells (Keep Others Editable)
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’:
For input cells, consider adding drop-down lists to control what users can enter. This combines cell locking with data validation for maximum accuracy.
- 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.
Featured Course
Excel Essentials for the Real World
How to Unlock Cells by 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.
How to Lock Only Formula Cells
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.
Download the Free Practice File
Want to quickly master locking and unlocking cells in Excel? Get our free, easy-to-use workbook packed with real-life examples and practical exercises. Practice what you’ve learned and boost your Excel skills today.
Why Download?
- Real-world practice: Apply Excel locking techniques with step-by-step examples.
- Quick and easy exercises: Learn fast with simple, hands-on tasks.
- Immediate access: Download instantly and start practicing right away.
🎓 Get Your Free Workbook Now and protect your Excel sheets with confidence!
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
