What does `$`

mean in Excel? Read on to learn everything you need to know about the usage of `$`

sign in Microsoft Excel.

Have you ever noticed the `$`

sign in Excel formulas, either before column letters or row numbers? It’s also present in the Excel ribbon menu and custom cell formatting codes. But, in Excel, what does the $ exactly mean?

To understand its significance, let’s explore this Excel tutorial thoroughly and practice the exercises provided. By doing so, you’ll master the crucial skills of utilizing the `$`

symbol to manage cell references in formulas and format numbers creatively. Let’s get started!

## What Does $ Mean in Excel?

In Excel, the dollar sign `$`

is a symbol you use in formulas to make cell references absolute rather than relative. By default, there are no `$`

symbols before column and row references and all cell references are relative.

This means, that Excel automatically changes the cell references as you copy them from one place to another, fill down the column, or fill to the right end of the row.

For example, if you copy a formula syntax `=G3+J3`

from `H3`

to `P3`

, the formula becomes `=O3+R3`

.

In another example, if you fill down **column H**, the formula syntax `=G3+J3`

in `H3`

becomes `=G4+J4`

in `H4`

, and so on.

The automatic updating of cell address references in an Excel formula shall only change if you copy the cell and not the formula syntax by copying it from the formula bar.

Now, if you include `$`

signs in the formula syntax `=G3+J3`

as `=$G$3+$J$3`

Excel keeps the reference fixed when you copy the formula or fill it down in the column.

So, if you copy a formula `=$G$3+$J$3`

to `P3`

, the formula syntax still stays `=$G$3+$J$3`

. It doesn’t change to `=O3+R3`

or `=$O$3+$R$3`

.

This is useful when you want certain cell references to remain constant regardless of where you copy the formula.

Let’s consider, that if you’re calculating sales tax based on a fixed tax rate stored in the cell `A1`

, you’d want to use `$A$1`

in your formulas so that the tax rate doesn’t change when you copy the formulas to calculate tax for different items.

In summary, the dollar sign in Excel helps maintain the integrity of cell references in formulas by making them absolute. This ensures consistency and accuracy in calculations.

## How to Add $ in Formulas in Excel

Find below the most intuitive and effortless ways to add the `$`

symbol to a column letter or row number in Excel:

Go to the target cell containing a formula you want to edit.

Press `F2` on the keyboard to enter the cell edit mode. You’ll see the formula syntax in the cell along with a cursor.

Use the left and right navigation arrows to move the cursor to the left and right respectively.

Suppose, I need to make `J3`

an absolute reference in the current formula.

So, I take the cursor to the left of `J3`

, press the `Shift` key, and use the `Right arrow` key to select the entire `J3`

reference.

Now, I can press `F4` on the keyboard to convert relative reference `J3`

to absolute reference `$J$3`

.

If you again press `F4`, the column letter `J`

becomes relative and row number `3`

becomes absolute, which is `J$3`

.

Pressing the `F4` key again shall invert the mixed status of the cell reference. This time, the column letter `J`

becomes absolute and the row number `3`

becomes relative, which is `$J3`

.

If you press the `F4` key consecutively for the fourth time, Excel again converts the selected cell reference, `J3`

in this exercise, to a relative reference.

## How to Use the $ Symbol in Excel

Using the $ sign in an Excel formula becomes necessary when you’re referencing fixed row values and fixed column values. Then, you also want to have the flexibility to fill the formula down the column and to the right of the row.

The concept will become clear if you look at the above dataset.

In the above example, the exchange rate values are fixed and located along **row 2** from **column C** to **column F**.

The price data of devices in **column B** are also fixed and span from `B5`

to `B8`

.

Now, I’ll show how you can create a semi-absolute or mixed cell reference to calculate prices in different currencies using a formula.

I’ll also show you that I can fill down the formula in a column and fill it to the right of a row to copy the formula.

Excel shall not change the absolute column letters and row numbers itself. This will enable me to calculate the prices accurately by referring to fixed values.

I only need to create a formula to calculate the price in the cell `C5`

. The formula is simple. I need to multiply `B5`

, the price in USD, with `C2`

, the ratio of USD/EUR.

However, I won’t use absolute references for both `B5`

and `C2`

. Doing so will make the formula rigid and I won’t be able to fill it down or to the right.

Instead, I’ll make **column letter B** absolute and **row number 5** relative. Then, for `C2`

, I’ll make column letter relative and row number absolute. Find below the formula I’m using in `C5`

:

`=$B5*C$2`

Now, if I fill down the column or to the right of the row, Excel will only change the relative column letters and row numbers according to the destination cells.

So, my dataset will calculate the required prices in different currencies accurately by referring to both relative and absolute values.

## How to Use VBA to Add $ to Formulas in Excel

If you wish to insert the `$`

symbol in front of a column letter or row number in Excel, you can also use the following **VBA script**. It’ll guide you through the step-by-step process visually.

Often, the `F4`

key might not work or you find manually entering the `$`

key inconvenient.

In such situations, the following VBA macro comes in handy.

```
Sub ConvertToAbsolute()
Dim rng As Range
Dim strFormula As String
Dim strColumns As String
Dim strRows As String
Dim arrColumns() As String
Dim arrRows() As String
Dim i As Integer
' Get the cell with the formula
On Error Resume Next
Set rng = Application.InputBox("Select a cell", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
strFormula = rng.Formula
' Get the columns to convert
strColumns = InputBox("Current formula: " & strFormula & vbCrLf & "Enter the column letters to convert to absolute, separated by commas")
arrColumns = Split(strColumns, ",")
' Get the rows to convert
strRows = InputBox("Current formula: " & strFormula & vbCrLf & "Enter the row numbers to convert to absolute, separated by commas")
arrRows = Split(strRows, ",")
' Convert columns to absolute
For i = LBound(arrColumns) To UBound(arrColumns)
strFormula = Replace(strFormula, arrColumns(i), "$" & arrColumns(i))
Next i
' Convert rows to absolute
For i = LBound(arrRows) To UBound(arrRows)
strFormula = Replace(strFormula, arrRows(i), "$" & arrRows(i))
Next i
' Update the formula and show a confirmation dialog
rng.Formula = strFormula
MsgBox "The updated formula is: " & strFormula
End Sub
```

To learn the process of creating a VBA macro, go through this simple tutorial:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

When you run the VBA macro created using the above script, you’ll see a prompt to choose the cell containing the formula you wish to modify.

Then, you’ll find another input box showing the formula in the selected cell. In this box, you can enter the column letters before which you want to add `$`

symbols. You can enter as many column letters as you want separated by commas.

On the next dialog, you need to repeat the above step but for row numbers.

In the last dialog, Excel shall show a prompt with the updated formula.

## What Are the Other Meanings of $ Sign in Excel?

Besides using the $ symbol to transform relative references into absolute references, there are other crucial uses as well. I’ve outlined those below:

### Manually Expressing Numbers as Currencies

You’ll often find the `$`

sign as a custom number formatting code in Excel.

Suppose, you’d like to convert the numbers in **column B** to currency formatting with up to two decimal points.

Then, select the values in **column B** and press `Ctrl` + `1` to bring up the **Format Cells** dialog.

Select the **Custom** category from the left side navigation panel and enter the following number formatting code inside the **Type** field:

```
$#,##0.00_);($#,##0.00)
```

Click **OK** on the dialog to apply the code.

You shall see that Excel has transformed general numbers into a currency format.

### Accounting Number Format

In the **Number** block of the **Home** tab of the **Excel ribbon menu**, you’ll find the `$`

symbol as the default currency format.

After selecting a cell or cell range containing general number values, if you click this `$`

sign, you’ll format the values with the dollar sign and two decimal points.

Suppose, you need a different currency symbol, you need to click the `$`

**drop-down menu** and choose the option you want from **GBP**, **EUR**, **YUAN**, etc.

## Conclusions

By now, you should have found an answer to your question, “In Excel what does the `$`

mean?”

You can now differentiate absolute cell range references from relative ones.

Also, you’ve perfected your Excel skills to represent numbers in currencies using the **Format Cells** and **Accounting Number Format** command.

Did the article help you learn more about absolute and relative references? Share your supportive comment below. Did I miss anything about the `$`

sign in Excel? Don’t forget to share your feedback in the comment box.

## 0 Comments