by 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.
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: =SUM(B2:B5)
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!
69 comments
Hey! Thanks for this education. I got the whole stuff. But how did you format the date?
Hi! I recommend reading this guide: How to change Excel date format and create custom formatting.
A B C D E F
Date: 1/24/2022
Date Ticker Currency QTY@Buy/Sell Qty Remaining Share Price
1/1/2021 META USD 600 600 261
4/18/2021 META USD 700 1300 260
8/19/2021 META USD 600 1900 269
1/24/2022 META USD -1100 800 265
i want to find inventory value based on specific ticker (assume there's >1 ticker), date before B2, followed by FIFO. I need the function able to do looping (eg: on 1/24/2022, sold META's 1100 stocks, the calc should substract 600 stocks at $261, and 500 stocks at $260. so the remaining stocks will be 200 stocks i bought at $260, and 600 stocks i bought at $269. multiply the remaining stocks with their stock price respectively and then sum it together to get the total inventory value on 1/24/2022. ((200*$260)+(600*$269)). The function should able to sense if the first batch of the stocks need to sell more than 600, then it should run the second batch and see whether it needs to be sold more than how much the second batch have. if its lesser than what it have (eg: sell 500 stocks in second batch which has 700 stocks), then we should know how much is the remaining and multiply stock price at its price.
(data from above) the first sheet named: Transaction Log
the second sheet named : Inventory Value
inventory value sheets looks like
A B C
Date : 1/24/2022
Ticker Remaining Quantity inventory value?
NVDA 3200
CSCO 400
GOOGL 900
META 800
MAYBANK 1200
DBS 700
so what function can i use to find inventory value? I really need help
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Actual purpose of this sheet is to calculate how many parts can be cut from a full stock length (23 manually entered stock lengths in AE3-BA3) of material and part lengths listed in the cells under the stock length. Calculates scrap lengths in (23 Places AE36-BA36).
Starts out by entering in the cut lengths in B6-B35 and part qty in C6:C35. This information is then calculated in descending order and places in cells E6-E35 and corresponding qty placed in cells F6-F35. Then the Unique values in the E6-E35 cells are placed in H6-H35 and qty of each length of the same value are added and placed in J6-J35. There is a countif formula that tallies the number of parts that are manually entered into the worksheet and another subtraction formula to count down the parts needed. This all works fine, i just have to enter the Stock Length which is usually 240, then underneath that i start entering part lengths and watch the scrap as i type them in to make sure that I do not go into the negative.
what i would like is for the part of the sheet that I enter in the lengths to populate as i am entering the information.
I have tried a bunch of combinations and cannot get it to work out. Get the circular reference error and I turned on Iterative Calculations.
IF H3 is less than AE36, AND AB6 is greater than 0 and less than or equal to J6, if this is true then I need H6 to go into cell if not-
IF H3 is less than AE36, AND AB7 is greater than 0 and less than or equal to J7, if this is true then I need H7 to go into cell if not-
IF H3 is less than AE36, AND AB8 is greater than 0 and less than or equal to J8, if this is true then I need H8 to go into cell if not-
AND SO ON. The cell range this needs to go in is AE4:BA35 ( 736 Cells )
This is a lot I know, I can use sheet manually and it works out great, but if I can get it to populate on its own it would be even better.
thanks.
Hi! Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
I am trying to populate cells with cut lengths on parts. I got it where it sorts the lengths from greatest to shortest, it will also calculate the number of pcs that are a certain length. there is a lot to it to say the least. the following is the formula i am trying to use but it is giving me a circular reference error:
IFS(AND(I7=G7)=TRUE,G7,AND(I8G8)=TRUE,G8,AND(I9G9)=TRUE,G9,AND(I10G10)=TRUE,G10,AND(I11G11)=TRUE,G11,AND(I12G12)=TRUE,H12,AND(I13G13)=TRUE,H13,AND(I14G14)=TRUE,H14,AND(I15G15)=TRUE,H15,AND(I16G16)=TRUE,G16).
I can't check your formula because I don't have your data.
Is there a way to send the spreadsheet with sample data?
thanks
=IFS(AND(I7=G7)=TRUE,G7,AND(I8G8)=TRUE,G8,AND(I9G9)=TRUE,G9,AND(I10G10)=TRUE,G10,AND(I11G11)=TRUE,G11,AND(I12G12)=TRUE,H12,AND(I13G13)=TRUE,H13,AND(I14G14)=TRUE,H14,AND(I15G15)=TRUE,H15,AND(I16G16)=TRUE,G16)
It did not paste right for some reason on the original
formula will not paste write in comments,
Okay, but then what alternative would you then suggest to simplify: =IF(A10<>"",IF(B10="",NOW(),B10),""). I use this formula to automatically enter the date of the data entry. It's extremely important to have this information pull through correctly. Also, is there a work around for 365 (excel online) as this function does not work at all or is not supported in Excel online (Or so it seems)
Hi! To prevent your date from changing automatically, in addition to the method you described, you can use several methods:
1. Use Shortcuts to insert the current date and time
2. Replace the date and time returned by the TODAY function with their values. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V.
3. Use VBA macro, to automatically insert current date when cell value changes.
Very helpful. I Solved my this issue with your article. thank u very much.
problem solve
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 ?
Hi,
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.
So
- 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?
Thanks!
Tony
Hi!
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.
hi
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 ?
thanks
Hi!
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.
Sorry just thought that i would add i have set excel to Enable iterative calculations
Say A1 has new purchases and B1 has qty in stock.. how can B1 automatically update anytime I enter a new figure in A1.
Hi!
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
Hello!
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.
Regards
Hello!
You can use Data Validation rule
=COUNTIF(B1:B4,"Yes")<2
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.
Thanks!!!
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 : )