Concise step-by-step guide outlines how to find circular references in Excel, how to correct them and how to allow them.
Find more technology guides, tips and advice
Find more business guides, tips and advice
Quickly enter a few numbers in Microsoft Excel, mark cells, search for a formula and press Enter. Error message! What did you do wrong? Where is the circular reference when it shows an error and how can you fix it?
Perhaps it has already happened to you while working with Excel: You confirm an entered formula and suddenly an unsightly error message pops up. Instead of the expected result, only 0 values appear in the worksheet. What happened? A so-called circular reference has probably arisen from an input error.
Circular references occur when reference is made to cells that contain the formulas themselves. This creates a closed loop. A circular reference can interfere with calculations and references in Excel and lead to incorrect results.
A circular reference is a formula that uses itself as a variable.
- For example, if you want to calculate the sum of cells B1, B2, B3, B4 and also B5 in cell B5 in Excel, you will not come to any result, since B5 is both the result and part of the formula.
- Since such a formula would lead to an infinite loop, an error message is displayed when creating such a circular reference.
- Instead of the correct result, either no result, an error or 0 as the result is output in the field.
How to find circular references in Excel and correct them
If you accidentally create a circular reference, you can quickly find and correct it.
- As soon as you create the circular reference, Excel gives you an error message that informs you about the illegal formula.
- Use the warning to rectify the error in the cell immediately. To do this, change the cell with the error and do not include the current cell in the formula when entering it.
- If you want to check your document for further circular references, you can use Excel’s built-in error checking.
- To do this, select the “Formulas” tab at the top and select the arrow next to the “Error check” icon in the “Formula monitoring” category. This consists of a yellow warning triangle with a green tick.
- Then select the item “Circular references” to display any circular reference errors in your document.
- Now continue with each error as described above and remove the current cell from the formula.
How to allow and use circular references
If you have created the circular reference on purpose to perform a loop, you can activate the Circular Reference option and use it that way.
- Open Excel and choose File in the upper left , then choose Options in the lower left .
- If you’re using Excel on a Mac, click the Excel menu and select Preferences .
- Navigate in the options to ” Formulas ” or ” Calculation ” if you are using a Mac.
- In the top section ” Calculation options ” you can enable the loops by clicking on the “Activate iterative calculation” check box.
- Then select the maximum number of iteration steps that can be carried out. Note that the number of steps can affect your computer performance, and higher numbers may take longer to calculate.
- In the “Maximum change” field, select the smallest value that is necessary to execute an iteration. The smaller the number, the more accurate your result will be. At the same time, the calculation will take longer for smaller numbers.
- Depending on how the formula of your circular reference is structured, the result can either keep changing, become stable at some point, or constantly switch back and forth between values.
How to find circular references in Excel: Tips and tricks
Excel makes it easy for you to find an incorrect formula. However, there are also further tips that will make troubleshooting easier for you.
- The cell number in which the incorrect reference is located is not displayed in the status bar if the cell is in another worksheet in the workbook. Then you will only see “circular references” without a corresponding cell address.
- Under Formulas, when you click Track To Predecessor or Track To Successor, you’ll see an arrow indicating the cells that affect the value of the selected cell. This is how you can find out what the problem might be
Excel stops after recognizing the circular reference
In addition, after recognizing a first circular reference, Excel does not perform any further calculations on cells that also contain a circular reference. Therefore, these formulas are not all displayed in the list of circular references in the tab.
If you have resolved a circular reference, it is therefore a good idea to check whether more cells are now displayed in the list. If this is not the case, all problems caused by circular references are solved.
How to find circular references in Excel: Multiple circular references
If there are several independent circular references in a workbook, you will rarely see them all at the same time in the list of circular references in the “Formulas” menu. The reason is that once Excel has recognized the first circular reference, it does not perform any further calculations in cells with circular references. It is therefore advisable to look again after removing the first circular reference to see whether other cells are now displayed in the submenu. If everything remains gray there, all circular references are dissolved.
