We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Getting a Circular Reference error in Excel can be intimidating. But it doesn’t have to be. Let’s take a look at what Circular Reference errors are and how you can fix them.
Circular Reference in Excel
WATCH: Circular Reference Error in Excel – Tutorial
What does Circular Reference mean in Excel???
A Circular Reference is a type of error in Excel that happens when a formula in a cell tries to calculate itself.
Essentially the calculation keeps going in circles and can’t figure itself out.
This sounds weird, I know.
A common way that this happens is by dragging your cursor a bit too far when selecting a range of cells for a calculation.
If you accidentally include the cell where your formula result should be in your selection, you’ll create a circular reference.
For example, the selection in this image will result in a circular reference. This is because the SUM function in cell E2 cannot include cell E2 in its calculation.
Circular Reference Warning Alert
Excel has a few ways of letting you know there is a circular reference error in your spreadsheet.
The first time you do a circular reference, you’ll receive a warning message.
FYI, you’ll also see this message if you open a workbook that contains a circular reference.
For example, pressing Enter to run the calculation in the example above would trigger a circular reference warning message.
Because the circular reference cannot calculate itself, clicking OK on the warning, would give you a result of 0.
The trouble with this warning is that it’s only displayed once per session.
If you create another circular reference, you will not get the message again!
Circular Reference Error – Dependent Lines
You may also see blue lines on your screen.
These typically show up when multiple formulas are trying to calculate each other, resulting in an error.
The blue lines show which formulas are dependent on one another.
If you create a second circular reference with formulas, the blue lines will move to the new error.
How do you find circular references in Excel?
There are a couple of ways you can use to find circular references in your spreadsheet.
Each of these methods, let’s you see only 1 circular reference issue at a time.
You’ll need to fix the error before you can see the next.
1. See the circular reference notice in your sheet’s Status Bar
The fastest way to spot a circular reference is by looking in the Status Bar at the bottom of your spreadsheet.
If there is a circular reference in your sheet, it will be displayed here.
If the circular reference is in a different sheet of your workbook, you’ll see “Circular References” on the Status Bar, but no cell address.
2. Use the Circular Reference Error Checking Tool
You can also see which cells contain a circular reference by looking in your Excel’s Circular Reference error checking tool
You’ll find this in the Formulas tab, under Formula Auditing. Click Error Checking, then Circular References.
If your circular reference is a result of selecting the cell that contains the calculation, you’ll see the cell address.
If your circular reference error is a result of formulas referencing one another (when you see the blue lines), the Circular Reference error tool will show you the cells involved in that calculation loop.
If the circular reference is in a different sheet of your workbook, you’ll see the name of the sheet and the cell location.
How do you fix circular reference errors?
To fix circular reference errors, you need to update the cells in your formulas so they do not reference each other.
If you included one cell too many in your selection, you should fix this in your formula.
If you have formulas referencing each other or have a busier spreadsheet:
- Go to the cell with the circular reference
- In your Formulas tab, in the Formula Auditing section, select Trace Dependents
This will bring up the blue arrows, making it easier to see which cell needs to be fixed.
To fix this error, you may want to break up the formulas into separate cells so that they are not referencing one another.
Allow Circular Reference in Excel
But, what if you need to allow circular references?
Maybe what you’re working on requires you to calculate a Circular Reference.
To allow circular references to iterate in Excel:
- Go into your Excel Options
- Then the Formulas tab
- In the Calculation Options section
- Check off Enable Iterative Calculation
- Click OK to apply your change
Here, you can also specify the maximum amount of iterations you’ll allow and the maximum change amount.
This will force Excel to calculate and recalculate to help solve your formula.
Be cautious using this option. Once selected, it will apply to all your workbooks. You may not notice an accidental circular reference as Excel will try to calculate a result.
Leave a Reply