Circular reference excel что это
You've entered a formula, but it’s not working. Instead, you’ve got this message about a “circular reference.” Millions of people have the same problem, and it happens because your formula is trying to calculate itself, and you have a feature called iterative calculation turned off. Here's what it looks like:
The formula =D1+D2+D3 breaks because it lives in cell D3, and it’s trying to calculate itself. To fix the problem, you can move the formula to another cell. Press Ctrl+X to cut the formula, select another cell, and press Ctrl+V to paste it.
Another common mistake is using a function that includes a reference to itself; for example, cell F3 contains =SUM(A3:F3). Here's an example:
You can also try one of these techniques:
If you just entered a formula, start with that cell and check to see if you refer to the cell itself. For example, cell A3 might contain the formula =(A1+A2)/A3. Formulas like =A1+1 (in cell A1) also cause circular reference errors.
While you're looking, check for indirect references. They happen when you put a formula in cell A1, and it uses another formula in B1 that in turn refers back to cell A1. If this confuses you, imagine what it does to Excel.
If you can't find the error, click the Formulas tab, click the arrow next to Error Checking, point to Circular References, and then click the first cell listed in the submenu.
Review the formula in the cell. If you can't determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.
Continue to review and correct the circular references in the workbook by repeating steps any or all of the steps 1 through 3 until the status bar no longer displays "Circular References."
If you're brand new to working with formulas, see Excel 2016 Essential Training at LinkedIn Learning.
The status bar in the lower-left corner displays Circular References and the cell address of one circular reference.
If you have circular references in other worksheets, but not in the active worksheet, the status bar displays only “Circular References” with no cell addresses.
You can move between cells in a circular reference by double-clicking the tracer arrow. The arrow indicates the cell that affects the value of the currently selected cell. You show the tracer arrow by clicking Formulas, and then click either Trace Precedents or Trace Dependents.
Learn about the circular reference warning message
The first time Excel finds a circular reference, it displays a warning message. Click OK or close the message window.
When you close the message, Excel displays either a zero or the last calculated value in the cell. And now you're probably saying, "Hang on, a last calculated value?" Yes. In some cases, a formula can run successfully before it tries to calculate itself. For example, a formula that uses the IF function may work until a user enters an argument (a piece of data the formula needs to run properly) that causes the formula to calculate itself. When that happens, Excel retains the value from the last successful calculation.
If you suspect you have a circular reference in a cell that isn't showing a zero, try this:
Click the formula in the formula bar, and then press Enter.
Important In many cases, if you create additional formulas that contain circular references, Excel won't display the warning message again. The following list shows some, but not all, the scenarios in which the warning message will appear:
You create the first instance of a circular reference in any open workbook
You remove all circular references in all open workbooks, and then create a new circular reference
You close all workbooks, create a new workbook, and then enter a formula that contains a circular reference
You open a workbook that contains a circular reference
While no other workbooks are open, you open a workbook and then create a circular reference
Learn about iterative calculation
At times, you may want to use circular references because they cause your functions to iterate—repeat until a specific numeric condition is met. This can slow your computer down, so iterative calculations are usually turned off in Excel.
Unless you're familiar with iterative calculations, you probably won't want to keep any circular references intact. If you do, you can enable iterative calculations, but you need to determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Excel stops calculating after 100 iterations, or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.
If you're using Excel 2010 or later, click File > Options > Formulas. If you're using Excel for Mac, click the Excel menu, and then click Preferences > Calculation.
If you're using Excel 2007, click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
In the Calculation options section, select the Enable iterative calculation check box. On the Mac, click Use iterative calculation.
To set the maximum number of times that Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
In the Maximum Change box, type the smallest value required for iteration to continue. This is the smallest change in any calculated value. The smaller the number, the more precise the result and the more time that Excel needs to calculate a worksheet.
An iterative calculation can have three outcomes:
The solution converges, which means a stable end result is reached. This is the desirable condition.
The solution diverges, which means that from iteration to iteration, the difference between the current and the previous result increases.
The solution switches between two values. For example, after the first iteration the result is 1, after the next iteration the result is 10, after the next iteration the result is 1, and so on.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
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:
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 2019, 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:
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. 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
43 comments to "Circular reference in Excel - how to check, find, enable, or remove"
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.
Hello!
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.
While working with Excel formulas, you may sometime see the following warning prompt.
This prompt tells you that there is a circular reference in your worksheet and this can lead or an incorrect calculation by the formulas. It also asks you to address this circular reference issue and sort it.
In this tutorial, I will cover all that you need to know about circular reference, and well as how to find and remove circular references in Excel.
This Tutorial Covers:
What is Circular Reference in Excel?
Let me try and explain this by a simple example.
Suppose you have the dataset in cell A1:A5 and you use the below formula in cell A6:
This will give you a circular reference warning.
This is because you want to sum the values in cell A1:A6, and the result should be in cell A6.
This creates a loop as Excel just keeps on adding the new value in cell A6, which keeps on changing (hence, a circular reference loop).
How to Find Circular References in Excel?
Below are the steps to find a circular reference in Excel:
Once you have addressed the issue, you can again follow the same steps above and it will show more cell references that have the circular reference. If there is none, you will not see any cell reference,
Another quick and easy way to find the circular reference is by looking at the Status bar. On the left part of it, it will show you the text Circular Reference along with the cell address.
There are a few things you need to know when working with circular references:
- In case the iterative calculation is enabled (covered later in this tutorial), the status bar will not show the circular reference cell address
- In case the circular reference is not in the active sheet (but in other sheets in the same workbook), it will only show Circular reference and not the cell address
- In case you get a circular reference warning prompt once and you dismiss it, it will not show the prompt again the next time.
- If you open a workbook that has the circular reference, it will show you the prompt as soon as the workbook opens.
How to Remove a Circular Reference in Excel?
Circular reference can also be caused based on multiple cells that feed into each other at many levels.
Let me show you an example.
- The formulas in cell A6 is =SUM(A1:A5)+C6
- The formula is cell C1 is =A6*0.1
- The formula in cell C6 is =A6+C1
In the above example, the result in cell C6 is dependent on the values in cell A6 and C1, which in turn are dependent on cell C6 (thus causing the circular reference error)
And again, I have chosen a really simple example just for demo purposes. In reality, these could be quite difficult to figure out and maybe far off in the same worksheet or even scattered across multiple worksheets.
In such a case, there is one way to identify the cells that are causing circular reference and then treat these.
Below are the steps to use trace precedents to find cells that are feeding to the cell that has the circular reference:
The above steps would show you blue arrows which will tell you what cells are feeding into the formula in the selected cell. This way, you can inspect the formulas and the cells and get rid of the circular reference.
How to Enable/Disable Iterative Calculations in Excel
When you have a circular reference in a cell, first you get the warning prompt as shown below, and if you close this dialog box, it will give you 0 as the result in the cell.
But in some cases, you may actually want the circular reference to be active and do a couple of iterations. In such a case, instead of an infinite loop, and you can decide how many times the loop should be run.
This is called iterative calculation in Excel.
Below are the steps to enable and configure iterative calculations in Excel:
Let me also quickly explain the two options in the iterative calculation:
- Maximum iterations: This is the maximum number of times you want Excel to calculate before giving you the final result. So if you specify this as 100, Excel will run the loop 100 times before giving you the final result.
- Maximum Change: This is the maximum change, which if not achieved between iterations, the calculation would be stopped. By default, the value is .001. The lower this value, the more accurate would be the result.
Remember that the more number of times the iterations run, the more time and resources it takes for Excel to do it. In case you keep the maximum iterations high, it may lead to your Excel slowing down or crashing.
Note: When iterative calculations are enabled, Excel will not show you the circular reference warning prompt and will also now show it in the status bar.Deliberately Using Circular References
But there might be some specific cases where you need a circular reference so that you can get the desired result.
One such specific case I have already written about it getting the time stamp in a cell in a cell in Excel.
For example, suppose you want to create a formula so that every an entry is made in a cell in Column A, the timestamp appear in Column B (as shown below):
While you can insert easily insert a timestamp using the below formula:
The issue with the above formula is that it would update all the timestamps as soon as any change is made in the worksheet or if the worksheet is reopened (as the NOW formula is volatile)
To get around this issue, you can use a circular reference method. Use the same formula, but enable iterative calculation.
There are some other cases as well where having the ability to use circular reference is desired (you can find one example here).
При работе в Excel можно столкнуться с циклическими ссылками, данная ситуация возникает тогда, когда формула в ячейке ссылается прямо или косвенно на саму себя, соответственно произвести вычисление такой формулы становится невозможно и Excel выдает предупреждение: "Некоторые формулы содержат циклические ссылки и напрямую или косвенно ссылаются на самих себя, то есть, на ячейки, в которых находятся. Из-за этого формулы могут вычисляться неправильно. Попробуйте удалить или изменить эти ссылки либо переместить формулы в разные ячейки".
В случае если файл простой, содержит один лист и несколько формул, найти ячейку с циклической формулой не представляется сложной задачей, но если файл состоит из множества листов, а то и ссылается на внешние файлы, которые в свою очередь ссылаются на него, то найти такую формулу вручную бывает сложной задачей, поэтому мы рассмотрим способ, который позволит это сделать быстрее.
Способ 1. Универсальный
Перейдите на вкладку "Формулы," там в блоке "Зависимости формул" нажмите на маленький треугольник справа от кнопки "Проверка ошибок" и выберите пункт "Циклические ссылки". В нем будут отражены те ячейки, в которых такая ошибка зафиксирована. Причем, что важно, будут показаны ошибки как в текущем листе, так и в других листах книги и даже в других книгах.
Теперь нажав на адрес ячейки с ошибкой, вы перейдете к ней и сможете ее скорректировать.
Способ 2. Ошибка на текущем листе
Этот способ подходит только если ошибка на текущем листе и вам по каким то причинам не хочется использовать первый способ (если честно, таких причин я не придумал, но мало ли. ). Итак при циклической ссылке в ячейке на текущем листе в панели уведомлений Excel укажет в какой именно ячейке ошибка.
Как вы видите, проблема в ячейке B5, туда вы можете перейти как просто прокрутив лист до нужного места, так и нажав F5 и в поле Ссылка прописав адрес ячейки.
Спасибо за внимание, надеюсь эта статья помогла вам решить проблемы с циклическими ссылками в Экселе.
Читайте также: