How to fix circular referencesAbout circular reference errors, circular reference warnings, how to find and correct them... and when to embrace circular references!
This tip helps clear up a lot of frustration caused by trying to troubleshoot circular references.
The Quick Tip lets you quickly identify and correct a circular reference.
The Bonus Tip shows you how to trace the cause of circular references.
Extending this tip using the Expert Tip shows you when and how to use deliberate circular references to your advantage.
We hope you find these tips useful.
How to identify and correct a circular reference
A circular reference is when a cell’s formula refers to itself, either directly or indirectly. There are two common causes of circular references:
Unintentional circular references: it is quite common to create a circular reference by accident. When this happens, it can cause issues unless corrected.
Intentional circular references: sometimes it can be useful to create a circular reference on purpose, if your model or use case is appropriate for this approach and your spreadsheet is set up to control the operation of circular references. See the Expert Tip for details on how to use circular references deliberately.
Circular reference error and warning message
There are two ways to tell if you have a circular reference:
- You will see the circular reference warning dialog when recalculating a worksheet, which looks like this:
- The status bar will display “Circular:” and the location of the cell with the circular reference (if it is on the current worksheet), like this:
What causes a circular reference?
When a cell formula refers to that same cell, it creates a circular reference.
The exact cause will depend on whether it is a direct or an indirect type of circular reference.
Direct circular references
A direct circular reference is caused by a formula referring to the cell that contains it.
If cell F11 contains the following formula, it would cause a direct circular reference:
Indirect circular references
An indirect circular reference is caused by a formula referring to a cell that relies on the first cell to calculate.
An indirect circular reference would result in the following situation:
Cell F14 contains the following formula:
and cell F15 contains the following formula:
Indirect circular references are often caused by more complex references, such as three or more cells where each cell relies on one of the others in a ‘circular’ manner.
How to correct a circular reference
To correct a circular reference, you need to adjust one or more of the formulas that form part of the circular reference chain. Your adjustments need to remove the reliance on other cell/s that calculated based on the cell that you adjust.
The first step is to find which cell is causing the circular reference warning or error. This can sometimes be challenging, but thankfully there are some tools to make it easier:
- Status bar: If you see a cell reference after the word “Circular” in the status bar, start by correcting the formula in that cell. It may be the only cell causing the circular reference, or you may have to revisit the status bar after fixing it to check if it indicates another cell also has a circular reference.
- Ribbon toolbar: This is sometimes the fastest method of identifying a circular reference. Just follow the steps below.
Time needed: 1 minute
How to identify a circular reference in Excel using the ribbon toolbar
- Click on the ‘Formulas’ tab of the ribbon toolbar
The ‘Formulas’ tab is available by default, so should be visible on your screen.
- Click on the dropdown arrow next to the ‘Error Checking’ submenu
You will find ‘Error Checking’ in the ‘Formula Auditing’ toolbar button group. Even if the ‘Error Checking’ submenu is inactive, the dropdown beside it will be available if there is a circular reference in the worksheet.
- Click on ‘Circular References’ dropdown button
This will show a submenu of circular reference cell addresses.
- Click the cell address containing the circular reference
This will navigate directly to that cell so that you can edit the formula that causes the circular reference.
Keyboard shortcut to go to circular reference cell
You can also use the following keyboard shortcut to quickly go to the cell containing the circular reference:
|Identify and navigate to circular reference cell||Alt then M then K then C then Enter|
Tips for fixing circular references
Here are some tips on how to correct common causes of circular references in formulas.
SUM function including total cell
Sometimes a calculated total cell inadvertently includes a reference to itself. When this happens, simply reduce the range in the SUM function by 1 cell, so that it still includes the cells to sum but no longer also includes the total.
Cut and paste to quickly move circular reference formula
To quickly troubleshoot or correct a circular reference, you can usually cut that cell and paste it elsewhere. This only works for cut (not copy), because cut preserves formulas verbatim whereas copy adjusts formulas relative to the new cell address.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
Download files for this tip
Get one for free.