Ever get stuck with weird errors in your Excel sheet? Circular references can be a real headache. They happen when a formula refers back to itself, either directly or indirectly, causing Excel to get into an endless calculation loop.
For example, if cell B2 has a formula that relies on the value in B2, you’ve got a circular reference. Excel keeps recalculating without ever reaching a final value.
Most of the time, these are accidental mistakes but sometimes they’re intentional for advanced iterative calculations. Regardless, they can mess up your spreadsheet results and slow things down. Let’s see how to find and deal with them.
Spotting and Handling Circular References
Circular reference errors occur when a formula refers back to the cell containing it. This leads to those endless loops, which Excel is usually pretty good at detecting and flagging. Knowing how to pinpoint and fix these errors is key to keeping your data accurate.
Imagine you’ve got numbers in cells A1 through A4, and you decide to use the SUM function in cell A5 with the formula =SUM(A1:A5). Here, A5 is included in the very sum that’s in A5 itself. That’s a direct circular reference, and Excel will immediately tell you about it.
After seeing the warning, you can hit ‘Help’ for more details or ‘OK’ to just close it. The formula usually ends up showing zero, because Excel can’t figure out the loop. These circular references can cause incorrect results and sluggish performance, so it’s best to fix them fast.
Types of Circular References
Circular references can be either direct or indirect.
Direct Circular References
A direct circular reference is when a formula refers directly to itself. If you put the formula =A2+1 into cell A2, the formula depends directly on the cell it lives in. Excel will throw a circular reference warning.
If you click ‘OK’ on that warning, the cell will just show zero because Excel can’t break the circular loop.
Indirect Circular References
An indirect circular reference is a bit trickier. It’s when a formula refers to another cell that eventually refers back to the original cell. It’s a chain of dependencies that isn’t immediately obvious.
Let’s say cell A1 has the number 20.
Cell C3 has the formula =A1.
Cell A5 uses cell C3 with the formula =C3+5.
Now, if you go back and change cell A1 to a formula that refers to A5, like =A5*2, boom! You’ve got a circular reference. A1 relies on A5, which relies on C3, which loops back to A1.
Clicking ‘OK’ will make the cell show zero. Excel might show tracer arrows to help you visualize this circular path.
Turning Iterative Calculations On or Off
Excel usually keeps iterative calculations off by default. This means when it spots a circular reference, it warns you and gives you a zero. But, in certain cases, you might need to turn them on for those intentional circular reference formulas that do iterative calculations.
To enable iterative calculations:
-
In Excel 2010 and later, head over to the ‘File’ tab and click ‘Options’.
-
In the ‘Excel Options’ window, select ‘Formulas’ on the sidebar. Then, under ‘Calculation options’, check the ‘Enable iterative calculation’ box and click ‘OK’.
For older versions of Excel:
- In Excel 2007, click the Office button, select ‘Excel Options’, then ‘Formulas’, and check ‘Enable iterative calculation’.
- In Excel 2003 and earlier, go to the ‘Tools’ menu, select ‘Options’, and then the ‘Calculation’ tab to enable iterative calculations.
Tweaking Iterative Calculation Settings
With iterative calculations enabled, you can set the ‘Maximum Iterations’ and ‘Maximum Change’ values to control how Excel handles these calculations.
- Maximum Iterations: This is the number of times Excel recalculates. A higher number allows more attempts to reach a solution but can make your sheet slower.
- Maximum Change: This is the smallest change between calculation results. Smaller numbers are more accurate but might need more iterations.
Keep in mind, turning on iterative calculations stops Excel from warning you about circular references, so only turn this on when you need it.
Finding Circular References
Pinpointing a circular reference in a huge spreadsheet can be like finding a needle in a haystack. Luckily, Excel has tools to help.
Using the Error Checking Tool
- Go to the sheet where the circular reference is. Click the ‘Formulas’ tab, find the small arrow next to ‘Error Checking’, and hover over ‘Circular References’. A list of all the cells with circular references will pop up.
- Click on a cell from the list to jump straight to it and fix the problem.
Using the Status Bar
The status bar at the bottom of Excel will also tell you about circular references. It will show “Circular References” followed by the cell address, like “Circular References: B6”.
Some things to keep in mind:
- The status bar won’t show circular references if iterative calculations are enabled.
- If the circular reference is on a different sheet, the status bar will just show “Circular References” without a cell address.
- The warning only pops up once per session, unless you fix the circular reference or turn off iterative calculations.
Fixing Circular References
To get rid of a circular reference, you need to change the formulas that are causing the loop. This means tweaking cell references or formula ranges so they’re not dependent on themselves.
For instance, if cell B6 has a formula like =SUM(B1:B6)*A5, and it’s causing a circular reference, change it to exclude B6: =SUM(B1:B5)*A5.
This tweak removes the loop, and B6 will show the correct result.
With more complex formulas, you can use “Trace Precedents” and “Trace Dependents” to visualize the cell relationships and pinpoint circular references.
You can find these tools under the ‘Formulas’ tab in the ‘Formula Auditing’ group.
Trace Precedents
This tool shows you all the cells a formula depends on. Select the problematic cell and click “Trace Precedents”. Blue arrows will point to all the cells directly feeding into the formula.
In the example below, the arrows show that cell B6 depends on cells B1 through B6 and A5. Because B6 is part of its own calculation, it’s a circular reference.
Adjusting the formula to exclude B6 will break the loop.
Trace Dependents
“Trace Dependents” shows all the cells that rely on the selected cell. Click on a cell and then “Trace Dependents” and arrows will show you which formulas use that cell’s value.
In the example, cell D3 uses the value in B4. The arrow shows that changes in B4 affect D3.
Using Circular References on Purpose
While usually something to avoid, circular references can be used intentionally for iterative calculations. If this is the case, you’ll need to enable iterative calculations.
For instance, imagine calculating a commission that is a percentage of the total price, where the total price includes the commission.
Make sure iterative calculations are enabled in Excel first.
Let’s say you’re calculating the total cost of a house, which includes a 2% agent commission based on the total cost. This creates a circular reference because the total cost affects the commission, which affects the total cost.
In cell B6, calculate the total cost:
=SUM(B1:B5)
In cell B4, calculate the agent’s fee as 2% of the total cost:
=B6*2%
This creates a circular reference between B4 and B6.
With iterative calculations on, Excel does repeated calculations until the results are close enough, giving you the right total cost with commission included.
If iterative calculations aren’t enabled, Excel will warn you and might not calculate correctly.
Knowing how to manage circular references in Excel is essential for making sure your calculations are accurate. By learning to spot and fix them, you can keep your data clean and use Excel like a pro.