Identifying circular references in Excel

I don't know about you, but every now an again, I manage to create a circular reference in Excel. Trying to get rid of one can do your head in.

We might have included the sum cell in a sum range. We might have a cell in our range that refers to another formula. We might have simply selected the wrong range, the wrong starting place or have a sneaky connection to another sheet.

However, it you can remember, when that annoying message pops up: there is an easier way, as follows:

1. Save the troublesome workbook
2. Create a copy
3. Go to the Formulas tab on the ribbon
4. Click the arrow next to Error Checking, on the dropdown, select Circular References
5. Side out to the first circular reference cell listed in slide out menu
6. Review that formula
7. If that does not remove the problem, tackle the next one on the list
8. Work through until the error stops
9. Save and close the workbook copy
10. Reopen the workbook copy and see if the error recurs
11. Make the same repair(s) to the original workbook.

Actually, I would recommend that, if there is more than one circular reference, that we tidy them all up while we are doing this.

I hope that works for you!


Sam

• Reference: Microsoft Support (2020). Remove or allow a circular reference. https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123