Buzz
In the following article, Mytour will guide you on how to fix Circular Reference errors in Excel. Additionally, readers can also refer to some other articles available on Mytour to learn how to fix #NAME? and #REF! errors in Excel.
1. Circular Reference Error in Excel
Circular Reference error in Excel occurs when a formula in a cell refers back to that same cell. For example, in the screenshot below, the formula in cell F7 is:
=F5+F6+F7
This creates a Circular Reference error because the formula entered in cell F7 refers to cell F7 itself. This error will sequentially remove the results of other formulas in cells D7, C11, and D11:
=F7 // formula in cell C7
=SUM(B7:C7) // formula in cell D7
=SUM(C5:C9) // formula in cell C11
=SUM(D5:D9) // formula in cell D11
Circular Reference can lead to various issues and confusion as errors may cause other formulas to return a value of 0 or inaccurate results.
2. Circular Reference Error Notification
If Circular Reference appears in the spreadsheet, you will see an error notification like the following:
'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'.
This warning may appear sporadically while editing or when the spreadsheet is open.
3. Finding and Fixing Circular Reference Errors in Excel
To fix circular reference errors in Excel, we first need to identify cells with incorrect references and adjust them if necessary. However, unlike other errors such as #N/A or #VALUE! in Excel, circular reference errors do not appear directly in cells.
To trace the source of circular reference errors, we need to utilize the Error Checking menu in the Formulas tab on the Ribbon.
Select the Circular References option to identify the source of circular reference errors:
After fixing circular reference errors, formulas will return accurate results:
Above, Mytour has just guided you on how to fix Circular Reference errors in Excel. Hopefully, this article has provided you with useful information about the error as well as common fixes in Excel. Additionally, if you have any doubts or questions that need clarification, readers can leave their comments below the article, such as how to fix #### errors in Excel, for example ...
