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.
After that, Excel will automatically fill all the sales of 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.
2 Possible Reasons and Solutions: Relative Cell Reference Not Working
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 kind of activated setting in while calculating the formulas.
For Windows User
Let’s solve this problem. Follow these simple 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. And click on OK.
- Now, if you gain 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:
- 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.
- Excel VBA: R1C1 Formula with Variable (3 Examples)
- How to Reference Text in Another Cell in Excel (14 Ways)
- Excel VBA Examples with Cell Reference by Row and Column Number
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- Excel VBA: Get Cell Value from Another Workbook without Opening
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.
💬 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.
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!