Excel formulas are powerful tools that can help you accomplish complex tasks in your spreadsheets. In order to harness the full power of formulas, it's important to understand how each component of a formula works, This article will focus on the $ symbol and explain how it's used to indicate absolute cell references in Excel formulas. Keep in mind that the keyboard shortcuts mentioned in this article are for Microsoft Office on a Windows machine; some of these shortcuts may be different for macOS users.
Let's begin our discussion by explaining what $ actually means in an Excel formula. If you duplicate cells that reference the value of another cell, Excel's default behavior is to change the relative reference position. The dollar sign overrides this default behavior, telling Excel to not alter the reference position. When you use the $ symbol in your cell formula, and you duplicate or copy that cell, the reference position for the original cell will be duplicated as well.
In short, the dollar sign, when used in Excel formulas, signifies an absolute reference. You can insert it into a cell's formula by pressing F4 on a Windows machine or command+T on a Mac.
If you need to add $ to a cell reference, there are two ways that you can insert it into a cell:
You can edit the cell manually. Just go into edit mode by double-clicking the cell or use F2 to place the cursor to where you want the $ to be placed and type it there.
Use the keyboard shortcut: F4 or command+T.
Here's another simple way to add $ when editing a formula in Excel. Place the cursor on the cell reference where you wish to add the $ sign and press F4 once. This method will change the reference by either adding or removing the $ sign, depending on how many times you press F4. Let's look at some examples that show how F4 would work if B1 was the reference cell:
Relative reference: This is the default reference for cells when no $ reference symbol is used in the formula. Copying a cell that has a relative reference will adjust the reference based on the cell where it's pasted.
Absolute reference: This type of reference occurs when you add the $ sign before the column and row. In this case, the reference will not be changed when the formula is copied and pasted to another cell. Absolute references are useful for values that need to be constant like interest rates or periods of time.
Absolute references can be structured in a couple of ways:
Adding a $ before the column and before the row will always point to that specific cell; in other words, a cell with the formula:$C$1 will always set its value to cell C1.
Adding $ to a range of columns and rows will always point to that range; for example, $C$1: $C$10 will always refer to the range of C1:C10.
Mixed Reference: This type of reference occurs when you will only add one dollar sign. Adding a $ in a column will lock only the column-- this means that if you copy and paste the formula, no changes will be made to the column but changes will occur in the row. The opposite action will occur if you only add $ to the row- the row will be changed but the column will.
Adding $ before the column indicates that only the column will be "locked"; for example, $C1 would constantly point to column C but the row would change when the formula is being copied.
Adding $ before the row means that only the row will be "locked"; for example, C$1 will always point to row 1 but the column would change when the formula is being copied.
Adding $ to the first row of a range will create an expanding range-- i.e., if you copy the formula C$1:C1, the range will change to C$1:C2, then C$1:C3, C$1:C4 and so on.
The dollar sign plays a key role in Excel formulas, changing relative cell references to absolute references and ensuring that these cell references remain unchanged when they're copied and pasted. Knowing how to use $ in Excel formulas will allow you to utilize more complex formulas and create better reports. With our examples to guide you, you'll have no trouble implementing absolute references in your own Excel worksheets.
