{"id":266614,"date":"2024-03-13T20:08:44","date_gmt":"2024-03-13T19:08:44","guid":{"rendered":"https:\/\/www.sheetgo.com\/?p=266614"},"modified":"2026-11-11T14:44:52","modified_gmt":"2026-11-11T13:44:52","slug":"encontrar-referencias-circulares-excel","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/excel-features\/how-to-find-circular-references-in-excel\/","title":{"rendered":"C\u00f3mo encontrar referencias circulares en Excel (y corregirlas)"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Excel offers a wide and versatile set of formulas<\/a> that ranges from basic to more advanced calculations. However, the higher the level of complexity, the more chances you will encounter a problem in your dataset.<\/p>\n

If you have come across this error message, \u201cThere 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.”, then it is likely that you tried to apply a formula to its own cell.<\/p>\n

By the end of this article, you will know how to locate and remove a circular reference in Excel effectively to quickly resolve any further issues you may encounter in your dataset.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”what-is-a-circular-reference” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

What is a circular reference?<\/h2>\n

According to Microsoft<\/a>, a circular reference occurs when \u201cyour formula is trying to calculate itself\u201d. In simpler words, the formula refers to the same cell where it\u2019s located, creating a reference loop that keeps the formula working without completion. The examples below will show what a circular reference looks like in Excel.<\/p>\n

Circular reference example(s)<\/h3>\n

If you select a cell and type in \u201c=\u201d sign followed by that same cell, it creates a circular reference. For example, \u201c=E1".<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/how-to-find-circular-reference-in-excel-formula-e1.webp” alt=”How to find circular reference in Excel \u2014 cell E1 with the formula =E1 typed into it” title_text=”Cell E1 with the formula =E1 typed into it” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

As you can see below, Microsoft Excel prompts the circular reference error message. Click \u201cOK\u201d to proceed.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/circular-reference-in-excel-warning-message.webp” alt=”Circular reference in Excel \u2014 Microsoft Excel warning that there are one or more circular references, with OK and Help buttons” title_text=”Microsoft Excel warning that there are one or more circular references, with OK and Help buttons” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Now, we\u2019ll try with a less basic formula, an IF function<\/a> that also includes the cell of the formula. For example, \u201c=IF(E1=2, "YES")".<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/circular-reference-in-excel-if-formula.webp” alt=”Circular reference in Excel \u2014 cell E1 holding an IF formula that refers to E1 itself” title_text=”Cell E1 holding an IF formula that refers to E1 itself” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

As soon as you hit \u201cEnter\u201d you will get a \u201c0\u201d result instead of the error message. However, this is a way to let you know that there is something wrong with the formula. Now, let\u2019s explore how to find circular references in Excel to fix them.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”how-to-find-circular-references-in-excel” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

How to find circular references in Excel?<\/h2>\n

Finding a circular reference is easier than you think. You simply need to use the \u201cCircular References\u201d feature under the Formulas tab. This will help you locate any circular reference in your spreadsheet, so you can then proceed to remove or fix it, if possible.<\/p>\n

1.<\/strong> Go to Formulas > Error Checking > Circular References. The cells containing a circular reference will appear as a list next to the menu.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/find-circular-reference-in-excel-error-checking.webp” alt=”Find circular reference in Excel \u2014 Formulas tab Error Checking menu with Circular References pointing to $E$1″ title_text=”Formulas tab Error Checking menu with Circular References pointing to $E$1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

2.<\/strong> The circular reference is also displayed at the bottom of the active sheet, showing the cell where it\u2019s located. Here, \u201cE1\u201d.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/find-circular-reference-in-excel-status-bar.webp” alt=”Find circular reference in Excel \u2014 status bar at the bottom of the sheet showing Circular References: E1″ title_text=”Status bar at the bottom of the sheet showing Circular References: E1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Note that you may have circular references in sheets besides the active one, for example when a formula references another sheet<\/a>. In this case, Excel won\u2019t display the cell, only \u201cCircular Reference\u201d.<\/p>\n

If you find that the \u201cCircular References\u201d function doesn\u2019t work, you may need to disable the \u201cIterative Calculation\u201d option. Although this function is usually disabled by default, you can check by going to File > Options > Formulas.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/circular-reference-in-excel-iterative-calculation.webp” alt=”Circular reference in Excel \u2014 Excel Options Formulas page with Enable iterative calculation unchecked and Maximum Iterations set to 100″ title_text=”Excel Options Formulas page with Enable iterative calculation unchecked and Maximum Iterations set to 100″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

In \u201cCalculation options\u201d you should see the \u201cEnable iterative calculation\u201d box disabled. If you enable this feature, Excel will recalculate the formula as many times as necessary. This will definitely affect the correct functioning of your workbook. However, you can always specify how many times Excel should recalculate in \u201cMaximum Iterations\u201d.<\/p>\n

Now that you know how to identify a circular reference in Excel, let\u2019s see how you can remove circular references in your Excel file.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”how-to-remove-circular-references-in-exc” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

How to remove circular references in Excel?<\/h2>\n

Unfortunately, there is no button or feature to get rid of all circular references in Excel at once. If your dataset is large and complex, sometimes you can\u2019t find a circular reference in Excel as easily as shown previously. To do so, you\u2019ll need to trace relationships between formulas and cells. This is how you can trace relationships to fix circular references.<\/p>\n

1.<\/strong> Go to Formulas > Trace Precedents. This will display which cell is providing the data to the formula. Below, the blue arrow shows that cell F1, the precedent cell, is part of the formula in cell E1.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/fix-circular-reference-in-excel-trace-precedents.webp” alt=”Fix circular reference in Excel \u2014 Trace Precedents arrow between cells E1 and F1 on the Formulas tab” title_text=”Trace Precedents arrow between cells E1 and F1 on the Formulas tab” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

2.<\/strong> Select \u201cTrace Dependents\u201d to display the cells that contain the formula referencing the selected cell. Here, E1 is dependent on F1.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/fix-circular-reference-in-excel-trace-dependents.webp” alt=”Fix circular reference in Excel \u2014 Trace Dependents selected with a blue arrow linking E1 and F1″ title_text=”Trace Dependents selected with a blue arrow linking E1 and F1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

3.<\/strong> To find out exactly how each cell is referencing another in the formula, select \u201cShow Formulas\u201d from the \u201cFormulas\u201d tab. Now you can identify the formula issues.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/fix-circular-reference-in-excel-show-formulas.webp” alt=”Fix circular reference in Excel \u2014 Show Formulas view with =(E1*2\/F1) in column E and =100*E1 in column F” title_text=”Show Formulas view with =(E1*2\/F1) in column E and =100*E1 in column F” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

4.<\/strong> As you can see within the formulas, these two cells are completely dependent on each other, causing this circular reference. Now you can pinpoint which cell references you need to alter within the formula in order to break this cycle. The most common way to do this is by turning at least one of the dependent cells into a static value (if possible). In this example, I can substitute my E1 reference with the static value of \u201c5\u201d to avoid a circular reference.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/fix-circular-reference-in-excel-static-value.webp” alt=”Fix circular reference in Excel \u2014 E1 formula changed to =(5*2\/F1), returning 0.05 in column E and 200 in column F” title_text=”E1 formula changed to =(5*2\/F1), returning 0.05 in column E and 200 in column F” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

5.<\/strong> To continue working on your spreadsheet as normal, you can click \u201cRemove Arrows\u201d on the same \u201cFormulas\u201d tab. You have the option of removing them altogether, or according to the type of trace, as shown below.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/fix-circular-reference-in-excel-remove-arrows.webp” alt=”Fix circular reference in Excel \u2014 Remove Arrows menu with Remove Precedent Arrows and Remove Dependent Arrows” title_text=”Remove Arrows menu with Remove Precedent Arrows and Remove Dependent Arrows” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

In a few clicks, you\u2019ve seen how to trace the cell relationships within a circular reference and how to fix it by substituting a cell reference with a static value.<\/p>\n

Please note:<\/strong> There are many circumstances where you are purposefully creating a circular reference within your Excel workbook. In this case, it\u2019s best to reduce the number of circular references within a single worksheet in order to reduce loading times.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”conclusion” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Conclusion<\/h2>\n

If you want to stop Excel from entering an endless loop and slowing down all calculations overall, having a full understanding of what a circular reference is and how to fix it is as crucial as it is useful.<\/p>\n

This article has explained the concept of \u201ccircular reference\u201d. You have also seen how to find circular references and remove them in Excel in just a few steps. Interested in learning more about formulas in Excel? Take a look at some of our latest blog posts:<\/p>\n