Have you ever seen ‘$’ in an excel sheet and wondered why is it so frequently used? To simplify things, ‘$’ means don’t change.
Sometimes, the $ in an excel sheet is just a dollar sign prefixing a currency value. Other times, it is used to lock a cell reference, often referred to as absolute referencing.
The article below covers all details on how and why a dollar sign is used in Excel.
Table of Contents
What Means Dollar Sign in Excel?
A dollar sign in Excel comes in handy to prefix numbers (mostly currency) or to lock a cell for all changes of references.
It can be used as a simple currency symbol or for indicating absolute or mixed cell reference.
What that does is fix the value in a cell. With absolute reference, you lock the address of a cell.
However, with mixed cell reference, you have the option of locking any one of the two, the row or the column reference as per the needs.
A dollar sign is usually only used for indicating references in Excel. To understand what that means, let’s first learn about a cell reference.
What is a Cell Reference?
The address of a cell is referred to as cell reference. It tells Excel the location of the value that is to be put into the formula.
A cell reference is one of the most basic steps of learning Excel. First thing’s first – once you master the difference between references, you can master the rest of Excel in no time.
The formulas used can refer to multiple cells at one time and that makes Excel more powerful as the change of a single row or column can change the entire calculation.
Cell reference comes into use when a formula refers to a cell.
Here’s a quick example.
Whenever you select a cell in Excel, the ‘Name Box’ siding left to the formula bar displays the reference to that cell. In the above example, this turns out to be A2.
Note: The cell reference ‘A2’ is a combination of Column A and Row 2.
Alternatively, select any cell and add the operator ‘=’ to activate the cell for the formula. Click on any cell, and Excel would add the reference for that cell to the formula bar. Take a look below.
Let’s see the types of references in Excel and which one is related to the dollar sign as below.
Types of References
Excel offers three different types of cell references using which you can copy a formula to other cells. These are:
- Relative references: The one that does not use the dollar sign at all.
- Absolute references: The one that uses the dollar sign twice in a formula.
- Mixed references: The one that uses the dollar sign only once.
Relative References
A relative reference is a cell address in Excel that changes with respect to the row and column number.
When you apply a relative reference to a cell and change its position, the reference changes according to the new position of rows and columns and adjusts to the new cell.
In excel, the relative reference is used by default.
Moreover, unlike other references, the dollar sign is not followed by the cell address in relative reference. In this way, the addresses can easily change, and each time you change the cell, you get a new result based on the given data.
Hence, a relative reference is a convenient method in Excel to deal with data when you want the references to be automatically adjusted for position changes.
It lets you perform the same calculation for corresponding cells across the whole worksheet. Let’s see an example below to visualize a better picture of how relative references work.
As seen in the above example, the formula ‘= A1 + 5’ when applied to cell B1, adds up the value of cell A1 and 5. If you now copy the formula to cell B2, the cell reference will change as in the following image.
The formula in cell B2 changes to ‘= A2 + 5’ unlike cell B1 as per the relative reference. Similarly, if we copy the formula to cell C1, the formula changes as ‘= B1 + 5’.
This illustrates how the relative reference changes for each cell when the same formula is copied at different places.
Also, for column B, simply try using the drag and drop function and see how Excel updates the cell references for every single cell in a snap.
Absolute References
An absolute reference is the cell address that uses two ‘$’ signs in one formula. One lets you lock the row and the other, the column.
When this reference is used, the cell reference remains the same no matter which cell of the worksheet it is copied to.
To use absolute reference for a cell, it needs to be applied manually to the cell in question.
Unlike relative reference, both the row and column names are preceded by dollar signs.
This is mostly used when you have large sets of data, and you must apply the same formula to all cells in a worksheet. So even if you change the position of the formulas, you will still have the absolute cell references unchanged.
Let’s see an example below to understand the concept better.
As visible, the absolute reference formula applied to B1 remains the same and will give the same result no matter which cell you copy it to.
The formula in cell C3 remains the same as in B1 because of the absolute reference.
There are only a handful of instances where you use absolute references independently in a worksheet. This is because most of the time, absolute and relative references are both used together.
Pro Tip: If you do want to change the absolute reference, add a row or column. Although absolute reference locks the cells and does not move from the origin, on the addition of a new row or column, it leaves the previous location and adjusts with the new row or column.
In the above image, the formula in cell B1 is =$A$1*10.
Upon adding a new column, the values shifted to the next column, and the cell reference in cell C1 became =$B$1*10.
Mixed References
A mixed cell reference is super simple and easy to use.
Unlike absolute reference, mixed reference locks only one coordinate, i.e., it uses only one dollar sign – either before the column name as $A1 or before the row number as A$1.
In $A1, the column name is locked and will not change but the row number can be adjusted. Similarly, in A$1, the column name can be adjusted but the row number won’t change as it is fixed.
Let’s see the image below to grasp the concept better.
=$A2*$B2+C$2
As seen in the image above, cell C3 contains mixed cell references. In the first two cell references, the column name is locked and in the third cell reference, the row is locked.
When you put $A2 in the formula, Excel will change the row number orderly, but the column name will remain the same.
Similarly, in $B2, the column remains the same, but the rows change for each cell where the formula is copied.
In C$2, the row will remain the same, but the three instances in three different columns will be used sequentially.
Shortcut to Add Dollar Sign
Adding or removing a dollar sign is easy. You can either do it manually or use a shortcut key to perform the task.
If you want to manually add a dollar sign, simply double-click on the cell to activate it (or press the F2 key). Place the cursor where you want to add the dollar sign and hit the dollar key.
Usually, when the shortcut is used, it adds or removes the dollar sign for references. The shortcut key used is F4. Single-click on the cell which contains the formula and press the F4 key. The key will let you toggle among the following four instances:
- Upon pressing the F4 key once, a dollar sign is added to the cell reference.
- Upon pressing it the second time, another dollar sign is added.
- Upon pressing it the third time, a dollar sign is removed.
- Upon pressing it the fourth time, another dollar sign is removed.
In this way, you can activate relative, absolute, or mixed references as needed. Or, you can remove dollar sign to turn your cell reference from absolute to mixed to relative reference.
Pro Tip: Make sure to activate the cell, or else, upon pressing F4, Excel will automatically select the cell to the left of the cursor and change its reference type.
Conclusion
A dollar sign has much more uses in Excel than being prefixed to a number for representing currency. You may use it to turn cell references into absolute cell references or mixed references with sheer ease.
Practice a few examples stipulated above to master using the $ sign in Excel in no time. Happy Excel Exploring!