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.
A circular reference occurs when a formula is entered into a cell and then uses the reference to the cell where it was entered to perform the calculation. Don’t worry if this statement seems a little unclear; it will start to make sense by the end of this tutorial.
How to find a circular reference in excel (quick and easy fix)
How to find circular references in Excel
Excel automatically disables circular references and issues an error message when it does. If users enable interactive calculations, they can click the “OK” button to reset the cell to “0.” Excel won’t repeatedly display alerts if you enter multiple formulas with circular references. By choosing “Error Checking,” you can view a list of all circular references in your worksheet. You can use the steps listed below to check Excel for circular references:
1. Go to the “Formulas” tab
Open the spreadsheet you want to review and select the menu option from the page’s top to find circular references in Excel. The “Formulas” tab can be found between the “Page Layout” and “Data” tabs in the “Formula Auditing” group section. This tab allows you to review formulas on your spreadsheet.
2. Select “Error Checking”
Next, activate the drop-down menu by clicking on the arrow next to “Error Checking.” Choose “Circular References,” the final option, from the menu by scrolling to the bottom. The cell numbers with circular references will then have a box with a checkmark next to them.
3. Click the cell under “Circular References”
A status bar shows up after you choose “Circular References” from the drop-down menu to inform you whether it discovered a circular reference in your workbook. The status bar displays the last cell where it occurred if your worksheet has one. For instance, the status bar would display Circular reference: C4 if cell C4 contained a circular reference. These procedures can be repeated to check for additional circular references.
4. Fix your circular references
If you find a circular reference on your worksheet, you can fix it by going to the cell where it appears. The formula can then be changed to a value or modified in some other way. Once the cell is fixed, the status bar warning vanishes, allowing you to focus on removing any other circular references that may already be present.
What is a circular reference in Excel?
When a formula in Excel refers to its own or another cell value more than once while performing calculations, it is said to be using a circular reference. When a formula in one cell uses data from another cell to perform calculations, references happen in Excel. Excel cannot finish the formulas because they require a result that depends on the original cell when cells refer to themselves or when another cell refers to the original cell.
For instance, the equation =B1+B2+B3 fails because it is in cell B3 and cannot calculate itself. Any additional calculations or formulas using B3 would result in a circular reference.
Circular references produce infinite loops, which can slow down your spreadsheet and make your formulas calculate incorrectly because they constantly change the value of your cells. These can occur either directly or indirectly.
Different types of circular references in Excel
Circular references can appear in Excel in the following ways:
When is it important to use circular references in Excel?
When performing a calculation in Excel where you need the final result to be a new input value for your calculation, it’s crucial to use circular references. For instance, you might use a circular reference when developing a calculation model for a wind turbine plant where some of the input energy is converted into the output energy of the same process. Additionally, circular references come in handy when you want a cell to remember the date that you entered something in another cell.
Tips for finding and resolving circular references in Excel
The following tips will help you identify and fix circular references in Excel:
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
How do I check circular references?
- If you are unable to locate the error, select the Formulas tab, select Error Checking from the drop-down menu, then select Circular References. Finally, select the first cell in the submenu.
- Review the formula in the cell.
