Home and Learn: Microsoft Excel Course
How to Find a Circular Reference in Excel
You may have seen this error message when you hit the enter key in Excel:
There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.
Try removing or changing these references, or moving the formula to different cells.
This error message tells you that you have made a circular reference in excel. But what is the message talking about, and how can you fix it?
What is a Circular Reference in Excel?
A circular reference is one where the formula or function is trying to calculate itself. An example may clear things up.
On a new blank worksheet, enter the following in cells A1 to A4:
31
64
87
95
Now, suppose you want to add up all these values and have the answer appear in cell A5. The formula is easy enough, and you should know how to do it by now. In cell A5, you'd just enter this:
= A1 + A2 + A3 + A4
If you typed that in cell A5, and then pressed the enter key on your keyboard, you'd get an answer of 277.
So far so good. Now, supposed you made a mistake. Suppose in cell A5 you entered this:
= A1 + A2 + A3 + A4 + A5
Go ahead and enter that in cell A5. I'm sure you can guess what happens - the dreaded Excel circular reference error message appears. Click OK and cell A5 will contain a value of 0.
Try this next example.
In cells B1 to B4, enter these numbers:
23
45
81
27
Now, in cell B5, enter this function:
= Sum(B1:B5)
Again, press enter on your keyboard. What you'll find is that you get a value of 0 again. But you don't get the error message popping up! Even though you have another circular reference, Excel didn't tell you about it.
That's because you haven't fixed the first one.
Check for Circular References
To see which cell Excel is pulling you up about, you can check for circular
references. Click on the Formulas tab at the top of Excel. On the Formulas
tab, locate the Formula Auditing panel:
On the Formula Auditing panel, click the dropdown for Error Checking. You should see an item for Circular References:
On the list, is your first error, cell A5.
Fix this error by entering this in cell A5:
= A1 + A2 + A3 + A4
Press enter to see the value change from 0 to 277.
Now go back to the Formula Auditing panel and click the drop down for Error Checking. You should see cell B5 appear on the Circular References menu item:
Fix that one by entering this in cell B5:
= Sum(B1:B4)
Press enter to see a value of 103.
So, in both cases, we got a circular reference error because, in our formula and function, we included the cell where we wanted the answer.
If you look at the bottom of Excel, in the status bar, you'll also see which cell is causing the problem:
If you have a big spreadsheet and are not sure where the formulas and functions are, you can use the Trace Precedents button on the Formula Auditing panel to find them:
Amend your function in cell B5 to this:
= Sum(B1:B5) + A5
For this to work, you need to click inside a cell that contains a formula or function. Make sure cell B5 is selected. Now click the button above. You should see some arrows appear on your spreadsheet:
The arrows tell you which cells are being referenced in your formula. In this case, cells B1 to B5 and cell A5.
Circular references can cause you a headache, but most of them are because of the issues outlined above.
In the next lesson below, you'll learn about Array Formulas.
<--Back to the Excel Contents Page