Contents
How To Lock Cells In Excel:
With Excel Formulas Such-That They Don’t Change When You Paste
Has this happened to you (or a friend who has come for help), you write your excel sheet with a function(s); say summation (+) / multiplication (×) –
When you try to copy and paste the formula for other relative cells, the values change, BUT you want to maintain a constant value for all the other cells.
In this article, we show you how to prevent this. You may want to have the same constant value which is in the first cell, and not have to update the values in the relative cells (which your formula tampers with)
Read Also: How to change Google Chrome Language
Step Guide on How to lock cells with excel formulas
- In the diagram below, the cells are not yet locked. (The formula in cell H4 has affected the total for the relative cells)
2. To lock the cell H4 such that it doesn’t change when we paste it to the relative cells (the cells below) – Select the formula in the formula bar
3. Press F4. This is how your cell will look now.
4. Click save and re-paste again, your cell will be locked and your excel sheet will look like this.
Conclusion
When you Press F4 the second time, you are locking the rows.
When you Press F4 the third time, you are locking the columns. To read more about locking columns and rows for a clearer explanation, Read HERE.
The above method in the computer language is called “Relative formula referencing”.
2. With Excel Formulas, Such-That The Formulas Can’t Be Changed
You might also want to put a password (lock) on the cells which contain formulas, say for a client such that they can easily change the values of other cells BUT not tamper with the cell which has a formula.
We are going to follow a simple four (4) step process;
- Select all cells with data on your excel sheet (To unlock them)
- Go to Format cells (To uncheck the “locked”
checkbox ) - Choose ONLY the cells with formulas and protect them
- Put a lock on those cells (With your desired password)
Using the same table as the one above.
Step Guide how to lock cells with formulas in excel such that can’t be changed
1. Select ALL cells
2. Go to FORMAT CELLS in the HOME menu
3. Unmark/uncheck the locked checkbox. (It is highlighted with yellow below.)
4. After, Go to HOME
5. Click on Find & Select (it is found on the far right of the Home bar)
6. Click on Go To Special
7. Select Formulas (all cells which contain formulas will be selected automatically) and then click OK
8. After, right click on the Selected cells
9. Go to FORMAT CELLS in the HOME menu
10. From the dialogue box, Click on protection
11. Check/Mark the “Locked” check box
(Refer to image in Step-1)
12. After, Click REVIEW
13. Click Protect Sheet
14. A dialogue box will pop up
15. Enter the password in the space with a blinking cursor (Password to unprotect sheet: box)
16. Input your desired password
When someone tried to edit the formula in the FORMULA TAB, the message below shall be displayed.
Conclusion
If you want to use the worksheet design to lock and protect formula, Read more HERE.