Excel formula: how to fix a circular reference error

      70
Excel for ecole.vn 365 Excel for ecole.vn 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm nhâm thìn Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

You"ve sầu entered a formula, but it’s not working. Instead, you’ve sầu got this message about a “circular reference.” Millions of people have the same problem, and it happens because your formula is trying to lớn calculate itself, & you have a feature called iterative sầu calculation turned off. Here"s what it looks like:

*

The formula =D1+D2+D3 breaks because it lives in cell D3, & it’s trying to calculate itself. To fix the problem, you can move sầu the formula to another cell. Press Ctrl+X lớn cut the formula, select another cell, and press Ctrl+V lớn paste it.

Bạn đang xem: Excel formula: how to fix a circular reference error

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 và check khổng lồ 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, kiểm tra for indirect references. They happen when you put a formula in cell A1, và it uses another formula in B1 that in turn refers baông xã lớn cell A1. If this confuses you, imagine what it does lớn Excel.

If you can"t find the error, clichồng the Formulas tab, cliông chồng the arrow next lớn Error Checking, point khổng lồ Circular References, và then cliông xã the first cell listed in the subthực đơn.

*

Đánh Giá the formula in the cell. If you can"t determine whether the cell is the cause of the circular reference, cliông xã the next cell in the Circular References subthực đơn.

Continue khổng lồ reviews 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."

Tips

The status bar in the lower-left corner displays Circular References & 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 sầu 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, & then cliông xã 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. Clichồng OK or cthua kém the message window.

When you cthua 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 lớn 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 sầu a circular reference in a cell that isn"t showing a zero, try this:

Cliông chồng the formula in the formula bar, và then press Enter.

Xem thêm: Những Điều Kiêng Kỵ Ngày Mùng 1 0 Điều Kiêng Kị Ngày Rằm, Mồng Một Bạn Nên Biết

Important In many cases, if you create additional formulas that contain circular references, Excel won"t display the warning message again. The following danh sách shows some, but not all, the scenarquả táo 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, & then create a new circular reference

You close all workbooks, create a new workbook, và 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 & then create a circular reference

Learn about iterative calculation

At times, you may want to lớn use circular references because they cause your functions to lớn iterate—repeat until a specific numeric condition is met. This can slow your computer down, so iterative sầu calculations are usually turned off in Excel.

Unless you"re familiar with iterative calculations, you probably won"t want lớn keep any circular references intact. If you bởi, you can enable iterative sầu calculations, but you need lớn 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 và the amount of acceptable change.

If you"re using Excel 2010 or later, click File > Options > Formulas. If you"re using Excel for Mac, cliông chồng the Excel thực đơn, & then click Preferences > Calculation.

If you"re using Excel 2007, clichồng the ecole.vn Office Button

*
, click Excel Options, và then cliông chồng the Formulas category.

In the Calculation options section, select the Enable iterative sầu calculation kiểm tra box. On the Mac, cliông xã Use iterative sầu 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 lớn calculate a worksheet.

In the Maximum Change box, type the smallest value required for iteration lớn continue. This is the smallest change in any calculated value. The smaller the number, the more precise the result & the more time that Excel needs khổng lồ calculate a worksheet.

An iterative sầu calculation can have three outcomes:

The solution converges, which means a stable over result is reached. This is the desirable condition.

The solution diverges, which means that from iteration to lớn iteration, the difference between the current & 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.

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community or get ecole.vn in the Answers community.