How To Lock Excel Cells With Formulas To Prevent Editing
MS Excel is an exceptionally powerful tool. If you can code, Excel is likely of little consequence to you. For those who can’t code, this app is heaven sent. It offers a wide range of features and can be used in a myriad of industries where workers are more likely to be familiar with it than with another brand solution. If you know your way around Excel, you can probably create custom sheets that streamline repetitive tasks. Obviously, you’d make use of the extensive formulas and formatting tools in Excel. At the end of the day, there’s just one thing you need to worry about; other people modifying your sheet. The good news is, you can lock Excel formulas to prevent editing while still allowing users to edit the sheet.
Lock Excel Cells
Open an Excel file and add the formulas and formatting you need to your sheet. Save the changes you’ve made. Next, select the cells that have formulas added to them. We’re assuming that you want other people to be able to edit the variable values but not the formulas that are going to be applied to them.
In the image above, the cells B2 and B3 are variable cells. The cell B4 contains the formula that will sum the cells B2 and B3. You want users to be able to input any value in the cells B2 and B3 but you don’t want them to accidentally (or intentionally) edit the sum formula.
Right-click inside the cell that contains the formula and select ‘Format cells’ from the context menu.
A new window will open. Go to the ‘Protection’ tab on this window and enable both the Lock and Hidden option.
Once you’re done, close this window and return to the main Excel window. Go to the Review tab and click the ‘Protect sheet’ button. Make sure the Protect worksheet and contents of locked cells’ option is checked. You can enter a password to make sure no one can unlock the cells but it isn’t necessary. If you think not being able to view the formula is enough to deter someone, you can skip the password. If not, go ahead and add one.
Unlock Excel Cells
To unlock Excel cells, go to the Review tab and click Unprotect sheet. If the author of the sheet has added a password to protect it, you will need to enter the password in order to unlock the cells. If there’s no password, you will be able to edit the locked cells after clicking the unprotect sheet button.
