How to Find and Handle Circular Reference in Excel

So I know it is not just me. I mean it cannot simply be only me making that mistake so often.

Frankly, it is even too complex a mistake to make. Beauty in chaos kind of.

I’d understand someone doing that while calculating the mass of the sun, but me? No, it shouldn’t be happening to me trying to split expenses with my room-mates. But it does.

So today we shall be looking at techniques to resolve and avoid this type of mess as often as possible.

Circular Reference in Excel Img1

The problem

Circular referencing is an iterative calculation in excel; in other words, an infinite loop.

When a formula in a cell is capable of altering its own contents directly or indirectly a case of circular referencing arises.

Allow me to demonstrate what we unfortunately only get by chance.

circular-reference-in-excel-img2

[Click on the image to get a clearer view]

The resolution

Ok, it was way too easy to make that mistake. No worries let me up things a little.

I get 500$ a month from home for expenses. So I go deposit it in the bank. At the end of each month, the bank gives me a 10% rate (yep I know awesome bank right) on the monthly average in my account. I use my handy dandy excel to predict what I’ll make in year 2016.

circular-reference-in-excel-img3

My excel displays the circular reference warning and loses it.

When the machine gets confused we are bound to confuse our self with it , cause even something mechanical is confounded how dare we in flesh and blood.

So don’t panic in such a situation. It is not unbreakable.

Breaking bad

To think about this clearly if this formula is correct and I want to calculate the amount I will receive in 12 months; this formula should be executed 12 times. So the loop as it is should have 12 iterations.

We don’t necessarily need to resolve the looping situation.

Excel has a nifty way of letting us do that or working around the situation.

Master Excel Formulas & Functions in Just 3.5 Hours!

with my FREE COURSE at Udemy.

Excel Formulas and Functions with Excel Formulas Cheat Sheet!

We can go about and define after how many iterations should it stop. So 12 yes I know that where should I put it.

It is easier than finding Narnia

Here is the map.

File -> Options -> Formulas

circular-reference-in-excel-img4

[Click on the image to get a clearer view]

So I wanted to know what it will the sum look like after 12 iterations.

Now since this will give me the final amount I’ll change my captions a bit.

When you hit OK; the formula will run 12 times and the values you see of average and interest earned will be what you will get AFTER 12 months.

circular-reference-in-excel-img5

So yay my money makes money.

I love how banks work.

And now you love how Excel works.

But be vary whichever calculations you do now will iterate 12 times. So don’t forget to uncheck the box in options and disable looping. Or you can change the number of iterations for the next calculations after obviously saving previous work.

If the excel file is too complex and you cannot figure out which cell is the trouble you can always use this:

circular-reference-in-excel-img6

[Click on the image to get a clearer view]

Go to Formulas option in ribbon and click on circular referencing. The drop down will display all the rogue cells.

First World Problems

Let us look at another problem to understand the power of circular referencing using it for our benefit.

I was doing expenses for my boss and he flies around like a buzzing bee.

This time, he embarked on a conference and merger meetings with various companies in the US.

His private jet took this route: Houston–Los Angeles–Seattle–Minneapolis– Houston.

Starting at Houston the plane started with 1000 gallons of fuel.

The fuel used on each part of the journey 40-0.2(average fuel)

Houston-Los Angeles

So I wanted to know what the average was on each journey which would be (initial fuel + final fuel)/2

This average would help me calculate the fuel used as 40 – 0.2* (average from above)

Now this quantity had to be deducted at the end of the journey.

So the total fuel left would be calculated and the average would change.

Without circular referencing this problem would be too complicated to solve for the remaining 3 stops.

So I went ahead did the first calculation for it.

circular-reference-in-excel-img7

I had to ensure this was repeated 4 times. So I went to Narnia.

No I mean I went ahead and redefined iterations allowed for circular reference to 4.

circular-reference-in-excel-img8

[Click on the image to get a clearer view]

And yoo-hoo!!

With the trick, I saved time to another round of coffee at work and so can you.

circular-reference-in-excel-img9

Circular referencing, in short, is not an error at least for me. Ok, sometimes it is.

But mostly I think of it as an added feature.

What I would spend 20 minutes coding in VBA is an inbuilt feature.

I would have sat designing a very confusing For-Loop or whatever and then run it and re-run until it was error free. Excel is kind enough to do ALL the work and let me have the pay.

So can you!!

Download working files

Download the working file from the links below.

Boss-USA-Trip.xlsx

Calculating-Yearly-Interest.xlsx

HAPPY CIRCULAR REFERENCING FOLKS!!


Hi, my name is Priya and I am a VBA expert. I self-learned this while working to a telecom giant We used to have a lot of data in form of subscriber info services call etc. To handle the same efficiently I came across this very interesting and helpful feature about excel and other office products.

4 Comments
  1. Reply
    Janus Ickham September 8, 2016 at 9:15 PM

    I still don’t get it. Could you put in a downloadable excel spreadsheet?

    • Reply
      Kawser September 10, 2016 at 3:41 PM

      The working files are uploaded. Thanks!

  2. Reply
    Armen September 9, 2016 at 6:03 PM

    I didn’t get the formula behind cells, please attache a file to the post.

    • Reply
      Kawser September 10, 2016 at 3:41 PM

      Armen, files are uploaded. Please let me know whether the concept is clear to you or not!

    Leave a reply