A circular reference in Excel will indicate that the formula calculation in a certain cell directly or indirectly refers to its cell once or several times hence creating an infinite loop. Despite it being unintended, a circular reference can slow down your spreadsheet.
A direct circular reference occurs in Excel when a formula directly refers to its cell. An "indirect circular reference" occurs when a formula indirectly refers to its cell.
Let's look at the steps to follow when you want to find a circular reference within your excel workbook.
1. First, turn off the warning message displayed by Excel.
2. Go to the Formulas tab on the main menu ribbon.
3. Go to the Formula Auditing group.
4. Tap on the "drop-down arrow" next to 'Error Checking to display a drop-down list.
5. Click on the Circular References option to display the last entered circular reference.
6. Next, click on the cell listed under Circular References. Excel will bring you directly to that cell.
7. Review the circular reference formula. Do this by replacing one of the formulas with a value. As a result, the warning displayed in the status bar will disappear.
The status bar will notify you of the circular references found in your workbook. It will also display the address of one of those cells. If the circular reference is found in other worksheets that are not displayed, the status bar will display only 'Circular References' with no cell address. To find more circular references, you will repeat the above steps.
Steps to follow to enable or disable circular references
For circular formulas to work in Excel, you will have to enable iterative calculations in your workbook. If you use Excel 2019, Excel 2016, and Excel 2010, you can follow the below steps to enable or disable circular formulas.
1. Click on the File tab > options
2. Click on the Formulas option.
3. Under the Calculation options section, select the 'Enable iterative calculation' checkbox.
4. Click OK.
In Excel 2007, you will first click the Office button. Next, click on the Excel options > Formulas > Iteration area.
In earlier Excel applications, the iterative Calculation option is under Menu > Tools > Options > Calculation tab.
Here you will set your own to specify how many times the formula should calculate. You must also specify the Maximum Iterations and Maximum Change when you enable the iterative calculations. Maximum Iteration by default is 100.
Maximum Change specifies the maximum Change between calculation results. By default, it is always at 0.001.
How to remove circular references
Now that you know how to find circular references within your Excel workbook let's look at how you can remove them.
Unfortunately, there is no prompt command in Excel that lets you eliminate all circular references formulas in a workbook with a button click. To remove them, you will have to inspect and look at each circular reference individually by following the above steps. You can either remove a given circular formula or replace it with simpler formulas.