How to Use $ in Excel Formula

  • Whatsapp

When creating formulas, there are times when someone uses cell references to retrieve a particular value. The use of cell references is usually done if the value used is not fixed or could one day be changed by changing the value of the cell that is being referenced.

Example:

You fill in a simple formula in cell C1:

C1 = A1 + B1.

In this formula, you use addresses A1 and B1 instead of taking numbers directly (not 2 + 6).

dollar-in-excel-formula-1

So, if one day, the value at A1 or B1 changes, then the value of C1 also changes.

To make the same addition formula, you can drag cells (drag the mouse) on C1 to cell C5. After you pull the results are as follows:

Formula-after-drag

If you drag (by dragging the cursor) down, you will automatically get the formula in cells C2 through C5, like in the picture above.

Using $ in the same column

Now try to place the $ (dollar) sign in front of the number like the formula in E1 then you drag it down to E5, so the results are as follows:

$-in-excel-formula

It can be seen that the function of the $ sign in the formula above is to make the numbers behind it constant. The results show that the formula values ​​from E1 to E5 are the same because the number drawn does not change or is the same, which is still directed at cells A1 and B1.

Using $ in the same row

Now consider the other example below:

dollar-in-formula-column

In the picture above, there are a series of numbers in cells H1 through N3. In column H3, there is the formula H3 = H1 + H2. Then another formula is created by dragging the cells H3 to N3 right. Automatically the numbers in other cells will change according to the sum of the numbers above it.

$-in-Formula-column

Now, I will add a $ sign to the front of the letter (cell address), like the following example:

H5 = $ H1 + $ H2. After that, I will drag to the right of cell H3 so that cells in other columns will be filled with formulas automatically, as shown below:

$-in-formula-column-2

It is seen that in columns H5 to N5, the formula used does not change or is constant. This is because of the placement of $ in front of the letters.

Now, you should be able to understand how to use dollars in formulas.

Conclusion

The function of the $ or dollar sign in a formula is to make the reference value used constant.

If you want to make a constant reference value only in rows, you can add $ in front of the number. Example: A $ 1.

If you want to make a constant reference value only in columns, you can add $ in front of the letter. Example: $ A1.

If you want to make a constant reference value in column and row, you can add a dollar sign in front of number and letter. Example: $ A $ 1.

Gravatar Image
Latest posts by Edwin (see all)

Leave a Reply

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