**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 excelworkbookDollarSigninExcel.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!

If you like this Excel tip, please share it

XFacebookLinkedInCopyEmailPrintReddit

##### John Michaloudis

Founder & Chief Inspirational Officerat MyExcelOnline.com

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 MyExcelOnline Academy Online Course.