How to fix circular references

About circular reference errors, circular reference warnings, how to find and correct them... and when to embrace circular references!

EQ

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.

Excel Quicker!

Quick Tip

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:

  1. You will see the circular reference warning dialog when recalculating a worksheet, which looks like this:
    Circular reference warning
     
  2. 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:
    Circular reference status bar
     

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.

For example

If cell F11 contains the following formula, it would cause a direct circular reference:

=F11

Indirect circular references

An indirect circular reference is caused by a formula referring to a cell that relies on the first cell to calculate.

For example

An indirect circular reference would result in the following situation:

Cell F14 contains the following formula:

=F15

and cell F15 contains the following formula:

=F14

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

  1. Click on the ‘Formulas’ tab of the ribbon toolbar

    The ‘Formulas’ tab is available by default, so should be visible on your screen. Locate the Formulas tab on the ribbon toolbar

  2. 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. Error checking dropdown

  3. Click on ‘Circular References’ dropdown button

    This will show a submenu of circular reference cell addresses. Circular references submenu

  4. 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. Circular references list

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:

ActionShortcut
Identify and navigate to circular reference cellAlt then M then K then C then Enter
Keyboard shortcut to identify and navigate to circular reference cells

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.


Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.