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.
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.
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:
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.
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 2010 and higher, 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.
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.
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:
Other circular references might not be that obvious and require more thought and calculations.
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!
I am writing a employee roster.
What formula can I use to replace a circular formula which states e.g., if tally of shifts <= to X, then plot shift.
The cell which 'counts (tallies)' the number of shifts already plotted does not increase by 1, when the above formula is true.
Is there an alternative formula I could use?
Unfortunately, this information is not enough to recommend a formula to you.
When I change a number that is related to a specific formula, the cell that has the formula does not change with the number I change. I have all calculations set to automatic and my iteration is enabled. What can I do to fix this ?
I wonder if you can provide some guidance? I put the question in this thread, as it may relate to circular references)
Can excel choose a value of a cell so that some logic is true? Allow me to explain......
In my example, I want to compare the impact of an electricity tariff which has a peak and off-peak price, using excel to identify the amount of off-peak usage required to make the tariff cheaper than a single flat-rate tariff.
- take a consistent usage of 6 (kWh in this case).
- flat rate price per hour is 0.2966
- dual rate tariff peak = 0.075
- dual rate tariff off-peak = 0.3566
Can we use excel to work out how much off-peak usage per day is required to make the dual rate tariff cheaper?
I am not an expert in the calculation of electricity tariffs. If you describe how you want to calculate the result, I will try to help with the formula.
it help to solve formula calculation
Hi I am trying to use the timestamp function but in a different way. I have a field for my PO number and then a Vlookup to pull through the due date however once the purchase order is closed off it will no longer be available in my table. what i was trying to do is if the Vlookup failed to find the due date then it left the old data in there. for example PO 125846 due 24/1/22
I used formula =IF(E4>0,IF(A4"",A4,Vlookup(E4,'Shortages SQL'!$A:$R,16,FALSE))
where E4 = the PO Number
A4 = feild for the due date
vlookup from 'Shortages SQL" tab to pull through due date if one is not already there
When the PO due date already exists it changes to 0/01/1900 instead of keeping the date that was there e.g 24/1/22
how can i make it keep the 24/1/22 ?
Sorry just thought that i would add i have set excel to Enable iterative calculations
Your formula is not complete. Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=IF(E4>0,IF(A4 < > "",A4,Vlookup(E4,’Shortages SQL’!$A:$R,16,FALSE))
I can't check the formula that contains unique references to your workbook worksheets.
If this is not what you wanted, please describe the problem in more detail.
Say A1 has new purchases and B1 has qty in stock.. how can B1 automatically update anytime I enter a new figure in A1.
If B1 contains a formula, then add minus A1 to it. If a number is written in B1, then it cannot be changed using a formula.
I am trying to successfully use a circular reference in a sheet. I am trying to achieve 3 cells to update to the same value when either of them is changed. Reason being is that I have 3 separate calculators created within the same sheet but the dependent calculations refer to the first calculator. The resulting formulas also apply to the other calculators. Ex:
C2, J2, & Q2 refers to the size of an item. If I use the second calculator without changing C2 first the calculations will not update in the second calc. I can link J2 to C2 and Q2 to C2 but if another reference is added (C2 to J2) it will create a circular reference.
Is there any way to force them to update according to any change across all three possible input locations succesfully?
Thanks for any help
Your task is not completely clear to me. Write the formulas you use in the cells.
Is there a way to find circular references when using Dynamic Arrays in Office365, because the Error Checking in Formula Auditing does not show a Circular Reference occurs, yet the Status Bar does?
I have created a circular reference on purpose using Dynamic Arrays to test it, but Excel knows it is there (status bar), but does not show it (Error Checking).
I am trying to set four cells so that they can only contain "Yes" or "No" but only one can be "Yes" at any one time, changing one of the "No" values to "Yes" needs to make the other cell switch to "No", then I need to protect this so that nothing else can be entered into the cells. Easy to make this work with two cells but is there a way with four?
Please can you help.
You can use Data Validation rule
Set it for each of the four cells.
The automatic change of the cell value from Yes to No is possible only with a VBA macro.
It Worked.Very useful.
It worked; I solved this annoying error in my document that I was facing for months. Thank you very much for this guide!
Very useful, thank you :D
Muchas gracias por la información, me ayudo a resolver el problema con mi hoja : )
When I open my country music excel file, I get a note saying there is a circular issue or something to this effect.
This is in my country music excel file where I am researching about 75 years of weekly Billboard country music charts. Each song is given points based on chart position for each and every week it is on the music charts. Most years have around 60 songs. Most of the charts have 60-100 positions, with a spreadsheet column for each possible chart position. I have broken those 75 years into smaller timespans between 5 - 10 years each and have made a subfolder for each time period. I also have subfolders for various working information to support the project. In addition I give points to any song winning or nominated for a major music award and for other misc things. The end result is subfolder with 200-300+ columns and hundreds of rows.
So now when I first open the excel program and select this entire folder, when the entire folder opens, I get this message from Excel about a circular issue. How do I identify which subfolder and where in it this issue is? The size and complexity of this folder make this Excel issue so very challenging to correct. This is proving to be quite frustrating and time consuming for what is of unknown importance. Any help you can provide to help me correct this issue would be greatly and I mean greatly appreciated.
Thanks so much,
Unfortunately, without seeing your data it hard to give you advice.
However, the instructions above tell you how to find the circular reference and how to fix it.
I am getting below error in my excel document.Kindly help me how to resolve this.
"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.
Try removing or changing these references, or moving the formulas to different cells."
Helpful - thank you!
If you want the current time stamp there's a simple shortcut Ctrl + ; or for a time stamp you can do Ctrl + Shift + ;
Current Date = Ctrl + ;
Current Time = Shift + Ctrl + ;
What do you do when you have a circular reference that is defined as a column in a table? So I am doing a SUMIFS or SUMPRODUCT on a column - but my cell is in that column. I need to define this dynamically, so I need to have the named column rather than a cell reference, and I can use SUMPRODUCT etc. to multiply by an array that always zeroes out my cell so it's never a true self reference, but Excel still recognises it as such. The labelling doesn't seem sophisticated enough that I can exclude Table[[#ThisRow]:[Column]] from Table[[#All]:[Column]] ... and I'm working on Excel 2007 for good measure.
I have a question about the above formula =IF(B2="yes", IF(C2="" ,NOW(), C2), "").
The sample shows that the dates are the same but the times are different. I follow the above formula but when I go to enter yes, the whole column changes to the same time. And to better help, here's what I'm trying to do:
I'm going to work in a stockroom and want to make it more productive and have an easier managing system for items than what's currently being used. I want to create a spread sheet where I can keep track of the date/time products enter or leave. For example, say Johnny is taking out a ream of paper on 4-3-19 at 3:50pm. Susie comes the same date but at 4:00pm and takes out 2 staplers. How can I get it to show the 3:50pm time and the 4:00pm with the current date?
Can you check time zone and Pc Current Date & Time
This is very helpful, especially on how to find the problems. Thank you.
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?
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) ?
Quick and easy solution. Thanks
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?
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?
did you ever get an answer?
Thank you soooooooooo much , it solved my problem completely.
I am getting a message while putting formulas in Microsoft excel 7 sheets that there "exists circular reference" in the sheet and the formula that I am trying to put is not calculating anything and showing me the same message every time so how to solve this problem if you can help me to solve the problem i will really appreciate your replies
I can't see your data, so I can't help you. Use the recommendations from the article above.
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
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.
I am getting an invalid circular indication on the first line of a new workbook. Excel version 14.7.0. Help please.
I keep getting false circular reference errors. What could cause that?
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)
This problem can be solved if you use IFERROR function
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.
Very useful, thank you for publishing the article.
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?
You can use a simple macro, which allows a statements such as this:
x = x + y
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.