You want to make sure there are no circular references in the file. Go to tab ‘Formulas’, choose ‘Error-checking’ and ‘Circular References’. Excel will show you exactly in which cell(s) circular references are detected.
Finding circular references can be difficult, so it’s crucial to know where to look. Circular references can be detected by a built-in mechanism in Excel, which prevents calculations from running in an endless loop. All you have to do is learn how to activate and benefit from this feature.
How to find a circular reference in excel (quick and easy fix)
Types of circular references in Excel
Here are three major types of circular references in Excel:
Unintentional circular references in Excel
Most circular references are unintentional. When formulating equations in Excel to calculate the sum of a collection of numbers or another metric, users may unintentionally include the results cell. By eliminating these circular references, the user can successfully finish the calculation. Excels formula tools can detect most unintentional circular references easily.
Hidden circular references in Excel
When a conditional action results in the circular reference, an Excel spreadsheet has a hidden circular reference. For instance, if you use an IF formula to add a conditional equation to a cell, the value of that cell may be dependent on the value of another cell. Circular references can occasionally result from these formulas, which Excel’s tools have trouble identifying because the values of the cells can change. In these situations, the cell may have a red mark, but Excel may not indicate that it is a circular reference.
Intentional circular references in Excel
Advanced Excel users may use circular references for iterative calculations, even though most circular references are unintentional. These formulas compute results by using values from earlier iterations or cycles of calculation. Excel’s error-checking tools typically flag deliberate circular references as errors because these calculations slow down the computer’s processing speed, unless the user explicitly states that the equation is correct and specifies parameters for when to stop the calculations.
What are circular references in Excel?
In an Excel sheet, a circular reference occurs when a formula applied to a cell refers to the value of the cell. You could, for instance, enter values in cells B1, B2, and B3. To get the sum of the values in the column, you could click cell B4 and type the formula =SUM(B1:B4). The formula becomes circular because it refers to the cell it is applied to. Because the function is circular, Excel typically flags these errors rather than calculating them because the value is constantly changing. Running constant calculations can slow your computers processing.
How to find circular references in Excel
You can use a number of methods to identify and fix circular references if you’re creating a spreadsheet with formulas that don’t intentionally perform iterative calculations. Here are two main ways to find these occurrences:
Excel alerts
When Excel detects a circular reference, its background error-checking tools frequently notify the user. If you entered a formula in a cell, you might see a red mark there or a text box with an error message. The steps below can be used to fix a formula if you enter it in a cell and then get a circular reference alert:
You could, for instance, create a set of values ranging from B1 to B6. Excel may alert you if you enter the formula =SU**M(B1:B7) in cell B7 because it could result in a circular reference. You can open the formula and change it to =SUM(B1:B6) to correct it and obtain the sum of the set of values.
Excel error check tool
Use Excel’s error detection tool to check a whole spreadsheet for circular references. Here’s how to use Excel’s function to find accidental circular references:
It’s possible that some of the cells listed in the “Error Checking” submenu contain indirect circular references, which means that the values of those cells come from another cell’s circular reference. For instance, a circular reference in a column’s sum could have an impact on the outcomes of other columns where the sum is used in formulas. Before you identify the cell that is the source of the circular reference when you use the “Error Checking” tool, you may need to look through several flagged cells.
When to use circular references in Excel
If you are modeling a cyclical action or process in Excel, you might decide to use a circular reference. As an illustration, in some industrial processes, the output of one production cycle serves as the input for the subsequent cycle. In that case, each cycle’s results might be influenced by those of the previous one. Excel allows the process to repeat 100 times, or until the difference between the products of each cycle falls below 0 if a user confirms that a circular reference is accurate. 001. Additionally, users can modify values and choose their own number of cycles.
How to allow a circular reference in Excel
Excel can perform iterative calculations by verifying a circular reference, but doing so may cause your computer’s processing speed to lag while Excel completes the calculations. Heres how to allow a circular reference in Excel:
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
How do I find a circular reference?
- Circular References can be found by selecting the Formulas tab, Error Checking, and then clicking the arrow next to it. The most recent circular reference entered is shown there.
- When you click the cell under “Circular References,” Excel will take you directly there.
How do I find a circular reference in an Excel cell?
- Second, choose “Error Checking” from the drop-down menu on the Formulas tab of the Excel ribbon.
- The aforementioned action indicates in a sidebar that cell C11 on our worksheet contains a circular reference.
- Go to Formulas > Error Checking > Circular References, to put it briefly.
