In a Microsoft Excel worksheet, there may be formulas in one cell that require information from another cell to calculate. These relationships are called references. When a cell refers to itself or to another cell that refers to the original cell, the formulas cannot be completed because they require a result that depends on the original cell.
This process is called circular referencing, and if you have a large spreadsheet with thousands of formulas, circular referencing can result in the entire worksheet not being calculated correctly.
When a formula refers directly to its own cell or perhaps refers to its own cell through a chain of references to other cells, Excel issues a circular reference warning. This is because the formula cannot be calculated in the usual way. After all, it may affect its own value when it is updated.
This is often a sign of a typo or logical error, and you need to find the cause of the circular reference and correct it.
What is the cause of the circular reference error in Microsoft Excel
After reading the articles about circular reference errors in Excel, you probably have a fair idea of the nature of the problem.
Basically, a circular reference error occurs when a formula directly or indirectly references a cell that already contains it. As a result, the formula recalculates the result it has already obtained, which creates this type of error.
If there is a circular reference in the Excel workbook, the automatic calculation will not be performed.
Instead, you must use Excel’s error checker function to determine the position of the circular references and then remove them.
If you leave them as they are, Excel will calculate each cell involved in that circular reference using the results of the previous iteration.
Take note: Iterating here means repeating the recalculation process until it meets certain numerical criteria.
To solve a circular reference error in Microsoft Excel
Activate the “Iterative calculation” function
- Open Microsoft Excel
- Select File and go to Options
- Select Formulas.
- Check the box next to “Enable iterative calculation.”
Manually move the formula to another cell
If you have a simple calculation, such as A1 + A2 + A3 = A4, but you wanted to set the formula SUM to A1, A2, or A3 instead of A4, this will cause a reference error.
In this case, simply select a different cell for the formula that doesn’t overlap with cells that already contain numeric values.
Find the cell that is causing the circular reference error
- Running Microsoft Excel
- Launching the error message
- Click the Formulas tab.
- Select Check for errors.
- Select the circular reference entry.
- This automatically corrects all reference errors
Frequently Asked Questions
- If you can't find the error, go to the Formulas tab, click the arrow next to Check Errors, point to Circular Links, then click the first cell listed in the submenu.
- Check the formula in the cell.
Excel tutorial on circular reference error in Excel. This is a type of error that occurs when a formula in a cell tries to calculate itself. The most common cause of a circular reference error is that the cell that should contain the result is accidentally included in the function.
For circular formulas to work, you need to allow iterative calculations in your Excel workbook. In Excel 2019, Excel 2016, Excel 2013, and Excel 2010 click File -> Options, go to Formulas and check to Enable iterative calculation under Computation Options.
Circular reference errors occur when a formula references its own cell. For example, in the example shown, the formula in F7 is =F5+F6+F7. This creates a circular reference because the formula entered in cell F7 references F7.