Updated November 2024: Stop getting error messages and slow down your system with our optimization tool. Get it now at this link
- Download and install the repair tool here.
- Let it scan your computer.
- The tool will then repair your computer.
In a Microsoft Excel spreadsheet, you may have formulas in one cell that require the information in another cell for calculation. These relationships are called references. If 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 event is called a circular reference, and if you have a large spreadsheet with thousands of formulas, a circular reference can cause the entire sheet to be calculated incorrectly.
You have entered a formula, but it doesn’t work. Instead, you get this message about a “circular reference”. Millions of people have the same problem, and it’s because your formula is trying to calculate itself, and you have disabled a function called iterative calculation. This is what it looks like:
The formula =D1+D2+D3 breaks down because it lives in cell D3 and it’s trying to calculate itself. To solve the problem, you can move the formula to another cell. Press Ctrl+X to cut the formula, select another cell and press Ctrl+V to paste it.
Another common error is to use a function that contains a reference to itself; for example, cell F3 =SUM(A3:F3).
How to Find Circular References in Excel
To check if your Excel workbook contains circular references, follow these steps:
- Go to the Formulas tab, click the arrow next to Error Check and point to Circular References This displays the last circular reference you entered.
- Click the cell listed under Circular References, and Excel takes you to that exact cell.
- When you do so, the status bar informs you that ring references have been found in your workbook and displays the address of one of these cells.
November 2024 Update:
You can now prevent PC problems by using this tool, such as protecting you against file loss and malware. Additionally, it is a great way to optimize your computer for maximum performance. The program fixes common errors that might occur on Windows systems with ease - no need for hours of troubleshooting when you have the perfect solution at your fingertips:
- Step 1 : Download PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista – Microsoft Gold Certified).
- Step 2 : Click “Start Scan” to find Windows registry issues that could be causing PC problems.
- Step 3 : Click “Repair All” to fix all issues.
If circular references are found in other sheets, the status bar displays only “Circular references” without the address of the cell.
Note. This function is disabled if the “Iterative calculation” option is activated. You must switch it off before consulting the workbook for circular references.
How to Remove Circular References in Excel
If a formula directly refers to its own cell or finally refers to its own cell via a string of references to other cells, Excel will give you a circular reference warning. The reason for this is that the formula cannot be calculated in the usual way because it can affect its own value when it is updated.
Often this is a sign of a typing error or a logical error, and you will want to find out what creates the circular reference and eliminate it.
- Open the spreadsheet
- Use the Formulas menu
- Search for a circular reference
- Tracking precedents with Excel
- Follow the reference chain
- Find other circles according to your needs
- Iterative calculation on Excel
Activating the Iterative Calculation Function
- Open Microsoft Excel
- Select File and go to Options
- Select formulas
- Check the box next to “Enable iterative calculation
Moving the Formula Manually to Another Cell
If you have a simple calculation, e.g. A1 + A2 + A3 = A4, but want to set the sum formula to A1, A2 or A3 instead of A4, this will trigger the reference error.
In this case, simply select a different cell for your formula without overlapping with cells that already contain numerical values.
Conclusion
If you follow the steps below, you should be able to correct any circular reference errors and continue to use Microsoft Excel accordingly.
Which method worked best for you? Let us know by leaving us a message in the “Comments” section below.
Expert Tip: This repair tool scans the repositories and replaces corrupt or missing files if none of these methods have worked. It works well in most cases where the problem is due to system corruption. This tool will also optimize your system to maximize performance. It can be downloaded by Clicking Here
