In this article, we will discuss the possible reasons and solutions for relative cell references not working in Excel.

## What is Relative Cell Reference in Excel?

A relative cell reference is a cell reference that is relative to another cell’s position in Excel. It automatically changes when using the **Fill Handle** icon to copy a formula across a column or row in Excel.

To illustrate, consider the following dataset of some sales:

Suppose we want to sum the total sales of January and February of each sales person using the **SUM function**. For the first salesperson, we use the relative cell reference **C5:D5 **in our formula.

Drag the **Fill Handle** icon over the range of cells **C6:C10**.

Excel will automatically fill in all the sales for January and February for each person.

Let’s look at the cell references in any other cell of our results.

The cell reference in **the SUM function** in cell **E10** is **C10:D10**, whereas the formula we copied used the cell reference **C5:D5**. Because the formula contained a relative cell reference, it adjusted and changed the cell references in the cells it was copied into accordingly.

## Relative Cell Reference Not Working: 2 Possible Reasons and Solutions:

Consider the screenshot below.

Here, we tried the same process as above, but the cell references didn’t change as expected when **Autofilled** down to the cells below. The relative cell reference is thus not working.

Here are two possible reasons and solutions.

**Reason 1 – Calculation Option is Set to Manual**

Manual Calculation Mode is the main reason for relative cell references not working in formulas. Let’s solve the issue.

**For Windows Users**

**Steps:**

- Click on the
**File**tab. - Click on
**Options**.

- In the
**Excel Options**dialog box that opens, click on**Formulas**.

Here’s the problem: the **Manual** option is selected under** Workbook Calculation**, meaning all formulas need to be calculated manually.

- Select the
**Automatic**option for**Workbook Calculation**instead and click on**OK**.

Now, if you type the formula and drag the **Fill Handle** icon, the expected output will be returned.

**For Mac Users**

**Steps:**

- Go to the menu system and click on
**Excel**. - Click on
**Preferences > Calculation**. - Select the option
**Automatically**.

**Reason 2 – Incorrect “Mixed” Cell References**

The next most likely reason to be causing this problem is the use, or misuse, of mixed cell references in your worksheet.

Unlike relative cell references, absolute cell references (which contain a dollar (**$**) sign before the cell reference, for example **$C$5**) fix cell references so they do not change when the formula is copied. Mixed cell references combine both relative and absolute cell references by fixing either the row or column reference (for example **C$5** or **C$5**).

Consider the following screenshot:

We have used mixed cell references here. In the formula, we used the dollar sign before the row reference to fix row 5 and its result. Simply remove the dollar signs from the formulas to make the cell references relative again, and the formula will copy as expected.

**Read More: **Relative Cell Reference Example in Excel

**Download Practice Workbook**

**<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel**

THANK YOU!!!! I could NOT figure out what was wrong with my file.

Hi, ETHAN! Hope, the solutions have worked for you!

I have a conditional formatting issue I can not resolve. In the Conditional Formatting Rules Manager, in the “Applies To” section, I can change the cell formula from absolute to relative, but when I click on Apply, it changes it back to absolute. Is there a solution?

Hello Roger,

Can you please share your Excel dataset with us? We will try our best to solve your problem. Because in our Excel 365, the conditional formatting works perfectly for absolute and relative cell references in both cases.