[Fixed!] Relative Cell Reference Not Working in Excel

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.


Download Practice Workbook


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 the cell reference that is relative to another cell 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 all the sales of 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.

Read More: Relative and Absolute Cell Address in the Spreadsheet


2 Possible Reasons and Solutions: Relative Cell Reference Not Working

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 kind of activated setting in 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. And click on OK.
  • Now, if you gain 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 Cell References in Excel Formula (All Possible Ways)


Similar Readings


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: Mixed Cell Reference in Excel (4 Examples)


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


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

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

Leave a reply

ExcelDemy
Logo