How To Lock Cells With Formulas In Excel

By
Emmanuel Kasomi
-
- Advertisement -
JOIN UGTECHMAG ON TELEGRAM

Contents

- Advertisement -

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

  1. In the diagram below, the cells are not yet locked. (The formula in cell H4 has affected the total for the relative cells)
lock cells with formulas in excel 1

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” )
  • 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 you do, you will be prompted to confirm it as shown below.

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.