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.
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:
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.
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:
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.
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.
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.
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.
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 a less than 0.001 change 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:
- 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.
- 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:
If circular references are found in other sheets, the status bar displays only "Circular References" with no cell address.
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.
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!
20 Responses to "Circular reference in Excel - how to check, find, enable, or remove"
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.
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?
Very useful, thank you for publishing the article.
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?
same here.. do you find the right formula? may i know it?
Try to put "" instead of "=" on the first IF statement. See sample below.
=IF(C5"",(IF(D5="",NOW(),D5)),"")
" "
This problem can be solved if you use IFERROR function
how?
I keep getting false circular reference errors. What could cause that?
Hi Greg,
Usually, Excel displays that error when there is one or more formula on a sheet that directly or indirectly references its own cell. A typical example is a formula, say, in A10: =SUM(A:A)
I am getting an invalid circular indication on the first line of a new workbook. Excel version 14.7.0. Help please.
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.
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
Thank you soooooooooo much , it solved my problem completely.
I've used =NOW()to insert the current date into a cell
for years. In the latest version of Office 365 Excel it generates a circular reference error at every occurrence and shuts down the application even though it appears to be accepting inputs to new cells. Is anyone else experiencing this issue?
I am creating a project form for my team to use that includes circular formulas in order to timestamp completed milestone dates. However, people here get annoyed that they always need to enable reiterations to use the sheet - is there a way for the sheet to work on everyone's computer without them changing their excel options every time they use it?
Thanks!
M
Quick and easy solution. Thanks
Hello,
Thank you for your shared informations on excel.
Regularly, I note the date (in B1) and the value of an investment (in C1) and I want to retain the minimum (in F2) and maximum (in F3) values.
Here are the formulas:
In F1: True (to initialize Min and Max) or False
in F2: =IF(F1,C1,MIN(C1,F2))
in F3: =IF(F1,C1,MAX(C1,F2))
Everything works correctly with circular references
But, how to memorize at the same time the date (in B1) of this statement (in G2 or in G3) ?
Best regards
I have a list of five items. Now i need to randomly pick 2-5 items from it, sum them to get the total of the selected items. But i also need to get what percentage of each items are in the total. Say ItemA, ItemB.... ItemE. I choose items A,C and D. Total of these 3 items is T. How do i calculate the percentage of itemA to total items T?