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, automatically, the row references are 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 😡
To prevent the cell reference from changing when copying, this is where you put the dollars. 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 points to references D4 and E1 (unnecessary situation).
Use only 1 dollar sign - Mixed Reference
There are some situations where you will write your formula with only one $ sign, like $A1 or A$1. This is called Mixed Reference. This article will explain the purpose of using only one dollar sign. And what is the difference between putting the dollar before the Column or Row reference?
