«

»

Absolute Relative References

The cell reference is the code that identify a cell: A1, B3, C15, ... BJ375.

But in Excel, it's not just reference, we have absolute, relative and mixed references (but what-is-this-stuff 😲🤔🤨)

It is very important to understand this concept because very often in a spreadsheet, you have important data that must be used in your formulas: current date, invoice date, exchange rate, currency rates,...

Relative reference

A relative reference is a reference that will change when you copy it. Relative, because the reference of the cells are 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

Avoid the errors

To understand the interest of an absolute reference, we will calculate the price (including all taxes) of our sales. 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 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.

How to correct this error?

The best way to correct an error in Excel is to

  • Activate the cell with an error
  • Analyze each reference of the cells that are used the formula (look, the color are different ).

In this example, it is easy to see that the reference of the cell for the VAR changes when the formula is copied downwards.

So, to stay always on the F1 cell, we will write our formula 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 formula

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 references of the cell.

Working with mixed references is a bit more complicated. In this article we will see examples of applications and how to build these references easily.

 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/absolute-relative/


Leave a Reply

Your email address will not be published. Required fields are marked *