Circular references can be quite tricky, so knowing how to find them is important. Excel has a built-in mechanism that can detect circular references and prevent calculations from going through 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. Also, 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 encounter some kind of 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, either directly or indirectly. This could cause them to incorrectly calculate «.
Since an endless loop can go on forever or disappear before the correct answer is reached, it is better to stay away from circular references in Excel. Not only that, but circular references can also slow down the entire calculation process in your workbooks to a great extent. However, in most cases, the biggest problem with circular references is identifying them.
There are three types of circular references: unintentional, intentional, and hidden. Most circular references are unintentional, as it would take someone with experience using Excel to create a deliberate circular reference. Finally, we have hidden circular references. While 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 click the “OK” or “Help” button. The latter will only give you more information on circular references without pointing out where they are in your workbook. On the other hand, if you choose “OK” or if you simply deactivate the message, you will find 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 not, Excel will stop notifying you about this matter.
In very rare cases, a formula containing a circular reference can be completed before the autocalculation 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 is why identifying it is the most important step.
To fix a circular reference error in Excel, you need to find it first. Follow the steps below to find out how to do it:
- Turn off the warning message that Excel displays.
- Go to the “Formulas” tab in the top menu.
- Go to the “Error checking” tab and click on it.
- Click on “Circular References” in the drop-down menu. This is where the circular references will be revealed.
- Click on the value in the pop-up list and you will be taken directly to that circular reference.
When you click the cell that contains the circular reference, it will also be displayed in the address bar at the bottom of the sheet.
If you need more help with circular referencing, there are two tools that can help you: track precedents and track dependents. The first tool, Trace Precedents, displays blue lines in the Excel workbook showing you which cells affect the cell you clicked on. Tracking dependents, on the other hand, does the opposite. They draw lines to show you which cells are affected by the cell you clicked on. These two functions help you find circular references that Excel cannot detect. Note that these markers don’t show you exactly where the circular reference is, just a hint to help you find it faster.
If you want to enable precedence tracing and dependent tracing, please follow the steps below:
- Go to the “Formulas” tab at the top of your spreadsheet.
- Continue with the category “Formula Auditing”.
- Select ‘Track Precedents’ or ‘Track Dependents’.
You can only select one at a time. A faster way to do this is to use these shortcuts: “Alt + TUT” for follow precedents, or “Alt + TUD” for follow dependents.
Some Excel users purposely create circular references to perform iterative calculations. But it is generally not a good idea to incorporate circular references on your sheets.
How to remove a circular reference
When you finally find the circular reference that is causing all the problems in your Excel workbook, you will want to remove it. While there is no way to fix it automatically, you can find out which part of the formula needs to be removed or replaced. You will know that you have solved the problem when you click on the cell and there is no “Circular Reference” label 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 is generally nothing to be cornered from. However, if you want to check if the iterative calculation feature has been enabled in some way, this is what you should do:
1. Go to the “File” tab in the upper left corner of your screen.
2. Continue with the “Options” section at the bottom of the menu.
3. Choose “Formulas” in the left sidebar of the pop-up window.
4. Under “Calculation Options”, go 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 2019. 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 should go to “Menu” and then to the “Tools” tab. The “calculation tab” will be in the “Options” section.
Find all circular references in your Excel workbook
In most cases, circular references are created by accident, but they can be quite troublesome. Not only do they mess up the entire formula, they can also slow down the entire calculation process. That is why it is important to find and replace them as soon as possible. Fortunately, Excel will inform you the moment one is detected. You can also use tracking precedents and tracking 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? Let us know in the comment section.