Excel $ Formula

Introduction

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.

Excel reference guide

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.

Absolute reference in Excel

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:

  1. 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.

  2. 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:

Screenshot of the Excel $ formula to create absolute references in a spreadsheet

Types of cell references in Excel

Excel absolute reference variations

Absolute references can be structured in a couple of ways:

Mixed cell reference variations

Conclusion

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.