[Fixed!] Relative Cell Reference Not Working in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

What is Relative Cell Reference in Excel?

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

What is Relative Cell Reference in Excel?

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

What is Relative Cell Reference in Excel?

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:

excel Relative Cell Reference Not Working

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.

excel Relative Cell Reference Not Working

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

excel Relative Cell Reference Not Working

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

excel Relative Cell Reference Not Working

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.

excel Relative Cell Reference Not Working

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 ReferenceCell Reference in ExcelExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

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

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo