Excel Formulae, Tips and Tricks - #4 (Circular References)

Do you ever open an Excel file and see a warning that it contains one or more circular references?

What is a circular reference?

Circular references are created when a cell is populated with a formula which either directly or indirectly refers back to the same cell.

Indirect reference example:

Cell A1 contains "=B1+C1"

Cell B1 contains "=A1+C1"

Direct reference example:

Cell A100 contains "=SUM(A1:A200)"

Why should they be avoided?

I regularly see cases where circular references have been created accidentally, and are then ignored despite Excel constantly providing warnings of their existence - the main reason usually being that the cell/s containing the circular references are only used for 'workings' or checks, and so don't impact the actual output of the sheet.

While this may seem fine if (and only if) the output is not affected, the important thing to note is that circular references can significantly slow down Excel's calculation process. When working with a small simple workbook, this may not be noticeable, but if your workbook is more complex, the speed reduction can be frustrating. In addition, even if the workbook you're actively working in doesn't contain circular references, if you have another workbook open which does, it will still impact Excel's speed.

One example I recently encountered was running a macro-based process in a workbook which normally takes around 15 seconds to complete an export of some data. However, due to having another workbook open which contained a circular reference, the same macro process took over 15 minutes.

Therefore, if your workbook contains unneeded circular references, please take the time to remove/correct them - it will save you (and potentially others) time and stress in the future!

How can I tell if I have circular references?

As well as the warnings provided when opening a workbook, you can also check the status bar at the bottom of Excel, which will show 'Circular References' if any exist in your open workbooks (the status will show in all workbooks, not just the one containing them).

What should I do?

Unless you have one of the very few genuine reasons to use circular references, remove them as quickly as possible to save you from painfully slow workbooks! To quickly locate circular references, use the 'Error Checking' tool in the 'Formulas' ribbon, which handily points you directly to where they are hiding.

More information on circular references can be found here: https://support.office.com/en-gb/article/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123