How do you remove Microsoft Excel Cannot calculate a formula there is a circular reference?

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

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

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.

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:

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. 😊

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

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:

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. 😓

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.

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

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

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

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

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.

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.

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.

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. 😊

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.