$ Symbol in Excel | Why use $ (Dollar) Symbol in Excel Formula?

$ Symbol in excel is used to lock a specific cell or rows or columns in a worksheet, the shortcut to lock down a reference in excel is by pressing ALT + F4, this feature is used while working in formulas when we do not want the reference to be changed when we copy or drag down the formula to other cell references, this feature will keep the reference same for all the further calculations.

$ Symbol in Excel Formula

One of the common question among all the excel beginners is “what does dollar ($) symbol doing inside the formula”??? This is the most trending question in excel forums. I was not different from you either, I had the same doubt. Ok, in this article we will explain to you the importance of “dollar ($)” symbol in excel formula.

Cell References in Excel

Before I explain to you about the $ symbol let me brief you about cell references in excel. For example, I have a value of 100, 200, and 300 in the first 3 cells.

Now in cell C1, I will give a link to cell A1.

So, now the C1 cell is dependent on cell A1. Whatever happens in cell A1 will directly have an impact on cell C1. Now if I copy and paste the cell C1 to C2, as a new learner we think we will get the value of 100 only but that is not the case here.

Look what we got here, instead of 100 we got 200.

If you look at the formula bar it says A2, the reason why we got A2 because when I copied the cell C1 it was not valued rather it was a formula, since cell C1 had a reference to the cell A1 and we moved down the cell by 1 it changed from cell A1 to A2, so we got the value of cell A2.

Similarity when we copy the cell reference cell based on how many cells we move down and how many cells we move either to left or right cell reference changes accordingly.

This is a brief understanding of cell references. Now we will see what $ symbol does with excel formulas.

How to Use $ Symbol in Excel formula? (with Examples)

Let’s see some simple examples of the $ symbol in excel formula.

You can download this $ Symbol Excel Template here – $ Symbol Excel Template

Example #1

For an example look at the below data set.

In the above data, we have Tax Rate in cell A2 as 5%. In the column “B” we have cost price. To arrive at the Tax Amount we need to multiply the Cost amount with Tax percentage. Apply the formula in cell C2 as B2 * A2 to get tax amount.

Since we have given a reference to cells I will copy and paste the formula down to the below cell.

This time we got 0 as a result. Now press F2 on the cell to debug the formula.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)

Course Price

View Course

Now Tax percentage cell has been changed from A2 to A3. So change the tax amount reference from A3 to A2 again.

Now if I copy and paste this cell again we will get again the tax percentage cell will change from A2 to A3. This will be the case with the remaining cells as well.

So, how do we deal with it???

Can we do this for all the cells?

What if there are a thousand cells??

These are the questions everyone will get who don’t know about the $ symbol in excel formula.

Now, the ($) dollar symbol in excel formula plays a vital role here. In a cell, C2 opens the formula.

Now give the cell reference to the A2 cell.

After giving the cell reference to cell A2 press F4 key and see the magic.

Wow!!! After all these days now you got to know the technique of inserting a Dollar ($) symbol in excel formula for cell reference. I am sure you must have tried entering the $ symbol manually!!!

Press enter to have a value of Tax Amount.

Now copy and paste the formula to the below cell and see the magic.

Wow!!! Now it is taking the same cell reference even though we copy and paste the above cell to below cell.

The reason why it is taking the same cell reference because since we have pressed the F4 key it has inserted a ($) dollar symbol in excel made the cell as an Absolute Reference cell.

It doesn’t matter where you are pasting in the worksheet it will still take the reference of the cell A2 only.

Now copy and paste to all the other cells to have an accurate tax amount.

Now we have seen how to make the cell absolute reference by pressing the F4 key which inserted a ($) dollar symbol in excel. Once the absolute reference sets it will still take the same cell reference.

We can two absolute references i.e. absolute row reference and absolute column reference.

Example #2

For an example look at the below data.

In column A we have unit’s data and in row 2 we have price data. In the empty boxes, we need to arrive at the sales amount by multiplying Units * Price.

Let’s apply the formula.

To arrive at the first sales price I have applied the formula as B2 to A3. Now I will copy and paste the formula to the below cell.

Since we have not locked the cell by using F4 key Price cell B2 moved down by one cell and taken the reference of B3 cell.

While applying the formula we need to decide on two things.

First, we need to decide while moving to the left Column reference of Units should be absolute but while moving down row reference should change.

The second thing is while moving from top to bottom row reference of Price should be absolute but while moving to the right column reference should change.

To make these arrangements lets open the formula in B3 cell and select B2 cell i.e. Price.

Now press the F4 key to make it an absolute reference.

This is absolute reference now, but for Price row while moving from top to bottom row reference of Price should be absolute but while moving to the right column reference should change. In order to make the row cell reference absolute press F4 key one more time.

As you can see from two it has changed to one dollar i.e. in front of the row number.

Now put multiply sign and select A3 cell.

With this column we need to make the column cell reference absolute, so press the F4 key thrice to make the ($) dollar symbol come in front of a column header in excel.

Press Enter key to have a result. Copy and paste the formula to other cells to have sales value.

Like this, by using a ($) dollar symbol in excel formula we can create absolute cell reference, absolute row reference, an absolute column reference.

Excel

Financial Functions Excel

Lookup Functions Excel

Excel Charts

VBA

Others

Certification Courses

Excel VBA All in One Bundle

Excel Data Analysis Course

VBA Macros Course