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:
   
    
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:
   
    

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.

Indirect circular references

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

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.

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:

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.