Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel tip: How to spot and use circular references

by
13th May 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

AccountingWEB’s technology editor-at-large David H Ringstrom delves into the hall of mirrors created by circular references in Excel - and then highlights situations where you might set them up on purpose.

In a recent article on our US sister site AccountingWEB.com, David Ringstrom explained what happens when users incorporate formulae with circular references in their spreadsheets.

In most situations when you create a formula, it will refer to a value in another cell. Circular references happen when the formula you create includes the cell where the formula resides.

But complex forecasting models will include circular references in several different cells - which can sometimes mean that Excel can’t resolve the circular reference. You can fix this by disabling the formulae and reenabling them once the formulae work.

Trying to track down all the circular references in a complex model is a nightmare, but one that Rinstrom solved when he discovered one of Excel’s hidden features. This tutorial sets out what he found.

To demonstrate the process, he created the example below with references  spanning across three worksheet tabs:

Circular reference between two Excel cells.

  • Sheet1: In cell A1 enter the words Building Cost and in cell B1 enter 100,000,000.
  • Sheet2: In cell A1 enter the words Developer Fee and in cell C1 enter 3%. Leave cell B1 blank for the moment.
  • Sheet3: In cell A1 enter the words Total Building Cost and in cell B1 enter this formula: =SUM(Sheet1:Sheet2!B1)
    • This creates what is known as a drill-through formula, where Excel drills through two or more worksheets and sums the same cell or cells on each sheet.
    • This sets the stage for our circular reference, so back in Sheet2 enter this formula in cell B1: =Sheet3!B1*C1
    • When you press Enter, depending upon your version of Excel, any number of prompts and indications may appear on the screen.

To enable circular references, turn on Excel’s Iterative Calculation option:

  • Excel 2007 and later: Choose File, Options, Formulas, and then click Enable Iterative Calculations, as illustrated below.
  • Excel 2003 and earlier: Choose File, Properties, Calculation, and then click Iteration.
  • Excel 2011 for Mac: Click on the Excel Menu, choose Preferences, Calculations, and then click Limit Iteration.

How to enable Iterative Calculations.
 

This sequence should resolve the formula, so that cell B1 of Sheet3 returns $103,092,784.

But what would happen if the circular reference involves dozens of cells, scattered across numerous worksheets? How do you find the starting point of a circle? To save you infinite amounts of time trying to untangle that riddle, Excel 2007 and later versions include a facility to find them. Here’s how:

  • Choose Formulas.
  • Click the arrow next to Error Checking.
  • Choose Circular References.

As shown below, the Circular Reference Command is disabled if the Iterative Calculation option is turned on.

How to disable Excel's Circular References command

If you follow the steps above to Iterative Calculations, the Circular References menu command will then display a map of the cells that are linked to each other in a circular fashion (see below).

Once you have this information you can tweak the formula to make it non-circular, or place an apostrophe in front of certain cells to disable the formulas. Doing this will stop Excel from returning #VALUE! Where it encounters the circular formulae.

Once you’ve broken the cycle, you can enable each formula again to restore the circular references if needed.

Circular References menu option displays the cells that are related to each other

Further reading

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.