No doubt, Microsoft Excel stands as an unrivaled giant in the realms of spreadsheet software. Amidst its vast array of features, one symbol stands out the most, commanding both attention and curiosity – the ubiquitous dollar sign ($).
From novices to seasoned Excel users, this enigmatic dollar sign sparks intrigue about its significance and uses.
In this post, we will unveil the meaning of the dollar sign in Excel and describe the role it plays within Excel’s dynamic framework.
Meaning and Significance of the Dollar Sign ($) In Excel
In Excel, the dollar sign ($) serves two purposes. One is for absolute cell referencing and the other is for currency formatting. When you add a dollar sign before the column letter and/or row number of a cell reference (e.g., $A$1), it creates an absolute reference.
This means that if you copy the formula containing this reference to other cells, the reference will not change, and it will always lock to the same cell.
To know more about cell referencing, we have to dive deeper into the topic (more on that in the next section).
Another meaning of the dollar symbol is defining the currency. Suppose you saw a spreadsheet containing the monthly bill of the office (Example: $400). This time, the dollar symbol is denoting the currency.
Now, if you ask, What is the difference between $1 and $A1 in Excel? That would be a different case. Why? Let’s find out.
Excel Cell Reference Explained
Cell reference is a way to refer to a specific cell or a range of cells within a worksheet. There are three types of cell referencing in Excel. Relative cell reference, Absolute cell reference, and Mixed cell reference.
i. Relative Cell Reference:
A relative cell reference is the default type of reference used in Excel. When you create a formula using a relative reference, the cell reference adjusts relative to the location of the formula when copied or filled to other cells.
For example, if you have a formula in cell B2 that refers to cell A1 (i.e., =A1), and you copy that formula to cell C3, the reference will adjust to =B2 automatically, as it moves one column to the right and one row down.
It’s as simple as that. There is no need for a dollar sign here as the reference cell gets adjusted.
ii. Absolute Cell Reference:
An absolute cell reference locks a cell reference to a specific cell, preventing it from adjusting when copied or filled to other cells.
To create an absolute reference, you use the dollar sign ($) before the column letter, row number, or both.
For example, if you have a formula in cell B2 that refers to cell $A$1 (i.e., =$A$1), copying the formula to other cells will always keep the reference as = $A$1, irrespective of the destination cell.
iii. Mixed Cell Reference:
A mixed cell reference locks either the column or row part of the cell reference while allowing the other part to adjust.
To create a mixed reference, you use the dollar sign ($) before either the column letter or the row number in the cell reference. Not both!
For example, if you have a formula in cell B2 that refers to cell $A1 (i.e., =$A1), copying the formula across columns will always keep the column A reference constant but adjust the row number based on the destination cell.
So, now let’s answer the question,
What is the difference between $1 and $A1 in Excel?
The answer lies in the referencing behavior when copied or filled to other cells. $1 denotes absolute row reference. This means that when you copy the formula containing this reference to other cells, the row part of the reference (in this case, row 1) remains fixed, while the column part adjusts based on the new location.
On the contrary, $A1 denotes an Absolute column reference because we are fixing the column for a specific cell whereas the absence of a dollar sign before the row number (1) allows it to be a relative row reference.
How to insert $ (Dollar Sign) Sign in Excel Formula
There are two reasons when you should insert a dollar sign in an Excel formula. One is when you want to lock a specific cell and the other is when you want to display numerical values as currency.
1. Insert $ Sign for Absolute Cell References
The shortcut key to adding a $ sign in Excel is simply the F4 button. For Mac users, press the Fn+F4 button altogether.
If you want to prevent your formula from changing when copied or filled to other cells, you should use a dollar sign for absolute reference.
There are three ways to use the dollar sign for absolute references:
- Absolute Column and Row: Place the $ sign before the column letter and row number (e.g., $A$1).
- Absolute Column: Insert the dollar sign before the column letter but not the row number (e.g., $A1).
- Absolute Row: Place the dollar sign ($) before the row number but not the column letter (e.g., A$1) using the F4 key.
PRO TIP: Press the F4 keys multiple times to switch between absolute, relative, and mixed references.
2. Add $ Sign for Currency Formatting
Normally, when you enter any symbol, Excel considers it as a text format. So, to add $ prefix as a currency format, you need to follow some steps.
- Select the cell or range of cells that you want to format as currency.
- Right-click and choose Format Cells from the context menu.
- Go to the Number Tab from the Dialog box and select the Currency option from the Category List.
- Choose the desired options for the currency format, such as decimal places and currency symbol ($) if not already selected.
- Preview the Sample text.
- Click OK to apply the currency formatting.
FAQ
Question: What does a $2 mean in Excel?
Answer: If the $2 text appears as A$2, it means that the row number is fixed to 2 and the column number is adjustable. Also, if the $2 text is a number, then it indicates a 2 dollar money.
Question: What does ‘!’ Mean in Excel formula?
Answer: The exclamation mark in Excel formula means you are referring to a cell that is present in another worksheet in the same workbook.
Conclusion
So, there is no additional meaning or specific functionality associated with the dollar sign ($) beyond the two primary uses: absolute cell references and currency formatting.
Use the F4 key to lock the columns, rows, or both while referring to a particular cell and use the Formatting Dialog Box to apply the $ sign as a currency.
