The dollar sign ($) is used as an absolute reference marker in Excel formulas. There are 3 situations.
- Relative reference: This is when the reference of the cell is written without a dollar sign, like A1
- Absolute reference: This is when there are 2 dollar signs in the reference, like $A$1
- Mixed reference: This is when you write your formula with only one dollar sign, like $A1 or A$1
The key point is the action of copying a formula in Excel. When you copy down a formula, the row references are automatically increased.
No dollar sign – Relative reference
Let’s take this example where, in D4, you have the multiplication between the Quantity and Unit Price.
When we copy the formula with the fill handle, each formula points to the correct cell. In row 5, the formula points to B5 and C5, and so on for the other rows. When there is no dollar in the cell’s reference in a formula, it is called Relative Reference. But, in some situations, you don’t want the reference of a cell changes.
Two dollars sign – Absolute Reference
Now, let’s take this second situation where we want to calculate the price with tax. The tax is in E1.
This time, when we copy down our formula there is a BIG problem because now you have many errors. The reason is that when we copied down the formula in E4, all the references changed, including the reference E1 😡
This is where you put the dollars to prevent the cell reference from changing when copying. This is called Absolute Reference. To add the two $ signs quickly, press the F4 key after selecting your cell.
In this situation, we use 2 cells in our formula. But one is a relative reference, and the other an absolute reference. Now, what happens if we write 2 absolute references?
As you can see, each new formula always references D4 and E1 (unnecessary situation).
Use only 1 dollar sign – Mixed Reference
In some situations, you will write your formula with only one $ sign, like $A1 or A$1. This is called Mixed Reference. In this demo, we built a formula in C4. When copied to 99 cells, each formula points correctly to the row and column headers 😮👍
