How to find a formula error

Question

When I open an excel worksheet, a message comes up stating "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

How do I find where the error is?

Regards,

Lea

Answer 1

Dear @Lea Roy (Admin),

Thank you for posting your question in the Microsoft Q&A forum.

According to my research, here are some steps you can try to find and fix the circular reference:

Step 1: Use Excel’s Built-in Circular Reference Tool

• Open your workbook in Excel.
• Go to the Formulas tab on the ribbon.
• Click the Error Checking drop-down.
• Hover over Circular References.
  • Excel will list the first cell that has a circular reference.
• Click the listed cell to jump directly to it.
• Review the formula and remove the self-reference (or adjust the logic).
• (If there are multiple circular references, Excel will show them one by one as you fix each.)

Step 2: Check the Status Bar

• Look at the bottom-left corner of Excel. If a circular reference exists, you’ll see
• Clicking this will take you to the problematic cell

Step 3: Trace Precedents and Dependents

• With the problematic cell selected:
  • Go to Formulas > Trace Precedents to see which cells feed into it.
  • Use Trace Dependents to see which cells depend on it.
• This helps identify indirect loops

Step 4: If the Menu is Greyed Out

• Sometimes, the Circular References option is disabled if the error is hidden deep in a large workbook.
• In that case:
  • Temporarily disable iterative calculation (File > Options > Formulas > uncheck “Enable iterative calculation”).
  • Or test sheet by sheet: copy each sheet into a new workbook until you isolate the one causing the issue

Step 5: Enable/Disable Iterative Calculations in Excel

You can enable or disable iterative calculations in Excel. Follow the below steps to enable or disable iterative calculations in excel.

• Select File > Options > Formulas. If you're using Excel for Mac, select the Excel menu, and then select Preferences > Calculation.
• In the Calculation options section, select the Enable iterative calculation check box. On the Mac, select Use iterative calculation.
• 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.

• After enabling iterative calculation, you will see that the calculation will not result in 0 but will produce results in case of circular reference as you can see.

For more detailed instruction, please visit: Remove or allow a circular reference in Excel

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

I'm looking forward for your reply.

---

If my answer is helpful, please click "Accept Answer" 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

Dear @Lea Roy (Admin),

I hope you are doing well.  

My apologies for reaching out to you again to ensure everything is progressing smoothly on your end. If there's anything you'd like to share or if further assistance is needed, we're here to help. Your feedback is important, and I want to make sure nothing is left unattended.

If you see any replies, bring helpful information, please kindly accept it as an answer and vote it up by your original account which raised this question. Once marked, it will automatically pin to top. As other users will also search information in this community, your valuable marked answer will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.  

Looking forward to hearing from you.

Thank you for your patience and understanding. I'm looking forward for your reply.

---

If the answer is helpful, please click "Accept Answer" 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.