How to Find Circular References in ExcelJun 25, 2021
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%. 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!
How to Find Circular References
-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. You have found your circular reference! Now delete this cell's formula by clicking anywhere inside its box, then pressing Delete from your keyboard!
Circular references are common. They often happen when you aren't paying attention or have been working too long and get your 'wires crossed.'
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
print('Hello' + name)
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.
Solutions for Circular References
- 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
-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.
81% of companies across the use Microsoft Excel.
Mastering Excel can increase your earnings up to 4x over the course of your career!
Bottom Line: It pays to master Excel. Take our world-class Excel University and take your spreadsheet skills to the next level.
Get 5 FREE Excel Templates and Trainings
What email do you want us to send your access to?
We hate SPAM. We will never sell your information, for any reason.