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: " & strFormulaEnd 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.
