Absolute Relative Reference – Dollar in Formula

Absolute Relative Reference – Dollar in Formula
Last Updated on 11/11/2023
Reading time: 3 minutes

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.

Relative reference of the cells

 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.

Reference with dollars

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

The Reference of the VAT is not blocked

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) 😀😍

The reference of the VAT is blocked

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 😍😎

Example Reference absolute with tax

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.

Formula with mixed reference
Formula with mixed reference

Leave a Reply

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

Absolute Relative Reference – Dollar in Formula

Reading time: 3 minutes
Last Updated on 11/11/2023

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.

Relative reference of the cells

 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.

Reference with dollars

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

The Reference of the VAT is not blocked

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) 😀😍

The reference of the VAT is blocked

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 😍😎

Example Reference absolute with tax

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.

Formula with mixed reference
Formula with mixed reference

Leave a Reply

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