It’s painful — that moment when you have to redo your Excel sheet because someone accidentally changed something. Protecting the sheet is an option, but how do you ensure some cells are open to editing?
In this article, I’ll tell you how to lock cells in Excel and what to avoid.
Compare Top Accounting Software Leaders
Locking/Unlocking Cells in Excel
There are two ways to lock specific cells:
- Unlock the editable cells.
- Lock the cells you want to protect.
For this article, I downloaded sample Excel data from online. The screenshot below shows sales data that includes the order date, region, sales rep and item details. Column G shows the total sales, which is the product of units sold and the unit cost.
I wanted to lock the columns in blue.
How to Lock Specific Cells
Follow the given steps to lock specific cells in MS Excel.
- First, ensure the sheet is unprotected by looking for Review > Protect > Protect Sheet in the ribbon at the top. It indicates your sheet is unprotected. You can only protect what’s unprotected.
- Use the Shift + arrow keys to select the cells you want to lock, which are highlighted in blue in this case.
- Right-click to open the Format Cells popup. Alternatively, select Ctrl+1.
4. Go to the Protection tab and verify that the Locked Cells box has a checkmark for the highlighted cells. Select OK to apply the setting.
Once you protect the sheet, Excel locks every cell with the checkmark.
5. Select Review > Protect Sheet. Excel will prompt you for a password (optional). Set one and confirm it on the next dialog box. You’ve protected the sheet and locked the selected cells.
You can choose not to set a password, but it leaves your sheet vulnerable. Anyone can unprotect the sheet from the Review tab. Excel doesn’t provide a workaround for if you forget the password, so save it carefully.
Note: Many users have this question: Can I lock cells in a protected sheet? No, protection turns off all formatting options, including cell locking. You must unprotect the sheet, lock the desired cells and protect it to apply the changes.
But, there was a slip between the cup and the lip.
I could lock the desired cells, but what about the ones I wanted to leave open? I wanted Column F to be editable as I might want to price my products differently in the future.
I needed to remove that checkmark from the Locked Cells option for column F. Here’s how I went about it.
Compare Top Accounting Software Leaders
How to Unlock Specific Cells
1. Unprotect the sheet.
2. Select the column F using Shift+A. Choose Ctrl+1 or right-click to open Format cells > Protection.
2. Uncheck the box next to Locked Cells to make the desired cells editable.
3. Protect the sheet. Voila! You should be able to modify the selected cells now.
As for the locked cells, try typing on one. You’ll see this error message.
Compare Top Accounting Software Leaders
FAQs
Yes, manual selection is one way, but it can be tedious. An alternative is hiding that row or column.
- If hiding isn’t an option, apply the same formatting or color and search for all matching cells using Ctrl+F.
- Select them using Ctrl+A.
- Then select Ctrl+1 > Protection and lock all the cells.
- Protect the sheet.
You can freeze the header row or the leftmost column while scrolling. Select View > Freeze Panes and choose whether you want to freeze one or more rows or columns.
The sheet is unprotected. Select Review > Protect Sheet in the ribbon at the top.
For cell protection, Excel provides Ctrl+1 and right-click actions to open the Format Cells popup. Protection is available at the sheet, workbook and cell levels.
No, they’re locked by default in a protected sheet. But it’s an excellent practice to unlock cells before protecting the sheet, as they might require editing later.
Compare Top Accounting Software Leaders
Next Steps
Now that you know how to lock cells in Excel, want to know more about it? Check out our guides on how to merge cells in Excel and Excel for business intelligence.
Do you have any other tips and tricks for managing spreadsheets? Please let us know in the comments below!
Trending Articles
- How To Install OptiFine: Ultimate Guide
- How to Download YouTube Videos
- How To Change Your Name On Facebook
- How To Reset AirPods