Excel Lock Formula (Table of Contents)
Lock Formula in Excel
Lock formulas is an excel function used to protect the formulas inserted in cells so that when the file is shared across recipients they won’t be able to change or delete the formula. Also, the lock function is useful to hide your work from others, especially when you share it with your client and you do not want them to know how you have got the values.
How to Lock and Protect Formulas?
It is a very simple and easy task to lock and protect formulas. Let’s understand how to lock and protect the formulas with an example.
Excel Lock Formula – Example #1
Consider the below example, which shows data of the sales team members.
In the below image, in column D the total has been calculated by inserting the formula =B2+C2 in cell D2.
The Result will be as shown below:
The formula in the total column has been copied from cells D2:D5.
In this example, we are going to lock the formula entered in column D. So let us see the steps to lock and protect the formulas.
4.8 (1,645 ratings)
View Course
- Select all the cells by pressing Ctrl+A and unlock it.
- Select the cells or the entire columns or rows, where you need to apply the formula.
- Lock the cells which contain the formula.
- Protect the worksheet.
Let us in detail show how are the above steps executed.
Step 1: Unlocking all the cells
The cells in excel are protected and locked in excel. As we need to lock particular cells in the workbook, it is necessary to unlock all the cells. So let us see how to unlock all the cells. The steps to unlock all the cells are as follows:
- Press Ctrl+A to select the entire worksheet.
- Right Click and select Format Cells from the options appeared in the context menu.
- Select the Protection tab and uncheck the Locked and Hidden option as well and then click OK.
Step 2: Select and lock the cells containing the formula
Now here we need to lock the cells where we have entered the formula. The steps to lock the cells containing formula in excel are as follows:
- Select all the cells in the worksheet by pressing Ctrl +A.
- Go to the Home tab and select Find & Select option from the Editing menu.
- After selecting the Find & Select option, other options will appear under it, from which select Go To Special, option.
- Go To Special dialog box will appear as shown below.
- In which we have to select the Formulas option and also check all the options under the Formulas button are ticked and then click OK.
Step 3: Protection of the Worksheet
This function is used to ensure that locked property is enabled not only for cells with formulas but also for all the cells in the workbook. Let us see the steps followed to implement the protection for the worksheet:
- Go to the Review tab and select Protect Sheet option.
- After this, the Protect Sheet dialog box will appear.
- In which make sure that “Protect Worksheet and contents of locked cells” is selected.
The user can also type a password in the text box under the Password to unprotect sheet, in order to make the worksheet safer.
Advantages of Lock Formulas in Excel
- It helps the user to keep their data secure from others when they send their files to other recipients.
- Helps the user to hide their work when the file is shared with other readers and users.
- The user can use a password in the case to protect the entire workbook, which can be written in the text box named, ‘Password to unprotect sheet’.
Disadvantages of Lock Formulas in Excel
- A new user, won’t be able to understand the function in excel easily.
- There are cases when it becomes difficult if the user forgets to enter the password to unprotect the file.
- It is sometimes not an efficient way, in terms of time as it consumes the time of a user to protect and unprotect the cells of the worksheet.
Things to Remember
- All the cells are protected by default, do not forget to unlock the cells in order to lock formulas in excel.
- After locking formulas in excel, make sure to lock the worksheet again.
- The entire workbook can be protected, by using the option restricted or unrestricted access from the “Protect Workbook” option.
- In case if the user needs to hide their work or formulas from others, they can tick the option “Hidden” by selecting the “Protection” tab from “Format Cells” dialog box.
- In case the user needs to unprotect the complete file, simply type the password by selecting the “Unprotect Sheet” option.
- A user can save time by moving the formulas to separate worksheet and then hiding it, instead of wasting time on protecting and unprotecting the worksheet.
Recommended Articles
This has been a guide to Lock Formula in Excel. Here we discuss how to Lock and Protect Excel Formula along with practical examples and downloadable excel template. You can also go through our other suggested articles –
