Cách khắc phục lỗi circular references warning! 10/2021

Thousands of users are facing the same problem on a daily basis simply because of forcing an Excel formula lớn calculate its own cell. When you try to vì chưng this, Excel throws up the following error message:

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

To put it simply, what Excel is trying lớn say is this: "Hey, I may get stuck at the round-about. Are you sure you want me to lớn 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 và concise definition of a circular reference provided by Microsoft:

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

For instance, if you select cell A1 và 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 lớn 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 khổng lồ calculate itself, and when that happens, Microsoft Excel returns the value from the last successful calculation.

But why would anyone want lớn make such a stupid formula that does nothing but cause unnecessary problems? Right, no sane user would ever want to lớn intentionally input đầu vào a circular formula like the above one. However, you may create a circular reference in your Excel sheet accidentally, & here"s a very common scenario.

Supposing you want to showroom 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 lượt thích in the following screenshot:

In some cases, one or more xanh arrows can also appear in your spreadsheet all of a sudden, so you might think your Excel has gone mad và 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, & 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 và 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 lớn insert today"s date in Excel. Và an overwhelming majority of questions posted in comments were about how to lớn enter a timestamp in Excel without it changing every time the worksheet is reopened or recalculated. I was very hesitant to lớn reply khổng lồ those comments because the only solution I know involves circular references, & they should be treated with care. Anyway, here is a very common scenario...

Supposing you have a danh sách 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 và time khổng lồ 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, và C2 is the cell where you want a timestamp lớn 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. & the second IF function is a circular formula that fetches the current day và 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 khổng lồ work, you must enable iterative calculations in your Excel workbook.See more: Không nội dung Được folder Trong Win 7 Để chia sẻ Dữ Liệu trong Mạng Lan

In Excel 2019, Excel 2016, Excel 2013, and Excel 2010, click File > Options, go khổng lồ Formulas, và select the Enable iterative calculation kiểm tra box under the Calculation options section.

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

In Excel 2003 & 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:

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 và not recommended approach. Apart from performance issues và a warning message displayed on every opening of a workbook (unless iterative calculations are on), circular references can lead to lớn a number of other issues, which are not immediately apparent.

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

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

How to lớn find circular references in Excel

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

As soon as you vì chưng this, the status bar will notify you that circular references are found in your workbook & 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.