Productivity
Brought to you by

How To Deal With Circular References In Excel

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]