Cómo encontrar referencias circulares en Excel (y corregirlas)

Excel ofrece un conjunto amplio y versátil de fórmulas que van desde cálculos básicos hasta otros más avanzados. Sin embargo, cuanto mayor es la complejidad, más probabilidades tienes de encontrarte con un problema en tu conjunto de datos.

Si te has encontrado con este mensaje de error, "Hay una o más referencias circulares en las que una fórmula hace referencia a su propia celda, ya sea directa o indirectamente. Esto podría hacer que se calculen incorrectamente. Intente quitar o cambiar estas referencias, o mover las fórmulas a otras celdas.", lo más probable es que hayas intentado aplicar una fórmula a su propia celda.

Al terminar este artículo, sabrás cómo localizar y eliminar una referencia circular en Excel de forma eficaz para resolver rápidamente cualquier problema que puedas encontrar en tu conjunto de datos.

¿Qué es una referencia circular?

Según Microsoft, una referencia circular se produce cuando "la fórmula intenta calcularse a sí misma". Dicho de forma más sencilla, la fórmula hace referencia a la misma celda en la que se encuentra, lo que crea un bucle de referencias que mantiene la fórmula en funcionamiento sin llegar a completarse. Los ejemplos siguientes muestran cómo es una referencia circular en Excel.

Ejemplos de referencia circular

Si seleccionas una celda y escribes el signo "=" seguido de esa misma celda, se crea una referencia circular. Por ejemplo, "=E1".

Cómo encontrar una referencia circular en Excel — celda E1 con la fórmula =E1 escrita

Como ves a continuación, Microsoft Excel muestra el mensaje de error de referencia circular. Haz clic en "Aceptar" para continuar.

Referencia circular en Excel — advertencia de Microsoft Excel de que hay una o más referencias circulares, con los botones Aceptar y Ayuda

Ahora probaremos con una fórmula menos básica, una Función IF que también incluye la celda de la fórmula. Por ejemplo, "=IF(E1=2, "YES")".

Referencia circular en Excel — celda E1 con una fórmula IF que hace referencia a la propia E1

En cuanto pulses "Intro", obtendrás un resultado "0" en lugar del mensaje de error. Aun así, es una forma de avisarte de que algo falla en la fórmula. Ahora veamos cómo encontrar referencias circulares en Excel para corregirlas.

¿Cómo encontrar referencias circulares en Excel?

Encontrar una referencia circular es más fácil de lo que crees. Solo tienes que usar la función "Referencias circulares" de la pestaña Fórmulas. Te ayudará a localizar cualquier referencia circular en tu hoja de cálculo para que después puedas eliminarla o corregirla, si es posible.

1. Ve a Fórmulas > Comprobación de errores > Referencias circulares. Las celdas que contienen una referencia circular aparecerán en una lista junto al menú.

Encontrar una referencia circular en Excel — menú Comprobación de errores de la pestaña Fórmulas con Referencias circulares apuntando a $E$1

2. La referencia circular también se muestra en la parte inferior de la hoja activa, con la celda en la que se encuentra. En este caso, "E1".

Encontrar una referencia circular en Excel — barra de estado en la parte inferior de la hoja con Referencias circulares: E1

Ten en cuenta que puede haber referencias circulares en hojas distintas de la activa, por ejemplo cuando una fórmula hace referencia a otra hoja. En este caso, Excel no mostrará la celda, solo "Referencias circulares".

Si ves que la función "Referencias circulares" no funciona, puede que tengas que desactivar la opción "Cálculo iterativo". Aunque suele estar desactivada de forma predeterminada, puedes comprobarlo en Archivo > Opciones > Fórmulas.

Referencia circular en Excel — página Fórmulas de Opciones de Excel con Habilitar cálculo iterativo desactivado e Iteraciones máximas en 100

En "Opciones de cálculo" deberías ver la casilla "Habilitar cálculo iterativo" desactivada. Si activas esta función, Excel volverá a calcular la fórmula tantas veces como sea necesario, lo que sin duda afectará al correcto funcionamiento de tu libro. Aun así, siempre puedes indicar cuántas veces debe recalcular Excel en "Iteraciones máximas".

Ahora que sabes identificar una referencia circular en Excel, veamos cómo eliminar las referencias circulares de tu archivo de Excel.

¿Cómo eliminar referencias circulares en Excel?

Por desgracia, no hay ningún botón ni función para eliminar todas las referencias circulares de Excel de una vez. Si tu conjunto de datos es grande y complejo, a veces no encontrarás una referencia circular en Excel tan fácilmente como hemos visto. Para ello, tendrás que rastrear las relaciones entre fórmulas y celdas. Así puedes rastrear las relaciones para corregir las referencias circulares.

1. Ve a Fórmulas > Rastrear precedentes. Así verás qué celda proporciona los datos a la fórmula. A continuación, la flecha azul muestra que la celda F1, la celda precedente, forma parte de la fórmula de la celda E1.

Corregir una referencia circular en Excel — flecha de Rastrear precedentes entre las celdas E1 y F1 en la pestaña Fórmulas

2. Selecciona "Rastrear dependientes" para ver las celdas que contienen la fórmula que hace referencia a la celda seleccionada. Aquí, E1 depende de F1.

Corregir una referencia circular en Excel — Rastrear dependientes seleccionado con una flecha azul que une E1 y F1

3. Para saber exactamente cómo cada celda hace referencia a otra en la fórmula, selecciona "Mostrar fórmulas" en la pestaña "Fórmulas". Ahora puedes identificar los problemas de la fórmula.

Corregir una referencia circular en Excel — vista Mostrar fórmulas con =(E1*2/F1) en la columna E y =100*E1 en la columna F

4. Como ves en las fórmulas, estas dos celdas dependen por completo la una de la otra, lo que provoca la referencia circular. Ahora puedes determinar qué referencias de celda tienes que cambiar en la fórmula para romper este ciclo. Lo más habitual es convertir al menos una de las celdas dependientes en un valor estático (si es posible). En este ejemplo, puedo sustituir la referencia E1 por el valor estático "5" para evitar una referencia circular.

Corregir una referencia circular en Excel — fórmula de E1 cambiada a =(5*2/F1), que devuelve 0,05 en la columna E y 200 en la columna F

5. Para seguir trabajando con normalidad en tu hoja de cálculo, haz clic en "Quitar flechas" en la misma pestaña "Fórmulas". Puedes quitarlas todas a la vez o según el tipo de rastreo, como se muestra a continuación.

Corregir una referencia circular en Excel — menú Quitar flechas con Quitar flechas precedentes y Quitar flechas dependientes

En unos pocos clics, has visto cómo rastrear las relaciones entre celdas de una referencia circular y cómo corregirla sustituyendo una referencia de celda por un valor estático.

Tenga en cuenta lo siguiente: Hay muchas situaciones en las que creas una referencia circular a propósito en tu libro de Excel. En ese caso, lo mejor es reducir el número de referencias circulares en una misma hoja para acortar los tiempos de carga.

Conclusión

Si quieres evitar que Excel entre en un bucle infinito y ralentice todos los cálculos, entender bien qué es una referencia circular y cómo corregirla es tan importante como útil.

En este artículo te hemos explicado el concepto de "referencia circular". También has visto cómo encontrar referencias circulares y eliminarlas en Excel en unos pocos pasos. ¿Quieres saber más sobre las fórmulas de Excel? Echa un vistazo a algunas de nuestras últimas entradas del blog:

Estos bucles son más difíciles de detectar cuando tus fórmulas toman valores de otras hojas y libros. Si copias datos entre archivos de Excel a mano, las automatizaciones con IA de Sheetgo pueden conectar esos archivos y actualizarlos de forma programada por ti.