Formulas are effective to sort out mathematical calculations in Microsoft Excel. These formulas help to calculate data without any help of a calculator or wasting any time. Sometimes we need to lock the formulas to add data or continue calculation without putting the same formula again and again. But many of us aren’t acknowledged about how to lock formula in excel. Here I will show you locking formula in excel in a few simple steps. These steps are pretty easy once when you try.
Suppose the formula is: A1 (column) + B1 (column) = C1. Then when you add data to A1 and B1, you will get the additional result in C1 column. This is just an example of formula. So, you want to lock this formula.
Firstly, you need to select and unlock all the cells from the worksheet. You can select them simply by pressing Ctrl and A button together on the keyboard. Then right click on your mouse or press ctrl and 1 together. This will show you a format cell dialog box. Click on the Protection tab. Now uncheck the Locked option and press Ok. By doing so, all the cells of the worksheet will be unlocked.
Secondly, after unlocking all the cells, you need to select all the cells containing your given formulas. Select all the cells including the formula cells by pressing Ctrl and A button. Now move to the Home tab (on the top of the menu) and look for Find & Select in the editing group. Click on the Find & Select and you will see a drop-down list. Click on the Go To Special option and there will appear a box. Select formula and click Ok. This step is done now.
The third or final step of locking the formula cells in excel comes now. Press Ctrl and 1 to open the format cells dialog box. Then go to the Protection tab (similar to step 1) and check the Locked option. Then click on Ok. This is how you can lock formulas in excel. If you want to do this for the entire worksheet you need to go for the next steps, I am going to tell you.
Go to the Review tab to protect the excel worksheet. There you will see Protect Sheet in the changes group. Now, you will have a dialog box after clicking on Protect Sheet. Check the Protect worksheet and contents of the locked cells if it is unchecked. Here you will see a blank space which will allow you set a password to protect or unprotect the worksheet. Then click on Ok.
The locked formula in excel is always visible in formula bar unless you hide them. First, lock all the formula cells doing the second step I’ve already told. Then do the third step. Here you will see the Hidden option below the Locked option. Then check it to hide the cells with formulas. Then click the Ok. Now you can work on the worksheet without seeing the formula bars all the time. Unchecking the options will make the sheet as usual again.
So, you can see how to lock formula in excel is so much easier.