Absolute Relative Reference – Dollar in Formula

What is a reference?

The cell reference is the code that identify a cell: A1, B3, C15, ... BJ375. But in Excel, there is absolute, relative and mixed references (but what-is-this-stuff 😲🤔🤨). 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 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.

Relative references, no dollar here

 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 of cell 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 calculate between the price and the tax. The formula is the following

=E5*F1+E5

The reference of the cell F1 is not fixed

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

Reference absolute with dollar, no error in the formulas
Reference absolute with dollar, no error in the formulas

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 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.

Formula with mixed reference
Formula with mixed reference

Related posts


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

Leave a Reply

Your email address will not be published.