Circular Arguments

Question

How to find circular arguments within a spreadsheet?

Answer 1

Hi, Kevin OHara

Another practical way is to use Evaluate Formula, which lets you follow a calculation one step at a time:

Select a cell that contains a suspicious formula.

On the Formulas tab, select Evaluate Formula.

Select Evaluate repeatedly and watch which cell references are substituted.

If the calculation eventually returns to the starting cell, you have found the loop.

For a large or complicated workbook, you can also use the Spreadsheet Inquire add-in. Select File > Options > Add-ins, choose COM Add-ins, and enable Inquire. Then open the Inquire tab and select Workbook Analysis. The report lists formulas, errors, warnings, and their cell locations, which can make hidden formula problems easier to isolate. Spreadsheet Inquire is available only in supported editions of Excel for Windows, including Microsoft 365 Apps for enterprise and equivalent editions.

It is also recommended that you check whether iterative calculation is enabled under File > Options > Formulas. If it is enabled unintentionally, Excel may continue calculating a circular formula instead of making the problem obvious. Turn it off temporarily, recalculate the workbook, and check which formulas produce a warning or unexpected zero. Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

A circular reference happens when a formula refers to its own cell, either directly or indirectly.

To find circular references in Excel:

1. Select the Formulas tab.
2. Select the arrow next to Error Checking.
3. Point to Circular References.
4. Excel shows the cells involved. Select a listed cell to jump directly to it.
5. Review the formula in that cell.
6. Repeat for the next listed cell until the status bar no longer shows Circular References.

Useful checks:

• If the circular reference is on the active worksheet, the status bar in the lower-left corner shows Circular References and a cell address.
• If circular references exist on other worksheets but not the active one, the status bar shows only Circular References without a cell address.
• Moving through each sheet tab can help identify which sheet contains the circular reference.
• To inspect related cells, use Formulas > Trace Precedents or Trace Dependents.

If the formula was just entered, start by checking whether it refers to its own cell directly, such as a formula in A3 that includes A3. Also check for indirect references, where one formula points to another cell that eventually points back to the original cell.

---

References:

• Remove or allow a circular reference in Excel
• i am kumar age 65 .excel365 using. how to rectify circular reference having 10 sheets in my workbook how to find which sheet is circular ref .please help me. - Microsoft Q&A
• how to find a formula error in a spread sheet - Microsoft Q&A