In Microsoft Excel, using cell references in the formulas is the most common thing that you will do. It is a day-to-day operation for every Excel user. We use absolute and relative cell references in a lot of Excel formulas. Sometimes, you may be in a situation where these cell references won’t work. In this article, I will discuss the possible reasons and solutions for relative cell reference not working in Excel.

This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.

## What is Relative Cell Reference in Excel?

Before starting this, I think you should revisit your knowledge of relative cell references in Excel. Now, what is a 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 adjusts and changes when you use and copy a formula using the **Fill Handle** icon across any column or row in Excel.

Have a look at the dataset:

Here, you can see, we have a sales dataset. We want to sum up the total sales of January and February of each salesperson. So, to do that we used the **SUM function**. Here, for the first salesperson, we used the cell references **C5:D5**.

Now, drag the **Fill Handle** icon over the range of cells **C6:C10**.

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

Now, let’s look at the cell references in any other cell of our results.

Here, in this screenshot, you can see the cell reference in **the SUM function** is **C10:D10. **But, previously we used the cell reference **C5:D5. **As you can see, Excel is clever enough to understand the operation that you are performing. For this reason, it adjusted and changed the cell references in the formulas on the Total Sales column.

Relative cell references save a lot of time while copying a complex operation to other cells. You can also insert a formula with a relative reference through VBA.

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

Take a look at the screenshot:

Here, we tried the same process again as the previous one. But, you can see there is no update in the result. So, in this case, our relative cell reference is not working in the Excel worksheet.

In the following sections, I will discuss two possible reasons and solutions for the relative cell reference not working in Excel.

**Reason 1: Your Calculation Option Is “Manual”**

Now, it is the main reason that is causing this problem of not working. In Windows, sometimes you have some activated settings while calculating the formulas.

**For Windows User**

Let’s solve this problem. Follow these simple steps:

📌 **Steps**

- First, on the
**File**tab. - Next, click on
**Option**.

- Now, from the
**Excel Options**dialog box, click on**Formulas**.

- From this screenshot, you can clearly see the main reason why the relative cell reference is not working in Excel. Here, the “
**Manual**” option is selected. It indicates that you have to calculate all the formulas manually. Excel won’t calculate them with relative cell references. So, we have to change it.

- Now, select the “
**Automatic**” option from the Workbook Calculation. Click on**OK**. - Now, if you type the formula and drag the Fill Handle icon, you will get the desired output.

**For Mac Users**

If you are a Mac user, you have to follow these simple steps:

📌** Steps**

- First, go to the menu system and click on
**Excel**. - After that, click on
**Preferences > Calculation**. - Now, select the option “
**Automatically**”.

These steps will solve your problem if you are a MacBook user. As you can see, we have fixed the problem of relative cell reference not working in Excel.

**Read More: **How to Use Relative Cell Reference in Excel

**Reason 2: You Are Using Wrong “Mixed” Cell References**

Another reason that is causing this problem is that you are using mixed cell references. And most probably you are using the wrong mixed cell reference in your worksheet.

By using the mixed cell references, we fixed rows or columns while calculating the formulas. We use the dollar sign($) to do this.

Take a look at the screenshot:

Here, you can see we have used the mixed cell references here. In the formula, we used the dollar sign before the rows in the formulas. Now, what does that mean? It means even if we copy the formula to the end, it will fix row 5 and its result.

In relative cell references, Excel automatically updates the rows and columns. But here, as we fixed the rows, Excel won’t update the result.

So, remove the dollar signs from the formulas to make the relative cell references work in your worksheet.

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

## 💬 Things to Remember

✎ Make sure to change the setting from “**Manual**” to “**Automatic**” in Excel Options.

✎ Make sure your Excel formulas don’t have absolute or mixed cell references.

**Download Practice Workbook**

## Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to fix the problem of relative cell reference not working in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

**<< Go Back to Relative Cell Reference | 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.