Download Excel Workbook
Watch our free training video on How to Creating Absolute and Relative Cell References in Excel:
You might have been a little bit confused to come across an Excel formula with the $ sign as part of the cell references. Worry not! The $ dollar sign in Excel serves a simple but useful purpose in spreadsheets. In this article, we will explore why and how the dollar sign $ is used in Excel formulas, specifically in the context of absolute and relative cell references.
Download the Excel Workbook below to follow along and understand how to use the $ Dollar Sign in Excel: Absolute, Relative, and Mixed Cell References – download excel workbookDollarSigninExcel.xlsx
Relative Cell References (Without the $ Sign)
In Excel, when you create a formula without using the dollar sign, the cell references are considered relative. This means that when you copy the formula to another cell, the references adjust based on the new location.
Let’s say you have a formula in cell C2 that adds cell A1 and cell B2.
Copy this formula to cell C3, it will automatically adjust the answer according to cell A3 and cell B3. This is called a relative cell reference.
But what if you want to copy the formula, but don’t want the cell reference to change?
This is where the absolute cell references come into play.
Click here to learn How to Master Excel Formulas – The Ultimate Guide!
Absolute Cell References
Absolute cell references are when you want Excel to always look at a specific cell, regardless of where the formula goes. By placing a dollar sign ($) in front of the column and/or row reference, you can “lock” that specific part of the reference.
For example, here we have a formula in cell C2 that references cell $E$2 as the constant.
If you copy this formula to cell C3, it will still refer to cell $E$2, even though it’s in a different location. This is particularly useful when working with constants, such as product prices or tax rates, that should remain the same across different calculations.
Note: While we can hard-code the value directly, it would take a longer time to change each and every formula if you need to change the price. The absolute reference gives us an advantage by reflecting the changes to all the formulas just by updating one cell.
Using the F4 Shortcut
There are two methods for inserting the $ sign into a cell reference in Excel:
Manual Entry:
- Enter the edit mode of a cell by double-clicking on it or using F2.
- Position the cursor where you want the $ sign and type it manually.
Using the Keyboard Shortcut (F4):
- Place the cursor on the cell reference where you want to add the dollar sign.
- Press F4 once to transform the reference by adding or removing the $ sign, depending on the original reference.
For instance, if you have the reference E2 in a cell, here’s how the F4 shortcut would operate:
Press F4 once: E2 changes to $E$2
The formula will refer to column B and row 2.
Press F4 twice: E2 changes to E$2
Row 2 is fixed, but the column can change as the formula is copied.
Click here to learn How to Create A Yearly Leave Record for Employees in Excel!
Press F4 three times: E2 changes to $E2
Column B is fixed, but the row can change as the formula is copied.
Press F4 four times: E2 reverts to E2
This keyboard shortcut offers a quick and efficient way to toggle between different reference types without the need for manual typing.
Mixed Cell References
In addition to absolute and relative references, Excel allows for mixed references, where either the column or the row is absolute, but not both.
For example, we want to multiply each number at the top by each number at the side.
Let’s enter the formula in cell B2 as =A2*B1. This is essentially saying that we are multiplying the value in the left-most column by the value in the upper-most row.
So it is always going to be column A, and it is always going to be row 1. =$A2*B$1
If you copy this formula to the other cells, the column reference will adjust (B,C,D, and E), but the column reference will remain fixed at column A, as well as row 1. This flexibility gives users even more control over how references behave in different situations.
Click here to learn How to Separate Date and Time in Excel!
Conclusion:
There you have it! The use of the dollar sign as a reference is guaranteed to increase your efficiency and accuracy in Excel.
Click here to check out Microsoft’s tutorial on how to Switch between relative, absolute, and mixed references!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
