How To Deal With Circular References In Excel

How To Deal With Circular References In Excel
To sign up for our daily newsletter covering the latest news, hacks and reviews, head HERE. For a running feed of all our stories, follow us on Twitter HERE. Or you can bookmark the Lifehacker Australia homepage to visit whenever you need a fix.

Circular references — formulas in a spreadsheet cell which refer to themselves — can be tricky to resolve. The Excel blog reminds us that there are a couple of ways to deal with them.

If you enter a circular formula — such as =A2+1 into cell A2 — Excel presents you with this error message seen above. Clicking OK will keep the existing formula and let you edit it, which is the quickest method if it’s an obviously self-referential cell like the example we gave.

However, if the reference is more complicated, the best option is to access the Error Checking option on the Formula tab. Hit the link for more details on how to use it. One warning: the feature isn’t include in the Starter version of Excel. However, that will show the cell number of the first circular reference in the status bar.

Circular References: Excel’s most popular (!#?%) error message explained [Excel Blog]

Log in to comment on this story!