$ Symbol in Excel

ts

$ 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 questions among all the excel beginners is, “what does the dollar ($) symbol doing inside formula”??? This is the most trending question in excel forums. I was not different from you either; I had the same doubt. In this article, we will explain to you the importance of the “dollar ($)” symbol in basic excel formula.

Cell References in Excel

Before I explain 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 three 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; instead, it was a formula, since cell C1 had a reference to the cell A1 and we moved down the cell by one 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.

It is a brief understanding of cell references. Now we will see what the $ 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 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 again get the tax percentage cell will change from A2 to A3. It 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, 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!!! It takes the same cell reference even though we copy and paste the above cell to the below cell.

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 absolute cell 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 use 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 using the F4 key, Price cell B2 moved down by one cell and taken the reference of the 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, the reference of Price should be absolute, but while moving to the right column, the reference should change.

To make these arrangements, let’s 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 the 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. To make the row cell reference absolute, press the 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 the 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, 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 a 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