A round-up on Circular References

Posted on September 16th, 2010 in Learn Excel - 41 comments

Excel Circular References - What are they, How to use them, Examples & Dealing with Circular ReferencesHere is a little experiment to freak out excel.

Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.

Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.

What is a Circular Reference & why use them?

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

We use circular references when we need circular references.

Excel Circular Reference Example:

For eg. (borrowed from John Walkenbach’s Excel 2010 Bible), lets 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)

Excel Circular References - an example

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:

Excel Circular References in Formulas - an example

But wait, just when you press enter after writing the formulas, Excel would scream bloody and curse your entire family 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 per-historic 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 avoid Circular References?

As you can understand circular references are a pain in cell. You may want to get rid of them altogether. Thankfully, with careful inspection and a mug of coffee, you can reduce most circular references to simple formulas. For eg, in the above case, we can calculate charity amount directly by using the following equations.
Remove or Avoid Circular References using Better Formulas

But, keep in mind that, in few cases, circular references may be required. For eg. if you want to add timestamps to your workbook.

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.

Locate Circular References in an Excel Sheet

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

Examples & More Resources on Circular References:

Do you do circular references?

I try to avoid circular references whenever possible. But in some rare cases, I think a circular reference gives elegant, shorter solution than a non-circular variation of it.

What about you? Do you use circular references often? What are the reasons / uses of them according to you? Please share your experience, tips thru comments.

PS: Here is a very useful link on circular references.

PPS: Monalisa pic source is here.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

41 Responses to “A round-up on Circular References”

  1. Rich says:

    Oooo, love how you slipped that title in there: “ROUND up on CIRCULAR references”!

    The most annoying thing about them is how Excel activates the help feature, so before you can get to fixing the error, you have to waste time getting rid of the pop-up.

    On that, is there a way to deactivate the F1 key? It is so closely located to the much used F2 key, it is the bane of my excel existence…

  2. as an alternative, consider Goal Seek.
    The risk with circular references is that Excel only warns you once so an intentional one can mask an unintentional error circ ref.

  3. Tom says:

    Good article – thanks!

  4. Steve @ Jet Reports says:

    Chandoo –
    Not only are you awesome, but you’re a mind reader. I was unsuccessfully trying to determine the cause of a circular reference when your e-mail came in. (It was on a report that I had open in the background. I wasn’t even looking there til I got your e-mail).

    Thanks much!

  5. Lucasini says:

    Circular references are one of the most powerful but underated features of Excel. Circular references are one way to implement loops in this app (FOR, FOR NEXT , WHILE, DO WHILE), so they are great to design complex models that otherwise would be impossible to write in Excel without VBA. BUT you have to make some considerations: for example, circular references are solved in sequence, so your formulas locations ARE important.

    Greetings from Panamá,
    SE HABLA ESPAÑOL Y EXCEL!!!!!!!!!!!!!

  6. Glen says:

    Very nice circular reference on the “PS”. How many iterations are on the link?

  7. Mynda says:

    If nothing else Chandoo you make me laugh (out loud….ok so I’m an Excel nerd too).

    For a topic know for it’s popularity with bean counters, who are ‘boring’, as we all know, you are clearly breaking the mould.

    Thank you,

    Mynda
    Head Bean Counter

  8. Chandoo says:

    @Steve… Welcome, I was expecting your comment ;)

    @Glen: It should stop at 100.

    @Mynda: I am glad you liked it…

    @Rich… very good point. The help on circular refs always annoys me.

    @Patrick: I agree, goal seek is much more elegant and simple. But there are some places where you just have to use Circular refs.

    @Lucasini: Greetings and thanks for your point on “circular refs are most powerful …”. I agree. They are like recursion in programming. You need to know how to use it, then you can do wonders with it.

  9. Ken says:

    I come from an era where a circular reference was a big problem – very hard to find and no way to control the re-iterations so I avoid them and figure out a formula to get a result. Likewise in VBA I cannot hack the deliberate use of “on error” just because MS doesn’t include better interogative functions. In my view its just poor programming practice. “on error” should really only be used so the operator can retreat as gracefully as possibly!

  10. [...] order to get this animation, you should be familiar with two excel magic spells – Circular References & Iterative Calculations. In simple [...]

  11. mimit says:

    thanks alot u realy help me

  12. Ng Chun Lee says:

    Hi Chandoo,

    Can you give a more coverage on circular reference in your Excelschool training?

  13. Tanuja says:

    Can you include me in your blog post, please? I am in Analytics and find all the articles very useful.

  14. Royd says:

    This is a help, really.
    I am trying to round a number entered into a cell to x significant digits. This entails circular reference. The cell is in a data entry form and I don’t want to add the data value into a secondary cell and have the function calculated into the primary cell (which does work by the way). I haven’t found a workaround to this yet.
    Thanks.

  15. Hui... says:

    @Royd
    Have you tried =Round(cell Ref, No Digits)
    eg: =Round(A2, 2) round the contents of A2 to 2 digits

  16. Royd says:

    Thank for the response Hui.
    My problem is centered around circular reference as I want to enter a value into a cell that has an equation defining the number of significant digits, within that cell. The Round function works fine if the cell is referenced from a secondary cell. As I want to build this into a data input form, I don’t want extraneous columns of data, if I can help it.

  17. Hui... says:

    @Royd
    It can be done with a little VBA
    .
    Copy the following code into a Page Module in VBA
    Alt F11 – Enter the VBA Editor
    Double click the page you want to use
    Paste the code in the right hand pane
    .
    = = = = =
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then 'Change Range to suit
    Application.EnableEvents = False
    Range("A1").Formula = Range("A1").Value * 100 'Change Formula to suit your needs
    End If
    Application.EnableEvents = True
    End Sub

    .
    = = = = =
    Change Range to suit

  18. shaibu says:

    thanx,so helpful

  19. Royd says:

    Thanks Hui. That’s great.

  20. Evan says:

    Very Helpful!

  21. CORNELIA says:

    Hi, i need to round a circular answer, please help

  22. Cornelia says:

    I Want to create a formula for my pricelist, every time the prices goes up a certain %, I just want to edit the % sell, then my entire spread sheet needs to change accordingly. I have created a circular formula, but i am having trouble figuring out the round? i tried naming my circular sell, but even that creates ####### problems :-(

  23. Hui... says:

    Instead of trying to use circular references why not just setup a list of price increases
    Say in Column A2:A100 add the price increases as numbers
    say 5, 8, 2 as 5%, 8%, 2% etc
    Then use a simple array formula
    =Original Price * PRODUCT((1+(A2:A100)/100)) Ctrl Shift Enter
    You can now add prices rises as % to the list in Column A and the cumulative price rise will be calculated by the above formula

  24. Cornelia says:

    Thank you very much Hui
    but i know very little about exel and i dont understand what you are telling me. i copied and pasted a part of my spread sheet. allow me to explain to you what i have done so far. 0.05 is in I5, 109.73 is in G7. this is the price after i have worked out the 5% increase. the formula i used goes like this (104.50)*(1+I5) the answer i got was 109.73. but i need to round it of to the nearest 5. say 109.75
    the formula i used before this one worked out perfect but instead of 0.05 in sel I5 i had 5%. the formula i used was (140.50)+round(I5*G9,1.05) but if i take 104.50 and add 5% it gives me 110.00 instead of the desired 109.73, which is closer to the amount.

    0.05
    PER BOX: EXCL. VAT: EFFECTIVE DATE: DIST. DISC:
    40 R 109.73 2011/06/13 -25%

    25 180.80 2011/06/13 -25%

  25. Dannyblr says:

    I am using the circular reference in shared workbook to get the start time and end time automatically. The problem is that the timestamp gets updated when the another user saves their data and my data(both the start & end time) changes to the saving time and difference of it gives zero.

    Please help on me this issue.

  26. Sonia says:

    Actually I require the circular reference formula in my sheet. But this sheet goes to the client as well and they get scared to see the circular reference warning. Is there a way, apart from the iteration tick thing, may be some code in VBA that can help me subside this warning sign?

  27. Casey Combs says:

    I am in a new job that uses timecards for teachers that have references in additional pages of the worksheet.  When I try to add cells for new students I get the circular reference.  I don’t want to change the worksheet as it has been working well for the company so far.  I just want to be able to add or subtract cells as needed.  Is there a way?  Thanks, Casey Combs

  28. Sanjeev Kumar says:

    First of all thank you for this freely available lesson (though I came very late onto this).

    But, the manual/normal arithmetic formula seems to be wrong. Acc to this very example, the formula gives the charity to be 13.08. While actually it will come to some/around 14.43. I think that is a BIG difference.

    Hope you’ll clear.

    P.S. Also, the 2nd line of formula (which shows the step: Pat= 7* (R-E-C/7) is very difficult to digest unless we tell the readers one fundamental equation (i.e. Profit after tax= Profit*(1-30% of Profit)

  29. Spar says:

    A very interesting and intelligent thread. Unfortunately Excel thinks we are idiots, because every time I generate a circular reference (usually from typo) Excel launches ‘Help’ whether I need it or not.

    Is there a fix? Or do I just have to live with it, as I must with so much of Excel’s poor interface design.

    • sanjeev kumar says:

      Everybody experience same problems.

      But Microsoft rarely listens.

      Actually they have to force all their energy (and the money earned from people like us) to slander, fight and (try to) outdo Google!

  30. Ben Helvensteijn says:

    What I found is that to get the recursive calling process going, Excel first needs to be fooled into thinking it has an actual value to work with. Example:
    In cell A1 type: 10 (gives the cell that is going to call A1 a working value)
    In cell A2 type: = 1 + 1/A1
    Go back to cell A1 and type: = A2
    Go to Excel Preferences and under the Formulas and Lists heading find “Calculator”. Then within Calculate Sheets select “Automatically”, and within “Iteration” select “Limit Iteration” then add your preferred number of iterations (use a low number for starters to see if it’s actually working as expected) and step size (modestly large helps at first).
    Hit OK (Exit Preferences) and calculations should already have happened.
    Hit Ctrl= to gain the results after another set of iterations.

    The main aid is to initially give the intended-to-be-recursive call an actual value, then set up all else, and in the end go back and change that number to become a recursive call.

  31. Jeffrey Smith says:

    Under your Examples and More Resources, there is a bad link for:

    Solve Circular References instead of using them

    Jeff

  32. Maddy says:

    Hi Chandu,

    I am not able to execute Circular Referencing through the advised method. Nothing happens when I click on checkbox for Iterative Calculations. Please advise.

  33. Joanna says:

    HELP!!
    Feeling very frustrated. Is there not a way to format a cell to immediately multiply by a number?
    I am trying to calculate the rebate of a total spend.
    I would like to enter a total spend in the cell and have the cell immediately calculate 10 percent of that number. This information is being entered manually each month.
    Any suggestions??!!

    • Hui... says:

      @Joanna
      Unfortunately in Excel you can’t enter formulas into a cell and calculate the result in the same cell without using VBA
      You can enter 100 in say B2 and in C2 have a formula =B2*90% and C2 will then display 90

Leave a Reply