Why there is dollar in the Excel formula

Last Updated on 27/02/2024
Reading time: 2 minutes

In Excel formulas, the dollar sign ($) is used as an absolute reference marker. There are 3 situations

  1. Relative reference

    This is when the reference of the cell is written without a dollar sign, like A1

  2. Absolute reference

    This is when there are 2 dollar signs in the reference, like $A$1

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

The reference of the row have changed after the copy of the formula

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

The reference of the cell E1 is not blocked

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, you can press the F4 key after selecting your cell.

All the formula point to the cell E1

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?

Each formula point to the same cells

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 to you easily the purpose of putting only one dollar sign. And what is the difference between putting the dollar before the Column or Row reference?

Leave a Reply

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

Why there is dollar in the Excel formula

Reading time: 2 minutes
Last Updated on 27/02/2024

In Excel formulas, the dollar sign ($) is used as an absolute reference marker. There are 3 situations

  1. Relative reference

    This is when the reference of the cell is written without a dollar sign, like A1

  2. Absolute reference

    This is when there are 2 dollar signs in the reference, like $A$1

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

The reference of the row have changed after the copy of the formula

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

The reference of the cell E1 is not blocked

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, you can press the F4 key after selecting your cell.

All the formula point to the cell E1

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?

Each formula point to the same cells

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 to you easily the purpose of putting only one dollar sign. And what is the difference between putting the dollar before the Column or Row reference?

Leave a Reply

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