Excel is a powerful software program that allows users to input, organize, and analyze data. It is often used for financial purposes but can also be used for a variety of other applications as well. The dollar sign is one of the many symbols used in Excel to indicate a certain value.
In this article, we will learn everything about the dollar sign and its function in Excel.
What Is the Dollar Sign in Excel and What Function Does It Have?
The dollar sign ($) is a symbol in Excel which purpose is to reference a particular cell that will not change in the future. You may also use it to refer to an entire row or column rather than just one cell.
The dollar sign as a symbol represents a specific cell, which can be absolute, relative, or mixed.
Shortcut to Add a Dollar Sign to Cell References
You can quickly add a dollar sign ($) to cell references in Excel by using the F4 key.
1. Click on the cell where you need to set the reference.
2. Go to the Formula bar and add the = sign.
3. Simply select the cell or range of cells you want to reference.
2. Press the F4 key.
The dollar sign will be added to the selected cell. You can press F4 multiple times to cycle through the different reference options:
- Absolute ($): $A$1.
- Absolute row and relative column (mixed): A$1.
- Relative row and absolute column (mixed): $A1.
- Relative: A1.
You can also add the dollar sign to cell references by using the keyboard shortcut
Ctrl + Shift + F4. This will cycle through the reference options in the opposite order: relative, mixed relative row and absolute column, mixed absolute row and relative column, and absolute.
If you want to quickly remove the dollar sign ($) from cell references, you can use the same keyboard shortcuts. Simply press F4 or Ctrl + Shift + F4 until the desired reference option is selected.
How Does It Work?
Let’s take a look at relative, absolute, and mixed references in detail, whether for an entire row or column, and how it all works!
Relative Cell Reference
The relative cell reference is indicated by the column letter and row number of the cell without the $ sign.
A relative reference changes when it is copied to a new location. For example, if cell B1 contains the formula =A1*20 and you copy that formula down to cell B2, the formula will change to =A2*20. The relative references in the formula adjust automatically based on their new location. In contrast, an absolute reference does not change when it is copied to a new location.
Absolute Cell Reference
The dollar sign ($) is used to designate absolute cell references, and both the row number and column letter of the cell have the dollar sign. If you want to refer to cell A1 and cell A1 only, with no changes, you would type $A$1.
When copied to a new location, an absolute reference remains unchanged. For example, if cell B1 has the formula =$A$1*20 and you copy that formula down to cell B2, the formula will still refer to cell A1, remaining as =$A$1*20.
Mixed Cell References in Excel
A mixed reference is one that includes both an absolute and a relative reference. For example, if you want to refer to cell A1, but have the column be absolute while the row remains relative, you would write $A1. This locks the column at A but allows the row to change as needed.
Mixed references are useful when you want part of a reference to stay the same but the other part to change. In the example below, the column will always be A, but the row will change depending on where the formula is copied.
How to Lock the Reference for the Entire Row or Column
You can lock the reference for an entire row by adding a dollar sign ($) before the row number. For example, if you want to lock the reference for row 1, you would write $1.
To lock a column’s reference, add a dollar sign ($) before the column letter. If you want to lock things to column A, for example, you would write $A.
When you want to reference a range for both a single row and a single column, use a dollar sign before both the row number and the column letter. For example, if you wanted to keep the reference for cell A1, you would write $A$1.
The dollar sign is a versatile tool in Excel that can be used for absolute, relative, and mixed cell references. You can quickly add or remove the dollar sign from cells by using the F4 key, or by using the Ctrl + Shift + F4 keyboard shortcut. Use the dollar sign to lock the reference for an entire row or column by adding it before the row or column number.
