What is a circular reference error in Excel?

I recently had an email from a client asking me to assist with an error in one of their Excel files. I opened the file to take a look and immediately the error appeared: “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 the references, or moving the formulas to different cells.”

This error message is a common one, and regularly pops up during training sessions so I thought I’d provide an explanation of why this happens and how to correct it.

What is a circular reference?

A circular reference in Excel happens when a formula is referring back to the cell containing the formula, either directly or indirectly. This creates a loop where Excel keeps trying to calculate the value, but it can’t finish since it keeps going around in circles.

Here’s an example to provide a visual of the problem:

1. Imagine you click in cell A1.
2. You write a formula in A1 that says =A1 + 5.

In this case, A1 is trying to use its own value to calculate a value, which doesn’t make sense. Excel will show an error or a warning about a circular reference because it can’t solve the formula.

To fix this, you need to change the formula so it doesn’t loop back to the cell that contains it.

Circular Reference Error

So let’s start off by deliberately creating a circular reference error.

1. Open Microsoft Excel.
2. Create a new blank workbook.
3. Click in cell A1 and enter the text Income.
4. Click in cell A2 and enter the text Expenses.
5. Click in cell A4 and enter the text Savings:

6. Click in cell B1 and enter the number 5000.
7. Click in cell B2 and enter the number 3450.
8. Click in cell B4, here we will create a formula and deliberately create a circular reference error.
9. In B4 type an equal sign (=) then type B1-B4 (we should be selecting B2 but we are deliberately making a mistake to create the circular error):

10. Press Enter.
11. The circular reference error will appear:

12. Click OK.
13. You’ll notice that Excel allows you to move past it, and doesn’t force you to correct it.
14. Cell B4 now displays the answer zero (0).
15. Save and close the file and then reopen it.
16. Every time you reopen the file, the circular reference error will appear and allow you to click OK to move past it.

Now the problem with the fact Excel allows you to move past the error, is that when you are working on larger files, you see the error but you don’t know WHERE the error is to begin to fix the problem. I see so many users open files for me and they breeze straight past the error message and ignore it completely. It doesn’t make the error go away and it means that something is not being calculated correctly.

How to find a circular reference error

Excel provides a simple way to quickly locate circular reference errors within a worksheet. It is part of the overall Error Checking options so it is just a matter of knowing where to find it.

1. Open the file containing the circular reference.
2. Click the Formulas tab from the Ribbon.
3. Click the Error Checking drop-down button:

4. Hover over the Circular References option to see a list of the cell(s) which contain a circular reference.
5. Click the cell reference and the cursor will be moved to that location.

Fix a circular reference error

Now that you know what a circular reference error is and how to locate it, now you need to fix it. The example of a circular reference error created in the first part of this activity is a very simple example where we deliberately made a mistake to demonstrate.

To fix this example:

1. Click back on cell B4 which contains the error.
2. Double click the cell reference to edit the formula, or press F2 on the keyboard.
3. Edit the formula to be =B1-B2
4. The error is now corrected.
5. Go back to Formulas > Error Checking and you’ll notice that Circular References is greyed out as there are no more found in the workbook.

I hope this gives you a good idea of what a circular reference error in Excel is and how to find and resolve this error. It’s important to acknowledge errors as they occur in your Excel file and address them as they arise so that you have confidence in the accuracy of the information that Excel is providing. Feel free to comment any questions below.