Tags

, , , , , , , , , , , , , , , ,

In Microsoft Excel, Dollar signs ($) can be used to lock reference values in place so that the values don’t change when you drag or copy the formula across to a different cell or cells.

Dragging a formula is done by clicking the bottom right corner of the selected cell, holding the click down and then pulling in the direction you want to copy the formula being either vertically or horizontally.

 

Dragging

Dragging can also be done to multiple cells so that the cell value goes up by the same increments each time.

For example;

1  ,  2  ,  3  ,  4  ,  5

When the cells are selected, then dragged to the right the next cells would read
6  ,  7  ,  8  ,  9  ,  10  ,  etc.

 

Locking

There are 3 ways to lock a cell within a formula in Microsoft Excel:

  1. Total lock

When the formula moves, the reference cell, or cells, within the formula doesn’t move.
For example; “=$A$1” – If this formula gets dragged across to other cells then the other cells will also use Cell A1 as a reference (i.e. they will say “=$A$1”).

  1. Horizontal Lock

When the formula moves, the reference cell, or cells, within the formula doesn’t move horizontally however it will move vertically.
For example; “=$A1” – If this formula gets dragged horizontally to other cells then the other cells will use Cell A1 as a reference (i.e. they will say “=$A1”).  However when the formula gets dragged vertically to other cells then the other cells will use Cell A2 as a reference (i.e. they will say “=$A2”).

  1. Vertical lock

When the formula moves, the reference cell, or cells, within the formula doesn’t move horizontally however it will move vertically.
For example; “=A$1” – If this formula gets dragged vertically to other cells then the other cells will use Cell A1 as a reference (i.e. they will say “=A$1”).  However when the formula gets dragged vertically to other cells then the other cells will use Cell B1 as a reference (i.e. they will say “=B$1”).

An example of this is shown in the image below

Locking - Excel

These locking techniques are very useful, especially when you use them with functions in more complex excel documents.   If you start finding very different values than the values you are expecting then this is the first place to look!

Now you should be able to open a new blank document and be able to control your numbers when moving them around the sheet!!

Advertisements