Circular Reference in Excel: How to Find, Enable, Use, or Remove

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

One of the most common warnings users encounter in Excel is the circular reference error.

In fact, you may already have seen a few of them already on your reports (which is probably how you ended up here). 😉

The good news is, they’re not really that hard to understand.

In this article, let’s discuss circular references in Excel and how to find, enable, use, or remove one.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

What is a circular reference in Excel?

According to Excel, circular references happen when…

“A formula refers to its own cell either directly or indirectly.”

For example, if you write “=A1” on cell A1, you will get the circular reference warning:

Microsoft Excel gives you a circular reference warning

The reason is, the formula is referring to the cell itself where it is written.

When left unchecked, circular references can iterate indefinitely which results in an endless loop.

It may be fine if your data set is small. But if your data set is large enough, it could cause significant slowing down of your workbook.

Although there may be a few rare instances wherein a circular reference has solved a problem, it’s always best to stay away from them. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

How to use a circular reference in Excel?

One of the most common instances circular references happen is when the user uses a function (like the ‘SUM’ function) and accidentally includes a reference to itself.

Like this:

A simple case of a circular reference in Excel

This is easy enough if you have a small data set like in the image shown above.

But if you’ve got a large dataset, finding the cell that gave off the warning will be like finding a needle in a haystack. 😓

Kasper Langmann, Co-founder of Spreadsheeto

How to find circular references?

Fortunately, you can easily find the cell that gave off the warning (and it will even automatically select the cell for you).

To do so, go to the ‘Formulas’ tab.

Where to find the formulas tab

Then, click ‘Error Checking’‘Circular References’.

How to check the origin of circular references

Excel will automatically select that cell for you and even show the warning on the status bar.

How circular references are shown on the status bar

You can also display trace arrows to give you a clue if ever the circular reference isn’t that clear.

Kasper Langmann, Co-founder of Spreadsheeto

There are two types of trace arrows:

  • Trace Precedents: Show you the cells that provide data to the formula
  • Trace Dependents: Show you the cells that are dependent on the active cell

You can use one or both if applicable.

To do so, just click on either of their buttons on the ‘Formulas’ tab under the ‘Formula Auditing’ section.

How trace arrows work with circular references

How to enable or disable circular references in Excel?

By default, Excel will not allow circular references, which is why you receive warnings when it occurs on your workbook.

If you would like to turn it on, all you have to do is enable iterative calculations from the options.

To start, click on the ‘File’ tab.

file from the tab list

Then, select ‘Options’ (the lowest options on the left-hand bar).

On the ‘Excel Options’ window, go to the ‘Formulas’ section and tick the ‘Enable iterative calculation’ box.

Click ‘OK’ to save the changes.

How to enable iterative calculation

After that, you will not get any warning whenever there’s a circular reference.

However, we strongly advise you not to do this.

How to remove circular references in Excel?

Unfortunately, there’s nothing in Excel that will let you remove all circular formulas in the spreadsheet.

If you would like to eliminate them, you must examine each circular reference. Then, you can try to modify it, remove the formula altogether, or replace it with another one.

Kasper Langmann, Co-founder of Spreadsheeto

In the case of the example used earlier, all you need is to readjust the parameter of the ‘SUM’ function and only include cells from C4 to E6.

How to repair a circular reference in Excel

Once you resolve the formula, you will not get the circular reference warning anymore.

Wrapping things up…

Almost all users who have used Excel have probably encountered circular references already. It happens when a formula refers to itself directly or indirectly.

Although there are cases when circular references “might’ve” solved a problem, it’s really not advisable to use one. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Since there’s no way to remove it with a button, you really need to examine the formula that gave off the warning. If you can’t repair it, you might need to either remove the formula or use a simpler one.

If you’re lost at trying to solve a circular reference, don’t forget that there are trace arrows you can use to further give you a clue of why the reference is happening.