$ Symbol in Excel

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

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

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

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

Recommended Articles

This has been a guide to Why Use $ in Excel. Here we discuss Examples of the $ symbol in excel formula using row & column references along with downloadable excel template. You may also look at these useful functions in excel –

• Methods to Insert Delta Symbol in Excel
• Disable Excel Scroll Lock
• Excel AVERAGEIFS Function
• Excel Minus Formula