What is a reference?
The reference is the address that identifies a cell: A1, B3, C15, ... BJ375. But in Excel, there are absolute, relative, and mixed references. If you have ever seen an Excel formula with dollars, that's exactly this type of references
It is very important to understand this concept to build your formulas to avoid mistakes.
Relative reference
A relative reference is a reference that will change when you copy it. Relative, because the reference of the cells is not fixed (logic 😉)
You can refer to the following article which explains with a basic example the impact of a vertical or horizontal copy on cell references.
Absolute reference
An absolute reference is a reference that will not be changed during the process of copy. We can also said that the reference is blocked or fixed.
A cell is fixed when it has 2 $ sign on both sides of the reference.
You can easily add the $ sign by pressing the key stroke F4 💡😀👍
Exercise with taxes
Let's see with the calculation of VAT the interest of the absolute reference. The VAT rate is in cell F1.
Mistake during the copy of the formula
The formula to calculate the prices with the VAT is the price of the product in E5, plus the part of the tax calculated between the price and the tax. The formula is the following
=E5*F1+E5
By copying this formula down we quickly identify that there is a problem because we have many errors in column F.
When you look at the formulas, you can see that when you have copy-paste your formula, the reference F1 has moved. That's what we need to change
How to correct this error?
In this example, it is easy to see that the reference of the cell for the VAT changes when the formula is copied downwards.
So, to stay always on the F1 cell, we will write the formula with dollars around the reference of the cell in the following way:
=E5*$F$1+E5
Now with this writing, when the formula is copied downwards, all the formulas are linked to the cell F1 (the VAT value) 😀😍
Make a test to check the formulas
Now, let's change the VAT rate to 20%.
All our formulas immediately return a new result without changing a single formula 😍😎
Simplification of the formula
You notice that we use twice the reference of the total (column E) in the formula. We can simplify the formula by using the factorization rules in mathematics.
=E5*(1+$F$1)
Reference mixed
A mixed reference is a reference that is blocked either on a column or on a line but not on the 2 elements of the reference.
