[Fixed!] Relative Cell Reference Not Working in Excel

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