Buy Now
Circular references can be a nuisance. Learn how to spot them with this blogpost.

How to Find Circular References in Excel

circular references excel errors excel tips Jun 25, 2021

How to Find Circular References In Excel

How many times have you been working in Excel, and then realized that something is wrong because the data doesn't make sense? For example, you might have two columns of numbers that are adding up to more than 100%. Oftentimes, you will get circular reference warning but have no idea what it means. This happens when there is a circular reference somewhere in your spreadsheet. Fortunately, it's easy to find these errors--and this blog post will show you how!

What are Circular References?

Circular References are a common error in Microsoft Excel, especially when you're dealing with really long and complex functions and formulas. A circular reference occurs whenever the cell reference in a formula you placed inside a workbook either becomes a direct circular reference, or an indirect circular reference. When the Excel sheet runs the formula, it does so in an endless loop. The error checking function built in your Excel sheet not only helps stop your computer from eating itself, but it also makes it easy to find circular references in your worksheet.

In this example, the formula in Cell Address D3 directly references itself. This is also aptly called direct circular reference. This type of error isn't too common among seasoned Excel users, but anyone can fall victim to it regardless.

The second kind of circular reference is called an indirect circular reference. We can see in this example that we referenced the correct cell, no more of that referencing its own cell nonsense, but it returns the wrong answer still. This is because cell address F1 contains a cell reference that references cell address D3.

Effectively, the cell address D3 formula refers to itself indirectly, causing you to create an indirect circular reference. We can see how this can pose as a problem because it alters the results of your computations which could have a huge impact depending on the kind of work that you do. You only get to see the circular reference warning message window on the first instance of committing a circular reference error. So how can we identify, fix, and prevent circular references in Microsoft Excel?

Examples of Circular References

-A cell referencing itself. In this case the formula =SUM(B12) would be in B11 and the result of that calculation is added back to B12, making a loop.

-Two cells with formulas that reference each other (a 'loop'). These often happen when you copy a range of cells then paste them into another location on your spreadsheet - two rows will end up pasting into one row, but they still have their original relationships so it looks like there are loops. For example SUM(D45) in D44 will sum values from column C only if total sales for quarter Q are greater than or equal to 500000! A similar situation happens when copying columns. For example, if you have a column of unique values and copy it then paste to another area on your spreadsheet, the formulas in cells A11:A30 will not work as expected because they will be referencing different rows.

If you program in Python, you may also experience circular references.

This is a common problem if you have two variables that reference each other, such as

def hello(name):

print('Hello' + name)

hello("Bob")

In this case the function will never return because it prints out "Hello" and then does not exit. The variable name always points to itself so there is no way for the program flow to continue. Circular references are very similar in Excel - we need to find them before they cause big problems!

- How do circular references happen? When copying rows or columns of data from one area on your spreadsheet into another, some cells might end up referencing themselves (rather than any external cell).

Here are some examples of how circular references can have real-life implications with your spreadsheets.

1) We have a budget of $1000

In this case, the budget will always be $1000 because it's referencing itself. The total amount spent will never change and so your spreadsheet is inaccurate.

2) When transferring funds between accounts in Quicken, if there are any cells with circular references on either side of the transfer (cells that refer to each other), you'll get an error message saying "Cannot find referent for cell X". This means that these cells cannot both show new data without breaking their own connection to themselves. In order to avoid errors when transferring funds in Quicken or Excel: keep track of which rows/columns contain circular references and don't try to edit or enter data in those cells.

How to Find Circular References

Finding Circular References the Old School (Hard) Way

This method would involve a bit of the hackerman in you. You can use this method if you're trying to look for circular references in an older version of Microsoft Excel.

-Select your data, click on the "Formulas" tab and press F12 to open the Visual Basic Editor.

-On the left side of the screen you'll see a list of all Excel formulas that are currently being used in your spreadsheet.

-Look for any cells with an asterisk (*) next to them - these represent circular references (cells dependent upon each other), so they will show when viewing relationships between cells.

-Click on one of those cell names and follow it back until it stops at another formula or column header name. This is the endless loop we were talking about. You have found your circular reference! Now delete this cell's formula by clicking anywhere inside its box, then pressing Delete from your keyboard!

Find Circular References in Excel the Easy Way

If you're running a newer version of Excel such as the Office 365 version, you're equipped with an arsenal of tools that make it easy to find circular references in Excel. These built-in tools make finding circular references in Excel too easy.

Right in Your Face

On the first instance of committing a circular reference error, you will get a circular reference warning message. Microsoft Excel's error checking utilities are always on the lookout for anytime you're probably ruining your spreadsheet. So when you commit a circular reference, Excel displays this error message. Once you see this, you can correct your mistake or, depending on how you want to structure your Excel workbook, opt towards enabling iterative calculation.

What's Your Status?

Just like your favorite social media platforms, Excel also has a status bar! The status bar is located at the bottom of your Excel Workbook.

The status bar in Excel displays a few important details such as the workbook status, if you've got some cells selected, you also get the average, count, and sum, and most importantly, it tells you that you have circular references present in the workbook and it even tells you the cell address where the circular reference is located. Just note that the status bar only shows one circular reference per sheet. In our simple example, only one circular reference is present. Even for matters other than circular references, you can use the status bar for a wide range of uses.

The Formulas Tab is the Secret to Successful Calculation

Depending on the nature of your work, you may or may not frequent the Formulas tab. If you need help with your circular reference, or any error for that matter, who you gonna call? The Formulas tab! It's here where we can see the bulk of error checking tools that Excel offers in the Formula Auditing Group.

The first error checking tool in the Formula Auditing Group that is probably the most useful is... well... the Error Checking tool!

You can easily click on it, and in the drop down menu, select Circular References and is shows you the which cells contain circular references. Error Checking can also do a bunch of other cool tricks like check the entire worksheet for errors, and if you've got a selected cell address that you know has an error, you can trace the error. You can do this for all the circular references found in your Excel Workbook. This will also cover the hidden circular references in your Excel Workbook

Tracing Precedents

Another cool tool you can find in the Formulas tab > Formula Auditing Group is the Trace Precedents tool. As the name suggests, this tool traces the different cells that precede or make up the values in a cell.

We simply select the cell address in question, then hit the Trace Precedents button. The Trace Precedents tool adds arrows to that point towards and away from the selected cell. The circles show the source and the arrows point to where they are directed to. In this example, we can visibly see the circular reference . We can see in cell C3 that it's pointing towards cell A3, but at the same time, we can see that cell A3 is also pointing towards cell C3. If we inspect cell C3 and check the formula bar, we'll see that cell C3 referenced cell A3 which is why there is a circular reference.

In this simple example as well, we can see how circular references can cause you to calculate incorrectly, which is always a bad thing.

Trace Dependents

Trace Dependents does what Trace Precedent does but backwards. While Trace Precedents let's you know which cells precede the computations in that cell so you can investigate the different inputs to that formula, Trace Dependents let you know which formulas or cells are depending on the cell/cells you selected.

In the above example, when we select cell A3 and click Trace Dependents, we can see which cells are depending on cell A3. In this case, we can see that cell C1 is dependent on cell A3 and therefore would return a circular reference should we reference cell C3 later on.

Trace Precedents and Trace Dependents make spotting circular references easy whether they're a direct circular reference or an indirect circular reference because of its visual nature.

Take it Step-by-Step

The last useful tool you can use in the Formula Auditing Group to help spot circular references is the Evaluate Formula tool. You can select a cell that contains the formula, and it gives you a play-by-play analysis of the the formula in question, and you can do this to all the formulas in the workbook.

How To Remove Circular References

Now that we know how to find a circular reference in Excel , how do we deal with it? Unless you've got iterative calculations enabled on purpose, you might be inclined to not want to have a single circular reference issue in your sheet because it leads to calculate incorrectly.

Generally, the only way to remove a circular reference is to fix the structure of your formulas. This is intrinsically a design error that should be more something that is prevented than cured.

Due Diligence is Key

Avoid circular references when possible - this helps you avoid errors because Excel will find any mistakes and alert you if there are any that it can detect. The status bar displays any pertinent details an many more without you even asking.

Be mindful that your mouse pointer picks up cell references when you're typing in formulas. It's common to commit a direct circular reference error when typing in formulas using your mouse. Don't fret though because as mentioned, a direct circular reference is the easier to detect than the indirect circular reference. Just remember to not reference the first cell by its own cell to avoid a direct circular reference.

Have a clear idea of how you want your Excel Function to be structured. This will take some getting used to but it yields dividends specially when you start working with large spreadsheets and formulas. You can even draw the function out if you have to. This helps you avoid committing an indirect circular reference error.

Create a table of contents with headers for each part of your spreadsheet; these headers should be text not numbers, so the user knows what they're looking at without having to scan through all the rows/columns on their screen

Use cell referencing instead of row/column references where appropriate (i.e., use A12 rather than B14) - this is helpful for users who may have different sized monitors as well as making sure your right and left hand columns line up.

Now that you know how to handle circular references, let's help you with your spreadsheets! Want more great tips on conquering Excel? Check out our Excel University or read more about How to Show Changes or How to Compare Two Columns in Excel

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.