Excel: How to lock a cell in a formula

Excel: How to lock a cell in a formula

Working on excel is indeed a great experience. A plethora of mathematical operations can be carried out using excel. Virtually all operations have their unique formula, and all the cells have their specific formulas. Imagine working on an excel spreadsheet, and you mistakenly delete a cell, or you mistakenly press a button. This can cause a serious error in your work if you don’t identify it early. This underlines the reasons why you need to lock and protect your cells.

Now let’s take a look at simple formulas that will lock your cells so that they will not shift, adjust, or become erroneous when you mistakenly make a click.

Your cells can adjust when you paste the given formula in other places in the doc or in other cells. You can curb this by adding $ in front of your row or the column value. The process of adding $ to the front of rows or columns is known as anchoring, and it is compatible with all versions of Excel.

 

Let’s assume you want to lock cell A1.

When unlocked, we can have the formula:

=SUM(A1: B2)

You can add $ to lock cell A1. When locked, we will have:

=SUM($A$1:B2)

Now, you can decide to anchor either of the row or column. In the formula above, we anchored both the row and the column, but you can anchor them singularly by adding $ to one.

=SUM($A1: B2) has an anchored row because $ is added to the front of A.

=SUM(A$1:B2) has an anchored column because $ is added after A

Note that $ is very efficient in locking cells when they are entered manually. Your spreadsheet should be using the standard A1 Row/Column notation. You can quickly toggle between the locking the column, rows, or the entire cell bay pressing the F4 key. By pressing the F4 key, you will toggle between the four possible locking options, which are A1, $A1, $A$1, and A$1.

Now let’s consider the notation: A1: B2.

If you place your cursor at the end of the array and press F4, all your array values will be locked immediately – A1B2, $A$1:$B$2, A$1: B$2, $A1:$B2.

On the whole, you will not be able to protect your cell if you cannot lock it. Follow the instructions above, and you will be able to lock and protect your cells. Thanks for reading!