Why do we need this?

Did you have a situation where you painstakingly constructed a formula, that was working well and good.
Only to find out that your boss wants you to insert a few rows here and there.

Aaaargh. Pain in the butt.

When I was new to Excel I had to quickly realise that there is a way to avoid all the hassle around the formulas I write. Whenever I was looking for information about formulas online, I came across the “lock” reference.
Unfortunately there is not much information about what is this so called locking. And of course why, where and how would I use it?

“Locking” refers to a cell’s address to be precise.
If I go to Excel I can type in to cell A1 the following simplest formula:

This would mean that the value I want to see In cell A1 is whatever is in C1.
Simples right?
If I only want to express this one and only cell, and I don’t need to drag my formula that is O.K.

Locking excel formulas
Dragging an excel formula without locking it

But what if I would like to drag this simple formula down a few rows, and I don’t want to change the reference?
Lets say that I want all the dragged rows to point to C1.

Then I need to “lock” the cell within my formula.

What does it mean to “lock” a cell within a formula?

It means that you govern, that when a formula that contains the cell is being dragged, WHich way you allowing the cell address to increment.

For Example:

An unlocked formula (below is a simple cell reference) being dragged to the right, will result in the columns incrementing within it.

Locking excel formulas
What happens when we drag an excel formula without locking the cell.

As you can see we can drag a formula in a cell or an array by hovering over to the bottom right handle of the selected cell.
The cursor will then change to a Cross. This means that you will drag the content of the formula.

Locking excel formulas

How to lock a cell within a formula in Excel?

In Excel the indicator of a cell’s address is being locked
in a direction is a $ sign You probably seen formulas where the cell reference looked something like this:
With the locks: =$A$1:$A$10
Without the locks: =A1:A10

The $ sign tells us that in which way a cell reference is locked.

There is 4 ways a cell reference can be locked:

$ sign is in front of the column letter AND the row number:
$A$1 You can drag the formula anyway, the reference will not change

$ sign is in front of the Row number:
A$1 If you drag down the row number will not change

$ sign is in front of Column letter:
$A1 If you drag to the right the column letter will not

No $ sign in the cell reference:
A1 You can drag the formula in any way you want

Confusing right?

I know. It seems like a mess. And who will remember to type all of this $ signs to the right place, at the right order…

But in reality this is one of the most important thing when you working in excel. We use it constantly here or here.

The F4 key in Excel

This is a very important topic.
If you want to work comfortably in excel you need to start using the F4 key. Get used to lock your formulas as a second nature.
The F4 key cycles through the four state of lockings in a cell reference.

Below we gonna play a little bit with an empty worksheet just to get a feel for what happens when we cycle through different ways of locking.

Note: I will hit F2 to jump inside a formula in a cell. This will bring up the color around the cell to show where a formula is pointing.

Locking excel formulas
Locking a cell reference once. (in every direction) press F4 once.
Locking excel formulas
Locking a cell twice (locking only the row reference) press F4 twice
Locking excel formulas
Locking a cell reference three times (only locking column) press F4 three times
Locking excel formulas
Not locking a cell reference. You can drag it any way you want it.

We created a facebook page of ExcelAngel! Check us out.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]