4 Best Methods to Lock Cells in Excel
Note: This guide on how to Lock Cells in Excel is suitable for Microsoft Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 and Office 365 users
Imagine this, you are working hard on a project with an Excel worksheet containing important data. You share it with your team and for some reason, you later find out some of the important information is missing. It’s frustrating, isn’t it?
That’s why it is important to know about locking cells in Excel. It will prevent these unintended errors and save you valuable time.
In this guide, I’ll talk about how to lock cells in Excel the easy way, so that you can share your spreadsheets without worries.
You’ll learn:
- How to Lock Cells In Excel?
- How to Lock Specific Cells In Excel?
- How to Lock Formula Cells In a Sheet?
- How To Unlock Ranges In a Spreadsheet?
I’ll explain each of these with clear examples below.
Related:
How To Protect Cells In Excel Workbooks-the Easiest Way
Excel Goal Seek—the Easiest Guide (3 Examples)
Create A Pivot Table In Excel—the Easiest Guide
How to Lock Cells In Excel?
Before I begin, keep in mind that all cells in an Excel worksheet are locked by default. But, it will not have any effect until you protect the Worksheet.
With that out of the way, let’s see how to protect cells in a sheet easily. Follow these steps.
- Select all using Ctrl+A
- Right-click anywhere on the sheet and choose Format Cells. Do this easily using the Ctrl+1 shortcut.
- In the next window, click on the protection tab
- Make sure the click box next to “Locked” is selected. This is to check if they are locked cells by default.
5. To enforce this Protect your Worksheet. The easiest way to do this is to right-click on the Sheet Name > Click on Protect Sheet > Set Password > Set the allowed actions for users > Re-Confirm Password .
You can also access the same window under the Review tab in the “Protect” group
.
Also Read:
Excel Conditional Formatting -the Best Guide (Bonus Video)
The Best Excel Project Management Template In 2021
How To Use Excel Countifs: The Best Guide
How to Lock Specific Cells In Excel?
The same steps apply for Locking specific cells in Excel with a slight change.
- First, select all cells in the sheet and go to the formatting window.
- Make sure all cells are Unlocked cells by unchecking the locked box under the Protection tab.
- Select the specific cells or range of cells that you want to lock
- Again go to the Protection Tab under the formatting cells window.
- Tick the Locked Check box
- Protect the sheet by setting a password. (As explained in the previous example)
How to Lock Formula Cells In a Sheet?
To lock formula cells in Excel, follow these slightly different steps.
- First, select all cells in the sheet and go to the formatting cells window.
- Make sure all cells are unlocked by unchecking the locked box under the Protection tab.
- In the “Home Tab” locate and click on “Go To Special” under “Find & Select”
- In the Go To Special window select “Formulas” and click OK. This will make Excel select all formula cells.
- Again go to the Format Cells window and Check the “Lock Cells” option under Protection Tabotection Tab
- Protect the sheet by setting a password. (As explained in the previous example)
All your formula cells are now locked and cannot be edited. .
How To Unlock Ranges In a Spreadsheet?
To unlock specific ranges in a protected spreadsheet, follow these steps.
- Locate the “Allow Edit Ranges” option under the Protect group in the Review section of the Excel ribbon.
- Click on New under the “Ranges unlocked by a password when sheet is protected” option.
- Enter a title for your range, specify your range and range password in the New Range window.
- Click on Permissions to choose which users to allow
- Under Permissions for Range1 windows add Users you want to allow and choose if Range password is compulsory for them.
- Click Apply and OK. Re-enter password for confirmation.
- Protect your Worksheet to enforce this new rule.
Congratulations! From now on only users who have access will be able to edit the specified range of cells after entering the password.
Suggested Reads:
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
FAQs
How do I make a cell non-editable in Excel?
Locking cells in Excel makes them non-editable by default. First, make sure the cells are unlocked by checking them under the Format cells menu. Then select the cell/range of cells you want to lock, locking them again under the format cells menu.
Enforce this by protecting the worksheet. Your select cells are non-editable now, without a password.
How do you lock formulas in Excel but allow data entry?
For locking cells containing formulas, but allowing other data entry, select Go To special under “Find and Select” in the Home section of the ribbon menu. Select formulas under the Go To special window.
This will select only the cells that contain formulas. Now, go to the Format cells dialogue box with shortcut Ctrl+1. Under the Protection Tab, select Lock Cells and click OK. Now enforce this rule by protecting the Worksheet with a password.
Now, users can enter data, but cannot edit the formulas.
Let’s Wrap Up
In this guide, I have explained how to lock cells in Excel. It covers all important features that will come in handy when you collaborate with your co-workers. You can use these methods to protect cells in your sheet.
For more high-quality guides on Excel do check out our free Excel resources centre.
Do you want to master your Excel Skills? Click here for in-depth courses that explain everything you need.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
