What does a dollar sign mean in an Excel formula?

With the dollar sign you can make a cell address in a formula absolute.When copying the formula, a cell reference no longer mutates. That can be useful.

If you copy = B2 * C2 as formula down it becomes B3 * C3 etc.

If you copy = $B $2 * $C $2 as formula, it remains the same.

So you block the dollar sign with the column and row of addresses in a formula.Only the column or only the row can of course also.

You can use this in a cell reference.A cell reference refers to a reference to one or more cells (range).

The advantage and power of cell references in formulas is that it makes the formula dynamic and only one place needs to make the change if necessary.

There are seven types of cell references where the 4 most common ones are explained extensively below.

Incidentally, you don’t have to type the dollar signs yourself.By pointing the cell with the mouse you can place the dollar signs by means of a hotkey. The function key F4. You can’t make “mistakes” because by continuing to use the F4, you’ll see the dollar signs long jump. After 4 x F4, the cell reference is again relative (see below for more info).

The relative cell reference For example, A1.As you see, no dollar signs are used, and this means that when copying the formula, the reference to A1 moves with it. From left to right it becomes B1, C1 etc. From top to bottom the A2, A3 etc.

Keyboard shortcut: Not necessary unless a cell is absolutely created by mistake (mixed).Then you can use F4 to make the cell reference again relative.

The absolute cell referencefor example $A $1.Now there is a dollar sign for both the column letter (A) and the row number (1). The dollar signs are now both blocked. No matter how you copy it, the reference always stays $A $1

Keyboard Shortcut: 1 x F4

The Mixed cell reference where the row is blockedFor example, A $1.
I
N This case, there is a dollar sign for only the row.Only the row is now blocked (read: is absolute). The column letter is relative (moves with it). When copying from left to right, the reference B $1, C $1 etc. If you copy the formula down, the reference remains A $1.

Keyboard Shortcut: 2 x F4

The mixed cell reference with the column letter blocked- for example $A 1. Now there is a dollar sign for the column letter and it has become absolute.The row number is relative (moves with it) because there is no dollar sign for it. Copying from left to right has the consequence that there is reference to $A 1, when copying from top to bottom, it is $A 2, $A 3 etc.

Keyboard Shortcut: 3 x F4

The other three species are:

A cell reference to another tab

This is a cell reference to a cell in another tab within the same work book.

A cell reference to another work book

This is a cell reference to a cell in another work book.

A 3D cell reference

This is a cell reference to the same cell or cells (read: ranges) over more than one worksheet.When you make use of this, the advice is to set the worksheets the same (not mandatory).

Leave a Reply