Do you know how to find circular reference in Excel? Find it with us!
In Excel, when you work with formulas, you may see a warning prompt. The prompt tells that your worksheet has the circular reference and it can lead the incorrect calculation by using the formulas. It also suggests you find the circular reference and sort it quickly.
Therefore, in this article, we will enlighten you on how to find a circular reference in Excel 2013, 2016, and others. Once you address the circular reference, you can remove it immediately to make your calculation accurate and fast in Excel.
The tutorial explains the following details about a circular reference in Excel:
What Does Circular Reference Mean in Excel?
The circular reference refers to the formula. When you end up entering the formula for calculation in one cell (which uses its own cell), it may create an endless loop which as a result slows down the spreadsheet.
For instance, you have a dataset in a cell A1:A5 and you are using the =SUM(A1:A6) formula in cell A6. Now this will show a circular reference warning pop-up on your screen.
That is because here you are summing the values in A1:A6 cell. Its result must appear in the A6 cell. It will create a loop because Excel will keep on adding a new value in the A6 cell. Hence, it appears as a circular reference circuit in Excel.
These circular reference loops may crash your calculation or slow down your sheet. Unfortunately, a user does not see the direct indication that what cell is causing the issue.
So, if you want to know how to find where circular reference is in Excel then we will teach you. In addition, you will discover how to remove the circular reference issue and fix it smoothly. Let’s get into more detail.
How to Find a Circular Reference in an Excel Workbook?
As we said before, circular reference error prompts if it is in the Worksheet. However, it does not notify where the circular reference exactly is and what cell reference causing this error.
Most users drop their concerns about how can I find a circular reference in Excel and other series. Here, we will tell you how you can find and resolve circular references in your Worksheet.
Looking for Excel Circular Reference? Find it here!
There are two ways to address the circular reference issue. You can either view cell reference through the Formulas in the menu bar or check Circular Reference in the status bar.
Both methods are easy to implement. The only difference is that the status bar is a quick way to check the circular reference whereas the menu bar requires you to navigate through the options manually.
Here, we will understand both methods with a manual procedure. For that, you can go through the steps we are discussing below. Let’s get started!
How Do You Find a Circular Reference in Excel through the Menu bar?
Excel Circular Reference: Find and Enable in the following steps using the Menu bar settings:
- Open the Worksheet that contains the circular reference.
- Click on the “Formulas” located on the top left menu.
- Go to the “Error Checking” menu as a drop-down in the “Formula Editing” group.
- Now, hover the mouse cursor over the “Circular References”. It shows that your Worksheet possesses a circular reference.
- Here, select the cell address that displays next to the “Circular References”. This takes you to the cell that is causing circular reference.
If you are thinking about how do I find the circular reference in excel, the above-mentioned steps would help you. Similarly, you can visit more cells that cause a circular reference by following the above-mentioned steps. If your spreadsheet does not show any circular reference issue then it will not show any of the cell references.
How Do You Find Circular References in Excel through the Status bar?
Excel Circular Reference: Find and Enable using the Status bar. For that, you can follow these steps:
- Activate the Worksheet that has a circular reference.
- On the left bottom of the sheet, you will see “Circular Reference” along with its cell address.
This is the direct and quick way to check the circular reference in your Worksheet. If you see no cell address in the status bar, it indicates that your sheet is free from any cell reference issue.
Important Things to Remember about Circular References
There are certain things about the circular reference that you should keep in your mind. Look at the following points:
- If you have enabled the iterative calculation then you would not be able to check the circular reference cell address in the status bar.
- In case a circular reference does not exist in the active sheet but in other worksheets then this will not show the cell address but the circular reference.
- When you launch the Workbook that contains a circular reference, this will instantly prompt the circular reference issue.
- If you see a warning prompt for a circular reference and you intentionally dismiss it then the prompt will not appear again. This may not alert you that your sheet has a circular reference.
Now that you know how to find circular reference in Excel 2007 and other series, you should also know how to remove it from your sheet.
How to Remove the Circular Reference in Excel?
Once you address that your sheet has circular references, you can remove it immediately to fix the calculation issue.
Important Note: You cannot remove the circular reference just by hitting the Delete key. Since the circular reference refers to the formulas and each formula may be different so you have to analyze this issue on the basis of case by case.
You can correct the issue by adjusting the cell reference. However, it is not that simple. Circular reference issues can be problematic for multiple cells. In such a scenario, you can treat this circular reference error with the Trace Precedents feature. This will help you to identify cells that are feeding cells with circular reference.
Let’s know how to find a circular reference in Excel Workbook and fix it using the Trace Precedents.
- On your worksheet, select the cell containing a circular reference.
- Then, click on the “Formulas” located on the top menu bar.
- Select the “Trace Precedents” option on the right.
Once you are done performing these steps, you will see the Blue Arrows. This shows what cell is feeding into a formula in the cell (that you selected). This is how you can find out the cells and formula that are causing the circular reference and resolve them.
Note: If you work with complex financial models then this precedent can go multiple levels. This method works well when all the formulas are referring to the cells in the same spreadsheet. If it is in multiple sheets then this method may not work well.
How to Allow Iterative Calculations in Excel?
In some instances, a user may want to keep the circular reference in the sheet for a number of iterations. However, it creates loops but if that is the preference then you can set how many times a loop should run in your sheet. This is what we call iterative calculations in an Excel sheet.
Therefore, we will learn how to enable to disable iterative calculation in the Excel sheet. To do so, follow these steps instructed below:
- Use any of the following ways to enable iterative calculation:
- In Excel 2010, click on the “File” tab.
- Then, click on “Options”. This will bring up the “Excel Options” dialog box.
- Here, click on the “Formulas” located on the left.
- In Excel 2007, click on the “Microsoft Office” button.
- Click on the “Excel” options.
- Go with the “Formulas” category.
- In Excel for Mac, click on the “Excel” menu.
- Click on the “Preferences” option.
- Select the “Calculation”.
- Now, check the box that says “Enable iterative calculation” in the “Calculation options” section.
- At this point, you can mention the “Maximum Iterations” and “Maximum Change” value.
You are done! After implementing these steps accordingly, the iterative calculation will be enabled in Excel as per your preferences.
Remember that If you enable the iterative calculation then you might not see the circular reference cell address in the status bar. Also, the circular reference will be turned off once you enable the iterative calculations.
To understand better these two options (in the iterative calculation), read below about them.
Maximum Iterations: A maximum number of times you require your Excel sheet to calculate (before it provides the final result). If you enter 100 as your maximum iteration then your Excel sheet would run the loop a Hundred times before proving the final result.
Maximum Change: If maximum change is not obtained between the iterations then the calculation will be discontinued. By default, the maximum change value is .001. Adding a lower value such as .001 would get you an accurate result.
Note: The more the iterations run, the more time it takes for Excel to perform the calculation. If you keep the iteration as your maximum number then Excel may slow down or crash entirely.
If you are wondering how to find a circular reference in Excel 2016 and other series then you can enable iterative calculations to disable circular reference.
Wrap up
Circular reference has both positive and negative impacts on your Excel sheet. There are instances when a user wants to enable the circular reference for calculations. However, enabling the circular references may slow down or crash the sheet. As a result, your calculation can go wrong. To prevent this, you can enable an iterative calculation option to get rid of circular references.
In this guide, we talked about how do you find circular references in Excel 2010 and other series. Besides that, we found easy ways to remove this error from the spreadsheet.
Hopefully, you find this guide useful. In case you have any questions or suggestions, let us know in the comments box.
To know more about Excel features and its functions, check out the following guides: