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.
If I only want to express this one and only cell, and I don’t need to drag my formula that is O.K.
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.
An unlocked formula (below is a simple cell reference) being dragged to the right, will result in the columns incrementing within it.
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.
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
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…
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.
We created a facebook page of ExcelAngel! Check us out.