Accounting How To Lock Cells In Excel: Ultimate Guide By Ritinder Kaur Accounting No comments Last Reviewed: July 12, 2024 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. In This Article Toggle Locking/Unlocking Cells in ExcelHow to Lock Specific CellsHow to Unlock Specific CellsFAQsNext StepsTrending ArticlesRelated Articles 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 Can I lock or protect specific rows or columns? 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. How can I lock cells while scrolling? 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. I locked some cells but can still edit them. Why? 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. Should I lock cells before protecting the sheet? 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 Related Articles How to Freeze a Row in Excel: Ultimate Guide How to Remove Duplicates in Excel Ritinder KaurHow To Lock Cells In Excel: Ultimate Guide07.08.2024