How to Fix the Excel Circular Reference Error?
(Note: This guide on how to fix the Excel circular reference error is suitable for all Excel versions including Office 365.)
As an Excel user, you might have come across the ‘circular reference’ error occasionally. This appears when a formula directly or indirectly refers back to its own cell.
Although a circular reference should be generally avoided, it can be used to carry out certain iterative calculations. That is why Excel actually has a feature to enable circular references in Excel.
Nevertheless, many users find themselves facing the Excel circular reference error after inadvertently using them in their formulas.
That is why in this guide, I’ll teach you everything about the Excel circular reference and clear the air of mystery surrounding it.
Related:
How to Extract an Excel Substring? – 6 Best Methods
How to Superscript in Excel? (9 Best Methods)
How To Find Duplicates in Excel? (3 Easy Methods)
What is an Excel Circular Reference?
A circular reference, as the name suggests happens when any formula directly or indirectly refers back to its own results. This creates an infinite loop and has the potential to slow down your spreadsheet if not used properly.
Take the case of the following example. Here, the formula in cell B3 refers to its own cell, causing the circular reference error.
Usually, this is an accidental error caused by a typo or negligence. However, there are some special occasions where this can come in handy. We’ll look at one such example in the next section.
How to Enable Circular Reference in Excel?
For some calculations, you need to enable circular reference to perform iterative calculations.
To enable a circular reference, File -> Options -> Formulas -> Enable iterative calculations -> Click OK.
Before hitting OK, set the Maximum Iterations to 100 or some other number that specifies the number of times a formula will be calculated. Please note that, if you specify a higher value, the calculations will take more time to complete.
Also, set a small decimal value to Maximum Change so that Excel stops the iterations if it sees very minor changes between the previous and current calculations.
By default, Maximum Iterations is set as 100 and Maximum Change is set as 0.001 so that Excel will stop the calculation at the 100th iteration or stop when the results are almost the same in each iteration.
Once you enable this, you can start using circular references in Excel. A word of caution: Try to avoid them as much as possible and use them only if absolutely necessary.
In the following example, we are trying to assign the current date and time to any delivered product if not already done. Here, the formula =IF(B4=”Delivered”,IF(C4=””,NOW(),C4),””) checks if the cell C4 is blank, only for delivered products. If that is the case, it assigns the current time and date using the NOW() function.
As you can see, it is a clear case of a circular reference, since the formula in cell C4 refers to itself as part of the calculations.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
Types of Circular Reference
Direct Circular Reference
A direct circular reference occurs when you directly refer to the cell where the formula itself is present. In the example below, you can see that A5 is included in the formula and the formula is also entered at cell A5.
This is an example of a direct circular reference. Once you execute it you will see a warning message as seen below.
If you click OK, the formula will return a “zero” as shown in the example below. Sometimes Excel will not return an error and just return a zero instead of displaying an error. Also please note that, if you keep repeatedly using circular references, Excel may not warn you each and every time.
Indirect Circular Reference
An indirect circular reference occurs when a formula indirectly refers to the same cell as part of the execution of the formula.
In the above example, we consecutively square the numbers in the diagonal. Cell A1 has a value of 10 and the other cells, B2, C3 and D4 are all consecutive squares of A1.
Now, instead of ’10’ if you use the cell reference D4 in cell A1, it will rise a circular reference warning. This is an indirect cell reference since the formula traces back its origin to itself. To trace back to its origin, click on Trace Precedents that is on the Formulas ribbon.
How to Find Circular References in Excel?
A circular reference error may not be explicitly obvious all the time. You may need to manually search and find the source of these errors. To find circular references in Excel, go to Formulas -> Error Checking -> Circular References
This will list all cases of circular reference errors. Resolve them one by one.
Trace Precedents
Sometimes, you may end up with a huge database and may not have any idea about which cell is causing the circular reference error. To help you visualize the relationship between cells, use the Trace Precedents feature.
The trace precedents feature tracks the cells on which the current cell depends using blue coloured tracer arrows. These are the cells on which the current formula is dependent and affects its value.
To use it, just select the cell whose precedents you need to track and click on the Trace Precedents option in the Formulas tab. In this example, the blue lines traceback cell A1 to the cells B2, C3 and D4. This indicates that cells B2, C3 and D4 are precedents of cell A1 and directly affect its value.
Trace Dependents
The adjacent Trace Dependant option is the inverse of the trace precedent feature. It helps you track the cells that are dependent on the current cell for its value. In the below example, the tracer arrow indicates that cell D5 is dependent on cell D3.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this article, we saw how to handle the Excel circular reference error. Please keep in mind that, though circular reference is allowed in certain situations, it is advisable to avoid them altogether.
I recommend you test these concepts in a sample spreadsheet for better understanding.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
