Cómo permitir o eliminar referencias circulares en Excel

Normalmente vas a querer eliminar referencias circulares en Excel porque suelen ser un error, pero puede que sean intencionadas. Sin embargo, Excel, por defecto, no las permite. Aquí te explico cómo habilitarlas si las quieres usar y cómo eliminarlas si no.

eliminar permitir referencias circulares excel

Por si no tienes al 100% claro qué es una referencia circular, recuerda que es una referencia en una fórmula o función a sí mismo que provoca un bucle en el cálculo.

Por ejemplo: si la celda A1 contiene la fórmula =A1+A2, habremos provocado una referencia circular. El resultado de la fórmula forma parte del cálculo, la pescadilla que se muerde la cola.

Esto, en principio, es absurdo y, por tanto, Excel no lo permite.

Cómo permitir referencias circulares en Excel

Sin embargo, hay lógicas complejas donde un bucle de este tipo puede tener sentido. Por eso, Excel dispone de la opción de cálculo iterativo en las opciones de Excel a las que puedes acceder vía “Archivo / Opciones” y seleccionando la sección de Fórmulas en el cuadro de diálogo que se mostrará y que puedes ver a continuación:

opcion calculo iterativo excel
La opción de "Habilitar cálculo iterativo" se encuentra deshabilidata por defecto en Excel.

Por defecto, esta opción está deshabilitada y Excel avisará del error cuando entres en un bucle de este tipo.

Activando la opción, Excel ejecutará el bucle. Pero hay un problema: siendo un bucle este proceso, en principio, no terminaría nunca y Excel se quería calculando hasta el fin de los tiempos. Por ese motivo, obliga a especificar un número máximo de iteraciones.

Ejemplo de cálculo iterativo legítimo con una referencia circular

Hay ciertos problemas lógicos que se pueden modelar/resolver de esta manera, como los que nos encontramos en el álgebra, por ejemplo.

Pongamos un ejemplo sencillo: tenemos una ciudad grande (con muchos habitantes) y otra pequeña. Sabemos que la grande tiene tres veces más habitantes que la pequeña y la diferencia con esta es de 265.000 habitantes.

¿Cuántos habitantes tiene la ciudad pequeña?

Esto lo pueden modelar de la siguiente manera:

  • La celda “Ciudad Pequeña”: =”Ciudad Grande/3”
  • La celda “Ciudad Grande”: =”Ciudad Pequeña + 265.000”

Para esto usaremos las celdas C2 y C3 con las respectivas fórmulas:

  • =C3/3
  • =C2+265000

Aquí lo puedes ver:

ejemplo calculo iterativo ciudades
Ejemplo de un cálculo iterativo que para solucionar un problema matemático.

Como ves, Excel ha hallado el resultado correcto que es 132.500 habitantes en la ciudad pequeña.

¿Qué ha pasado aquí? Pues que va haciendo sucesivas iteraciones obteniendo resultados intermedios:

  1. La celda de la ciudad pequeña la considera inicialmente con valor 0 y la grande con 000.
  2. En la segunda iteración ya tiene un valor para la grande que es dividido en 3 es 333,33..., que sumado al valor de la grande es 353.333,33...
  3. En la tercera iteración, divide 333,33… entre 3 y el resultado lo vuelve a sumar a 265.000.
  4. Y así sucesivamente…

Con las iteraciones suficientes llegará al resultado final. Es muy bonito limitar sucesivamente las iteraciones a muy pocas (a 1, 2, 3, …) y observar cómo van cambiando los resultados de iteración en iteración.

Si limitamos las iteraciones a 10, por ejemplo, las cifras son de 132499,9169 y 397499,9169 respectivamente. Podemos ver cómo el cálculo ya se ha aproximado muchísimo al resultado correcto, pero aun sin alcanzarlo al 100%.

Cómo eliminar referencias circulares

Para eliminar referencias circulares, comprueba en primer lugar que la opción de cálculo iterativo no esté habilitada para que Excel te avise de los errores y los puedas identificar más fácilmente.

Normalmente, la solución del problema será trivial porque la referencia circular será un error, es decir, que has cometido un error en la fórmula. En todo caso, habrá que sacar simplemente la celda que causa el problema del bucle, es decir, llevarla a otra celda.

Si la depuración de las referencias circulares se te atasca, puedes usar también la funcionalidad de rastreo de dependencias entre celdas de Excel que se describe aquí:

Revisa bien tus fórmulas y sigue este proceso hasta que Excel deje de avisar de problemas de referencias circulares.

Acerca del autor: Berto López

Soy autor y fundador de este blog, e Ingeniero en Informática de formación con amplia experiencia: he trabajado como empleado, empresario, incluso funcionario. Vamos, que he probado de todo 😄

¿Te gustaría leer un poco más sobre éste u otros temas?