When Excel displays a circular reference warning, it typically identifies a formula in a cell that indirectly refers to another formula. Due to the formula’s ability to reverse its own result, a circular reference is created. In this step-by-step tutorial, we’ll go over everything you need to know about circular references, including how to avoid them and when you might want to use them.
Circular reference warnings will appear for any formula you attempt to enter into Excel that loops around on itself. For instance, if you’re using a SUMIF formula to analyze data and one of the cells in your data set refers to the output from the same cell that contains that formula, Excel won’t be able to calculate the result, leading to the appearance of this error.
Circular references in Excel can be useful in one specific situation, though. Circular reference formulas can be used to insert iterative calculations (calculations that repeat themselves a set number of times until certain conditions are met), but Excel’s settings must be adjusted in order for this to work.
- If you can’t find the error, click the Formulas tab, click the arrow next to Error Checking, point to Circular References, and then click the first cell listed in the submenu.
- Review the formula in the cell.
When does a circular reference error appear?
A circular reference error appear when you:
The error message appears in the center of your screen in a dialog box. It gives a straightforward explanation of what a circular reference is, indicates that there are one or more of them in the workbook, and explains how they could result in calculations that aren’t accurate. In addition to the error message, blue arrows denote the flawed calculation chain on the spreadsheet.
What are circular references in Excel?
In Excel, circular references occur when a formula refers to its own cell or a cell that depends on the formula’s outcome. Although a circular reference typically yields a value of 0, it may also yield the result of the cell’s most recent successful calculation. Heres an example to help you better understand circular references:
A1 and A2 each have values of 100 and 200 respectively. You are aware that entering the formula “=A1+A2” into the cell A3 results in the sum of these two values being 300. You enter the following formula into A3 to see what would happen if you included A3 in this equation: “=A1+A2+A3 Because A3 is the formula’s cell, the formula is unsure of how to handle it, so it returns a circular reference error.
How to check for circular references
After you create the first circular reference, the workbook might not warn you, so it’s crucial to manually check for them. You can produce more accurate spreadsheets by double-checking your formulas, especially when working with large amounts of data. Heres how to check for circular references:
Circular references are displayed in this drop-down menu throughout the entire workbook. You can quickly respond to each instance by clicking on it to automatically highlight the cell. The program disables the “Circular References” button if there are no examples of circular references.
Why do circular references occur?
A mistake is among the most frequent causes of circular references. Think about someone who wants to figure out the sum of A1 through A10 in A11, for instance. They might attempt to select cells A1 through A10 to designate these values as the addends after entering “=SUM(” into A11 The formula appears as “=SUM(A1:A11)” if the user unintentionally chooses A11. The formula’s A11 cannot calculate itself, causing a circular reference error.
A formula that depends on its own value causes circular references to also happen. Take a look at the spreadsheet below, which shows a worker’s base pay and bonuses. B3’s formula reads “=SUM(B1:B2)” to represent the employee’s overall cash compensation. A manager enters the following formula into B2 to determine that the employee bonus is equal to 10% of their total cash: “=B3* 1. This formula serves as an illustration of a circular reference because it uses B3 to reference its own cell.
How to remove circular references in Excel
Heres how to remove circular references in Excel:
1. Check the formula for references to the formulas cell
Select the cell that contains the circular reference. Check the formula for references to the formulas cell. For instance, if the formula in A8 is “=SUM(A1:A8),” it is circular. You could resolve this by switching the A8 to the A7 and pressing “Enter. “.
2. Check for indirect references
If there are no direct cell references in the formula, the error might be brought on by indirect references. To look at the references in the formula in greater detail, click on them. For instance, imagine that B4 contains this formula: “=SUM(B3+5). ” You can click B3 to view its formula. A circular reference results from B3 referencing B4 using a formula like “=B4+20.” Editing B3’s formula to ensure that it doesn’t reference B4 will correct the problem.
3. Copy the formula to a different cell
Copying the formula to a different cell is the last step for getting rid of circular references. Consider adding the values of A2, A3, and A4 as an example. You can move the formula “=SUM(A2:A4)” to a different cell if it was accidentally entered into cell A4. Right-click cell A4, choose “Cut,” then choose “Paste” on the new cell where you want the formula to appear. “.
How to allow circular references in Excel
Circular references may occasionally be desirable to enable the formula to perform iterative calculations. Instead of displaying zero, these calculations force functions to repeat in order to return a result that is as accurate as possible. Heres how to allow circular references in Excel:
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
Excel Troubleshooting : How to Remove Circular Reference
FAQ
How do I turn off one or more circular references?
The error message “There are one or more circular references” appears when a formula directly or indirectly refers to its own cell. This might cause them to calculate incorrectly. Remove or modify these references, or move the formulas to different cells, as your options. “.
How do I find hidden circular references in Excel?
In the ribbon menu at the top of the Excel window, select the “Formulas” tab. In that section, select the small arrow next to the “Error Checking” button. The most recent circular reference entered will show up when you hover your cursor over “Circular References.” To access that cell in the spreadsheet, click this reference.
