RECOMMENDED: Click here to fix Windows errors and optimize system performance
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.
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.
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.
RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.