### 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:**=C1**

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.

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.

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

### 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.*

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