How To Find A Circular Reference In Excel

Circular references can be quite tricky, which is why it’s important to know how to find them. Excel has a built-in mechanism that can detect circular references and stop calculations from going in an endless loop. You just need to know how to enable and use this feature to your advantage.

In this article, we’ll show you how to find circular references in Excel. In addition, we’ll explain what exactly circular references are in the first place and why you should avoid them.

How to Find Circular Reference in Microsoft Excel

When you try to enter a formula in your Excel workbook and encounter some kind of an issue, you might be dealing with a circular reference. This happens when the formula is trying to use its own value to make calculations. At this point, Excel will send you a warning message:

“There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.”

Since an endless loop may keep going forever or quit before the right answer is reached, it’s best to stay away from circular references in Excel. Not only that, but circular references can also slow down the entire calculating process in your workbooks to a great extent. However, in most cases, the biggest issue with circular references is identifying them.

There are three types of circular references: unintentional, intentional, and hidden. The majority of circular references are unintentional since it’d take someone who is skilled at using Excel to create a deliberate circular reference. Finally, we have hidden circular references. While accidental circular references are easy to find, Excel can’t always detect hidden circular references, so you’ll have to use other methods.

When the warning message pops up, you can either click on the “OK” or “Help” button. The latter will only give you more information about circular references without pointing out where they are in your workbook. On the other hand, if you choose “OK” or if you just turn the message off, you’ll either find the last calculated value or zero in your last cell. It’s also important to note that this notification won’t always appear. For example, if you keep creating more circular references, intentionally or unintentionally, Excel will stop notifying you about this matter.

Very rarely, a formula that contains one circular reference can be completed before the self-calculation mechanism is in motion. In that case, only the last successful value will be displayed as a result. In other words, a circular reference can make the system unresponsive. That’s why identifying it is the most important step.

In order to fix a circular reference error in Excel, you need to find it first. Follow the steps below to find out how it’s done:

1. Turn off the warning message Excel displays.
2. Go to the “Formulas” tab on the top menu.
   
3. Navigate to the “Error Checking” tab and click on it.
   
4. Click on “Circular References” on the drop-down menu. This is where any circular references will be revealed.
   
5. Click on the value on the pop-up list, and you’ll be taken directly to that circular reference.
   

When you click on the cell that contains the circular reference, it will also be displayed in your address bar at the bottom of the sheet.

If you need more help with the circular reference, there are two tools that can help you – trace precedents and trace dependents. The first tool, trace precedents, displays blue lines in the Excel workbook that show you which cells affect the cell you clicked on. Trace dependents, on the other hand, do the opposite. They trace lines to show you which cells are affected by the cell you clicked on. These two features help you find circular references that Excel can’t detect. Keep in mind that these tracers don’t show you exactly where the circular reference is, just a clue to help you find it faster.

If you want to enable trace precedents and trace dependents, follow the steps below:

1. Go to the “Formulas” tab at the top of your spreadsheet.
   
2. Proceed to the “Formula Auditing” category.
   
3. Select either “Trace Precedents” or “Trace Dependents.”
   

You can only select one at a time. A quicker way to do this is to use these shortcuts: “Alt + T U T” for trace precedents, or “Alt + T U D” for trace dependents.

Some Excel users create circular references on purpose to make iterative calculations. But it’s generally not a good idea to incorporate circular references into your sheets.

Additional FAQ

How to Remove a Circular Reference

When you finally find the circular reference that’s causing all the issues in your Excel workbook, you’ll want to remove it. While there is no way of fixing it automatically, you can figure out which part of the formula needs to be removed or replaced. You’ll know that you’ve solved the problem when you click on the cell and there isn’t a “Circular Reference” tag in the address bar.

Circular references can only be created in your Excel sheet if the iterative calculation feature is enabled. This feature is disabled by default, so there’s usually nothing to be cornered about. However, if you want to check whether the iterative calculation feature has somehow been enabled, this is what you need to do:

1. Go to the “File” tab in the upper-left corner of your screen.

2. Continue to the “Options” section at the bottom of the menu.

3. Choose “Formulas” on the left sidebar of the pop-up window.

4. Under “Calculation Options,” go to the “Enable iterative calculation” box to see if it’s checked.

5. Uncheck it to disable iterations.

6. Click on the “OK” button.

This method can be applied to Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have Excel 2007, you will find the iterative calculation feature when you click the Office button and go to “Excel Options.” The “Iteration area” section will be in the “Formulas” tab. If you have Excel 2003 or earlier, you need to go to “Menu” and then to the “Tools” tab. The “Calculation Tab” will be in the “Options” section.

Locate All the Circular References in Your Excel Workbook

In most cases, circular references are created by accident, but they can be quite problematic. Not only do they mess up your entire formula, but they can also slow down the entire calculation process. That’s why it’s important to find them and replace them as soon as possible. Fortunately, Excel will inform you the moment any is detected. You can also use trace precedents and trace dependents to help you discover the relationship between the circular reference and your other cells.

Have you ever tried to find a circular reference in your Excel sheet before? How did you find it? Let us know in the comments section below.