What is the Function of Dollar Sign ($) in Excel Formulas?Explained & Defined with Examples
Have you ever seen a dollar sign ($) in the Excel cell reference formula before?
If yes, and it is your curiosity that made you search for its function, this article is for you!
What is the Meaning of $ Sign in Excel Formulas?
One reason why Excel is a dynamic tool is the feature to link cells or selections and use them in formulas.
Accordingly, when these formulas are copied, the reference of the cell will automatically change based on its corresponding source.
See the illustration below where I will duplicate the formulated cell and put it in C3.
Instead of having the same formula as Cell C2 which was linked to A2 and B2, Cell C3’s formula shifted to A3 and B3.
This is known as a relative reference where the cell references automatically change relative to the cell to which it has been applied.
Conversely, it is not all the time that we need the automatic changing of cell reference features.
Instead, what we might actually need is the other way around. We sometimes need the cell reference to stay as it is. Here we introduce the $ Sign Function.
The $ Function
We use the dollar ($) sign to instruct Excel that when we duplicate the cell with a formula, we want to retain linking to the specific cell.
For example, we type $C$2. This means you want Excel to keep linking to cell C2.
There are three cases of references in Excel. In connotation, we can use the dollar ($) sign in three different approaches.
Adding Dollar Sign to Cell References (Shortcut)
Adding the dollar ($) sign can be done in two different methods: Manual & Hotkey F4.
Manual
You just have to double-click the cell where you want to add the dollar sign or just press the hotkey F2 to put the cursor to the particular place where you like to add the dollar sign and then do the typing.
Using the Hotkey F4
Click the cell where you want to add the dollar sign and click the keyboard key F4 once.
Depending on what’s the initial reference, Excel will then automatically edit the reference by adding/deleting the dollar ($) sign.
To see how this shortcut key works, let’s take the reference C2 in a cell as our example.
- You can turn C2 to $C$2 by clicking F4 once.
- You can turn C2 to C$2 by clicking F4 twice.
- You can turn C2 to $C2 by clicking F4 thrice.
- You return C2 to C2 by clicking F4 four times.
Cell References in Excel (3 Types)
Excel has three types of cell references that differ depending on how often you use the dollar ($) symbol.
These are Relative, Absolute, and Mixed Cell References.
- Not using a dollar sign in the cell references by any chance is called Relative Cell Reference.
- Using the dollar sign once is called a Mixed Reference. An example of this is $C3 or C$3.
- Using the dollar sign twice is called an Absolute Reference. An example of this is $C$3.
To understand better, let’s explain them one by one.
Relative Reference
A cell address without the dollar ($) sign on its coordinates is called a Relative Reference.
Consequently, when a formulated cell containing relative references is copied into another cell, the reference will automatically adjust depending on the relative position of rows and columns.
See the same illustration below where the references or cell’s address change when we duplicate the cell containing the formula.
Absolute Reference
A cell reference is an Absolute Reference when the dollar ($) sign is placed before the row and column coordinates.
When a cell containing an absolute reference is duplicated, the row and/or column will remain constant.
When we are working on time durations and annual percentage rates, we usually need some values to remain constant. This is when Absolute References are suitable to use.
See the illustration below showing how a value (in this example cell D2) will not change when formulas are duplicated.
To guarantee that it will remain constant when we duplicate the formulated cell, we use $D$2.
Take note that the illustration above both has an absolute reference (used the dollar sign twice) and the relative reference (no dollar sign).
Note: You might ask why to use the absolute reference instead of just applying the usual hard code.
Well, using the absolute reference might save you a lot of time in the future when you have to change the formula values.
Instantly, change the value in just a single cell (for example, C3) and all the other relative cells will be updated, at the same time.
Mixed reference
Among the three reference types, Mixed Reference is the most complex.
Here in Mixed Reference, we only use the dollar sign once.
One part of the cell reference is relative while the other is constant, and it could either be the column or the row coordinate (for instance – $C3 or C$3).
Adding a dollar sign-in before the column coordinate (C for this instance) will lock the column only.
This implies that duplicating the formulated cell containing $C3 will allow the row to change while keeping the column constant.
Conversely, when we add a dollar sign before the row coordinate, (3 for this instance) it will lock the column only.
This also implies that duplicating the formulated cell containing C$3 will allow the column to change while keeping the row constant.
Summary
The coordinates that go before the dollar sign, either the row or column coordinates, will be locked or become constant.
Here is a short summary of the meaning of the dollar ($) sign in Excel Cell References:
- $A$1:$A$100 – denotes the range selection A1:A100
- $A$1 – denotes column A and row 1
- $A1 – When the formulated cell is duplicated, Column A is constant while the row is changeable.
- A$1 – When the formulated cell is duplicated, Row 1 is constant while the column is changeable.
We hope you found this article useful in learning the meaning and function of dollar ($) signs in Excel Formulas.