You may not be familiar with a circular reference in Excel but odds are you will eventually come across one, usually by accident.
What is a circular reference in Excel? It might be easier to think about what a circular reference in real life might be. Imagine that you ask two of your friends, Alex and Riley, what their favorite breakfast cereal is. Alex responds, “The same as Riley”. Then when you ask the same question to Riley, she responds, “The same as Alex”.
Technically both people have answered the question but you still don’t have an answer because both answers are dependent on each other. This is the same thing that causes a circular reference in Excel.
Example of a circular reference in Excel
You can intentionally create a circular reference in Excel by doing the following. Open a new spreadsheet and put the following formula in cell A1:
Cell A1: =B1
Now, in cell B1, put the following formula:
Cell B1: =A1
As soon as you do this, Excel will pop up the following error message:
Excel is informing you that you have created a circular reference and that you should fix it to avoid calculation errors in your spreadsheet.
Check out our Excel Lesson Videos for how-to Excel video tutorials.
Circular reference using the SUM formula
Here’s another example of a circular reference in Excel using the SUM formula. Suppose you have a spreadsheet with numbers in cells A1, B1, C1 and D1.
You want to sum the values in cells A1:D1 in cell E1 but when you build your formula, you accidentally include cell E1 in the sum such that the formula in cell E1 looks like this:
Cell E1: =SUM(A1:E1)
Excel will flag this as a circular reference because you are asking Excel to include cell E1 in the SUM formula, which is the same cell you are using the SUM formula.
Fix circular references in Excel
If Excel displays an error message warning you of a circular reference, you should fix it immediately. Unresolved circular references will cause unpredictable and inaccurate results in your spreadsheet.