Dealing With Circular References in Excel

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:

circular reference excel - sample 1

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)

circular reference excel - sample 2

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.

Comments are closed.

Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!