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 prevent calculations from going into an endless loop. You just need to know how to enable and use this feature to your advantage.
In this article, we will show you how to find circular references in Excel. Plus, we'll explain what exactly circular references are in the first place and why you should avoid them.
How to Find a Circular Reference in Microsoft Excel
When you try to enter a formula in your Excel workbook and you encounter a problem, you may be dealing with a circular reference. This happens when the formula tries to use its own value to perform 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 directly or indirectly. This could cause them to calculate incorrectly.
Since an endless loop can go on forever or stop before the correct answer is reached, it's best to stay away from circular references in Excel. Not only that, but circular references can also significantly slow down the whole calculation process in your workbooks. However, in most cases, the biggest problem with circular references is identifying them.
There are three types of circular references: unintentional, intentional, and hidden. The majority of circular references are unintentional because it would take someone who is adept at using Excel to create a deliberate circular reference. Finally, we have hidden circular references. Although accidental circular references are easy to find, Excel cannot always detect hidden circular references, so you will have to use other methods.
When the warning message appears, you can either click the "OK" or "Help" button. The latter will only give you more information about circular references without telling you where they are in your workbook. On the other hand, if you choose "OK" or simply disable the message, you will find either the last calculated value or zero in your last cell. It is also important to note that this notification will not always appear. For example, if you keep creating more circular references, intentionally or unintentionally, Excel will stop informing you about it.
Very rarely, a formula that contains a circular reference may be completed before the auto-calculation mechanism is in motion. In this case, only the last successful value will be displayed accordingly. 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 circular reference error in Excel, you must first find it. Follow the steps below on how to do this:
- Disable the warning message displayed by Excel.
- Go to the “Formulas” tab in the top menu.
- Navigate to the "Error Checking" tab and click on it.
- Click "Circular References" from the drop-down menu. This is where all circular references will be revealed.
- Click on the value in the popup list and you will be taken directly to that circular reference.
When you click on the cell that contains the circular reference, it will also appear 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: 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 the other hand, trace dependents do the opposite. They draw lines to show you which cells are affected by the cell you clicked on. Both of these features help you find circular references that Excel cannot detect. Keep in mind that these plotters don't show you exactly where the circular reference is, just a hint to help you find it faster.
If you want to enable trace precedents and trace dependencies, follow the steps below:
- Go to the "Formulas" tab at the top of your spreadsheet.
- Switch to the "Formula Audit" category.
- Select either "Trace Precedents" or "Trace Dependents".
You can only select one at a time. A faster way to do this is to use these shortcuts: "Alt + TUT" for trace precedents, or "Alt + TUD" for trace dependencies.
Some Excel users create circular references on purpose to perform iterative calculations. But it's generally not a good idea to incorporate circular references into your sheets.
Additional FAQ
How to delete a circular reference
When you finally find the circular reference causing all the problems in your Excel workbook, you need to delete it. Although there is no way to fix it automatically, you can determine which part of the formula needs to be removed or replaced. You'll know you've fixed the problem when you click on the cell and there's no "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 worry about. However, if you want to check if the iterative calculation feature has somehow been enabled, here is what you should do:
1. Go to the "File" tab in the upper left corner of your screen.
2. Go to the “Options” section at the bottom of the menu.
3. Choose "Formulas" from the left sidebar of the pop-up window.
4. Under 'Calculation Options', navigate to the 'Enable Iterative Calculation' box to see if it is 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 2022. If you have Excel 2007, you will find the iterative calculation function 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 an earlier version, you must go to "Menu" and then to the "Tools" tab. The "Calculation" tab will be in the "Options" section.
Locate all 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 whole formula, but they can also slow down the whole calculation process. That's why it's important to find and replace them as soon as possible. Fortunately, Excel will notify you as soon as it is detected. You can also use trace precedents and trace dependencies 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? Let us know in the comments section below.
