Dec
15

Circular reference in Excel - how to check, find, enable, or remove

This short tutorial explains the basics of Excel circular reference and why you should beware of using them. You will also learn how to check, find and remove circular references in Excel worksheets, and if none of the above is an option, how to enable and use circular formulas.

Circular references in Excel
You've tried to enter some formula in your Excel sheet, but for some reason it's not working. Instead, it tells you something about a circular reference. Is this how you ended up on this page? :)

Thousands of users are facing the same problem on a daily basis simply because of forcing an Excel formula to calculate its own cell. When you try to do this, Excel throws up the following error message:

"Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly."

To put it simply, what Excel is trying to say is this: "Hey, I may get stuck at the round-about. Are you sure you want me to proceed anyway?"

As you can understand, circular references in Excel are troublesome, and the common sense says to avoid them whenever possible. However, there may be some rare cases when an Excel circular reference is the only possible solution for the task you are faced with.

What is a circular reference in Excel?

Here is a very straight and concise definition of a circular reference provided by Microsoft:

"When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference."

For instance, if you select cell A1 and type =A1 in it, this would create an Excel circular reference. Entering any other formula or calculation referring to A1 would have the same affect, e.g. =A1*5 or =IF(A1=1, "OK").

As soon as you hit Enter to complete such a formula, you'll get the following warning message:
An Excel circular reference warning message

Why does Microsoft Excel give you a heads-up? Because Excel circular references can iterate indefinitely creating an endless loop, thus significantly slowing down the workbook calculations.

Once you got the above warning, you can click Help for more information, or close the message window by clicking either OK or the cross button. When you close the message window, Excel displays either a zero (0) or the last calculated value in the cell. Yep, in some cases, a formula with a circular reference can complete successfully before it tries to calculate itself, and when that happens, Microsoft Excel returns the value from the last successful calculation.

Note. In many cases, when you enter more than one formula with a circular reference, Excel doesn't display the warning message repeatedly.

But why would anyone want to make such a stupid formula that does nothing but cause unnecessary problems? Right, no sane user would ever want to intentionally input a circular formula like the above one. However, you may create a circular reference in your Excel sheet accidentally, and here's a very common scenario.

Supposing you want to add up values in column A with a usual SUM formula, and when doing this you inadvertently include the total cell itself (B6 in this example).

If circular references are not allowed in your Excel (and they are turned off by default), you will see an error message we've discussed a moment ago. If iterative calculations are turned on, then your circular formula will return 0 like in the following screenshot:
An accidental circular formula in Excel

In some cases, one or more blue arrows can also appear in your spreadsheet all of a sudden, so you might think your Excel has gone mad and is about to crash.
The trace arrows might show up after entering a circular formula.

In fact, those arrows are nothing more than Trace Precedents or Trace Dependents, which indicate which cells affect or are affected by the active cell. We will discuss how you can show and hide these arrows a bit later.

By now, you might have an impression that Excel circular references are a worthless and dangerous thing, and may wonder why Excel has not banned them altogether. As already mentioned, there are some very rare cases when using a circular reference in Excel can be justified because it provides a shorter and more elegant solution, if not the only possible one. The following example demonstrates such a formula.

Using an Excel circular reference - formula example

In one of our previous tutorials, we discussed how to insert today's date in Excel. And an overwhelming majority of questions posted in comments were about how to enter a timestamp in Excel without it changing every time the worksheet is reopened or recalculated. I was very hesitant to reply to those comments because the only solution I know involves circular references, and they should be treated with care. Anyway, here is a very common scenario...

Supposing you have a list of items in column A, and you enter the delivery status in column B. As soon as you type "Yes" in column B, you want the current date and time to be automatically inserted in the same row in column C as a static unchangeable timestamp.

Using a trivial NOW() formula is not an option because this Excel function is volatile, meaning that it updates its value every time the worksheets is re-opened or recalculated. A possible solution is using nested IF functions with a circular reference in the second IF:

=IF(B2="yes", IF(C2="" ,NOW(), C2), "")

Where B2 is the delivery status, and C2 is the cell where you want a timestamp to appear.
A circular formula to insert a timestamp in Excel

In the above formula, the first IF function checks cell B2 for "Yes" (or any other text you supply to the formula), and if the specified text is there, it runs the second IF, otherwise returns an empty string. And the second IF function is a circular formula that fetches the current day and time if C2 doesn't already have a value in it, thus saving all existing time stamps.

Note. For this Excel circular formula to work, you should allow iterative calculations in your worksheet, and this is exactly what we are going to discuss next.

How to enable / disable circular references in Excel

As noted earlier, iterative calculations are usually turned off in Excel be default (in this context, iteration is the repeated recalculation until a specific numeric condition is met). For circular formulas to work, you must enable iterative calculations in your Excel workbook.

In Excel 2016, Excel 2013, and Excel 2010, click File > Options, go to Formulas, and select the Enable iterative calculation check box under the Calculation options section.
Enabling circular references in Excel

In Excel 2007, click Office button > Excel options > Formulas > Iteration area.

In Excel 2003 and earlier, the Iterative Calculation option resides under Menu > Tools > Options > Calculation tab.

When you turn on iterative calculations, you must specify the following two options:

  • Maximum Iterations box - specifies how many times the formula should recalculate. The higher the number of iterations, the more time the calculation takes.
  • Maximum Change box - specifies the maximum change between calculation results. The smaller the number, the more accurate result you get and the more time Excel takes to calculate the worksheet.

The default settings are 100 for Maximum Iterations, and 0.001 for Maximum Change. What it means is that Microsoft Excel will stop calculating your circular formula after 100 iterations or after the circular reference's values change by less than 0.001 between iterations, whichever comes first.

Why you should avoid using circular references in Excel

As you already know, using circular references in Excel is a slippery and not recommended approach. Apart from performance issues and a warning message displayed on every opening of a workbook (unless iterative calculations are on), circular references can lead to a number of other issues, which are not immediately apparent.

For example, if you select a cell with a circular reference, and then accidentally switch to the formula editing mode (either by pressing F2 or double-clicking the cell), and then you press Enter without making any changes to the formula, it will return zero.

So, here's a word of advice from many respected Excel gurus - try to avoid circular references in your sheets whenever possible.

How to find circular references in Excel

To check your Excel workbook for circular references, perform the following steps:

  1. Go to the Formulas tab, click the arrow next to Error Checking, and point to Circular References The last entered circular reference is displayed there.
    Checking the workbook for circular references
  2. Click on the cell listed under Circular References, and Excel will bring you exactly to that cell.

As soon as you do this, the status bar will notify you that circular references are found in your workbook and display an address of one of those cells:
The circular references notification in the status bar

If circular references are found in other sheets, the status bar displays only "Circular References" with no cell address.

Note. This feature is disabled when the Iterative Calculation option is turned on, so you need to turn it off before you start checking the workbook for circular references.

How to remove circular references in Excel

Regrettably, there is no mechanism in Excel that would let you eliminate all circular formulas in a workbook on a button click. To get rid of them, you will have to inspect each circular reference individually by performing the above steps, and then either remove a given circular formula altogether or replace it with one or more simple formulas.

How to trace relationships between formulas and cells

In cases when an Excel circular reference is not obvious, the Trace Precedents and Trace Dependents features can give you a clue by drawing one or more lines that show which cells affect or are affected by the selected cell.

To display the trace arrows, go to the Formulas tab > Formula Auditing group, and click one of the options:

Trace Precedents - traces cells that provide data to a formula, i.e. draws lines indicating which cells affect the selected cell.

Trace Dependents - traces cells that are dependent on the active cell, i.e. draws lines indicating which cells are affected by the selected cell. In other words, it shows which cells contain formulas that reference the selected cell.

Alternatively, you can use the following shortcuts:

  • Trace Precedents: Alt+T U T
  • Trace Dependents: Alt+T U D

To hide the arrows, click the Remove Arrows button that resides right underneath Trace Dependents.
Use the Trace Precedents and Trace Dependents options to display relationships between formulas and cells in Excel.

In the above example, the Trace Precedents arrow shows which cells directly supply data to B6. As you can see, cell B6 is also included, which makes it a circular reference and cause the formula to return zero. Of course, this one is easy to fix, just replace B6 with B5 in SUM's argument: =SUM(B2:B5)

Other circular references might not be that obvious and require more thought and calculations. The following article makes a good job explaining how you can solve circular references by using basic algebra.

This is how you deal with Excel circular references. Hopefully, this short tutorial has shed some light on this "blind spot", and now you can do further research to learn more. I thank you for reading and look forward to seeing you on our blog next week!

You may also be interested in

11 Responses to "Circular reference in Excel - how to check, find, enable, or remove"

  1. Excel user says:

    The problem is that the references are not and have never been circular. Excel makes an illogical assumption that the formula is not similar to other adjacent formulas. Not true and so what. It allows you to ignore each of its erroneous warnings but continues to give you the error message upon opening the file while admitting when you hit error checking that all references have been resolved.

  2. John says:

    For about 20 years I have been calculating our budget with a simple system. I have rows named 'housekeeping', 'vehicle expenses', 'insurance', 'medical expenses', 'clothing', ‘holiday & family’, ‘savings’ and a host of other items we budget for.
    The formula was simple, column 1 would have the name of budget, column 2 and 3 would be blank, that’s where we enter amount budgeted and spent for each item, like debit and credit columns. There is a hidden column (4) where we calculate the sum of column 3 less 2 (income less expenditure). Column 5 would calculate the balance of column 4 (which could be a plus or minus amount), plus column 5, (a circular reference) which would give us a running balance of that row. We delete entries in columns 2 and 3 and the balance remains for the following session. All the items added together in column 5 would then correspond with our bank statement.
    Through the years this system worked flawless until Microsoft brought out Excel 2016. The programme now refuses to calculate the circular reference and I don’t know how to get around this.
    Has anyone got any suggestions to get around the circular reference?

  3. Manish Garg says:

    Very useful, thank you for publishing the article.

  4. Vanitha says:

    Hi, when I use this =IF(B2="yes", IF(C2="" ,NOW(), C2), "") , I get 1/0/1900 as the timestamp. I have enabled iterative calculation. why am I not getting the right time stamp?

  5. Vanitha says:

    This problem can be solved if you use IFERROR function

  6. Greg Lambert says:

    I keep getting false circular reference errors. What could cause that?

  7. John C says:

    I am getting an invalid circular indication on the first line of a new workbook. Excel version 14.7.0. Help please.

  8. contributor says:

    hi, i do product review & tech writing. i cannot find a short procedure on finding a ciruclar reference. (all this verbage.. have no time for it, this is a steps thing, for 1 line bullet sequences). here's the only thing that worked for me so far:
    - select cell with ciruclar reference, in circular reference bar drop down box check all same cells where recently made a change / undo change, calc sheet. done.
    - when need it/ not always see how red lines show up on an error / if applies here or not.
    - still looking for main procedures other than my slight observation, thanks.

  9. Ramu Padala says:

    2-Jan-17 3-Jan-17 4-Jan-17 5-Jan-17 6-Jan-17

    12 30 40 50 10

    i have circular reference in my excel sheet A linking to other excel file B, when i change the date on the file B it updates the A file with the date match, retains the other dates values.Currently i am not able to perform sum on the file A since i want weekly total.

    Any help would be appreciated thanks

  10. Alireza says:

    Thank you soooooooooo much , it solved my problem completely.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard