What is a Circular Reference & why use them?

A circular reference is created when you refer to same cell either directly or indirectly.

Excel Circular Reference example: let's say you run a fictitious company named Sky is the Ltd. And you have a strange policy of donation 10% of your profits after tax to charity. But, in your country, charity donations are tax exempt (they are expenses).

So charity = 10% * after tax profits


after tax profits = (revenues - expenses - charity) * (1 - tax rate)

By definition, charity refers to after tax profits, which refers to charity, thus creating a circular reference. Now, how would you find out how much to donate to charity? Simple, we write formulas with circular references, like this:

But wait, just when you press enter after writing the formulas, Excel would scream and curse you for having a circular reference in your worksheet. :-)

Enabling Iterative Calculation Mode

You must enable what they call iterative calculation mode before the formulas work. For this we must go to Excel Options.

In latest versions of Excel,enable Iterative Calculation mode to get Circular References work.

• Click on Office button.
• Go to Excel options, this is analogous to opening the bonnet of your car, but just a bit more confusing.
• Locate the “Formulas” on the left, click on it.
• Now, check the “Enable iterative calculation”. This way you are telling Excel to evaluate references iteratively, up to 100 times (default).
• Click ok, close the bonnet. That is all.

In previous versions of Excel:

• Go to Menu > Tools > Options > Calculation Tab.
• Check Iterative Calculation box. (see image)
• Once you do this, your formulas will work nicely and you will find that the required charity donation to be made.

How to locate Circular References?

Do you know that you can find all the circular references in a workbook?

Whenever you see circular reference warning message, just go to formula ribbon and click on error checking options. You can see all the circular references there.

Note: In Excel 2003, you can see the same from circular reference toolbar (Menu > View > Tool-bars > Circular Reference).