Cómo encontrar referencias circulares en Excel para evitar datos defectuosos

¿Vio el error de referencia circular en una hoja de Excel y se preguntó si la hoja de trabajo está rota? ¡Confía en mí! No eres el primero en enfrentar este error de Excel. Cientos y miles de usuarios de Excel experimentan errores de referencia circular todos los días. ¡No es para preocuparse! Aquí se explica cómo encontrar referencias circulares en Excel y resolverlas.

Siendo un ávido usuario de Excel, rara vez escribe los mismos datos en muchas celdas. En su lugar, importa un conjunto de datos desde el origen y utiliza la referencia de celdas para crear fórmulas. Por lo tanto, crea una hoja de cálculo dinámica.

Cuando alguien cambia los valores de celda de las referencias de celda, la hoja de trabajo se actualiza automáticamente. Esto se llama automatización del análisis de datos y a los analistas de datos les encanta jugar con referencias de celdas.

Sin embargo, el problema llega cuando intencional o accidentalmente escribes la dirección de la celda donde estás realizando un cálculo actualmente. O bien, cuando está creando muchas fórmulas en la hoja de trabajo, una celda que contiene una fórmula aparece indirectamente en otra fórmula.

En tales y muchas otras situaciones similares, obtiene el error de referencia circular en Excel. Siga leyendo para saber qué es la referencia circular, sus razones, por qué evitarla y, lo que es más importante, cómo encontrar referencias circulares en Excel para deshacerse de estas anomalías para siempre.

¿Qué es una referencia circular en Excel?

Excel encuentra un error de referencia circular cuando ve que una o varias funciones de la hoja de trabajo hacen referencia a la dirección de la celda donde se crearon sus funciones.

Puede sonar confuso para usted, así que piense qué tan grave podría ser para la aplicación Excel. En resumen, nunca desea poner la dirección de la celda en una fórmula donde la está creando. Mire el siguiente ejemplo para una aclaración visual:

Estás tratando de multiplicar 10 de la celda B1 y poner el resultado en la celda B1 .

Ahora, en el escenario anterior, si simplemente presiona Aceptar , Excel cerrará el error y mostrará cero como resultado calculado. Este es solo un ejemplo sencillo.

¿Qué es la referencia circular en Excel?

Ahora, imagine que hay cientos de fórmulas en una hoja de trabajo de contabilidad, hoja de trabajo de planificación financiera, nómina de empleados, hoja de trabajo de pago de proveedores, etc.

Confiará en los datos erróneos calculados por Excel debido al error de referencia circular y eso a su vez afectará su toma de decisiones comerciales o profesionales.

Además, las referencias circulares pueden reducir considerablemente la velocidad de los cálculos dentro de la aplicación Excel. En casos severos, el mismo error también puede causar un rendimiento extremadamente lento en la PC si no se trata de inmediato.

Sin embargo, si está trabajando en fórmulas de Excel avanzadas en las que su modelo de datos exige entradas intermedias de la referencia circular para continuar con una función larga, es posible que deba usarla. De hecho, Excel te permite usar este método de cálculo cuando sea necesario.

Por ejemplo, en las unidades de producción de energía, como los molinos de viento, debe calcular la energía de salida reciclada a partir de una fracción de la energía de entrada. Alternativamente, en una planta química, los ingenieros usan el vástago de salida como entrada para la misma caldera o recipiente de proceso u otra. Dichos cálculos de ingeniería de alto nivel requieren referencias circulares para cálculos iterativos.

¿Cuándo ve el error de referencia circular en Excel?

Hablando claramente, la ventana emergente de referencia circular no es un mensaje de error. Es una forma de que Excel le informe que encontró una referencia circular en una o varias fórmulas y eso puede influir en su algoritmo de cálculo y rendimiento.

Para evitar los daños causados ​​por las referencias circulares en las fórmulas de Excel, debe saber cuándo aparecen y cuándo no. Encuentre a continuación las instancias:

• En cualquier hoja de trabajo o libro de trabajo abierto, crea una fórmula e ingresa la referencia circular por primera vez.
• Usted o alguien eliminó todas las referencias circulares del archivo de Excel y lo guardó. Ahora, nuevamente usted u otro usuario ingresan una referencia circular en el mismo archivo.
• Cerró todos los libros abiertos. Ahora, abra un libro de trabajo diferente y luego escriba una fórmula que contenga una referencia circular.

¿Cuándo ve el error de referencia circular en Excel?

• Acaba de abrir un libro de trabajo que anteriormente contenía una o varias referencias circulares. Verás la advertencia.
• Una vez que Excel le haya mostrado el mensaje de advertencia de referencia circular una vez y haya hecho clic en Aceptar , no volverá a mostrar la misma ventana emergente en la misma sesión, incluso si sigue creando más fórmulas de referencia circular.

Por lo tanto, debe buscar cuidadosamente las instancias anteriores para detectar advertencias de referencia circular y resolverlas si no está buscando cálculos iterativos.

Tipos de referencias circulares en Excel

Si desea aprender cómo encontrar referencias circulares en Excel, debe comprender sus tipos. Porque encontrar y eliminar referencias circulares en Excel no es una tarea automatizada. Encuentre a continuación los tipos comunes de este error:

Un ejemplo de uso de referencia circular

• Las referencias circulares intencionadas ocurren cuando las usa intencionalmente con fines de cálculo iterativo. Por ejemplo, debe ingresar una marca de tiempo en otra celda cuando alguien ingresa un valor en una celda referenciada usando funciones lógicas y volátiles como IF, OR, SUMIF, etc.

Referencia circular no deseada en Excel

• Referencias circulares no deseadas cuando intenta trabajar en una fórmula de Excel más rápido y hace referencia a la celda que contiene la fórmula por error. Por ejemplo, debe aplicar la función SUMA de la celda A1 a la A9 y obtener el resultado en la celda A10 . Pero por error, escribes la fórmula así en la celda A10:

=SUMA(A2:A10)

• Las referencias circulares ocultas son aquellas que no muestran ningún mensaje de advertencia cuando crea una fórmula o abre la hoja de trabajo por primera vez. Sucede porque alguien permitió el cálculo iterativo en el archivo de Excel.

Un ejemplo de una referencia circular indirecta

• Las referencias circulares indirectas también son populares y ocurren cuando crea una nueva fórmula en cualquier celda, por ejemplo, la celda A1 , y esta nueva fórmula usaría otra función en otra celda, como la celda B1 , que ya usa la celda A1 como referencia. .

Ahora que comprendió los conceptos básicos de las referencias circulares en Excel, veamos cómo encontrar referencias circulares en Excel a continuación:

Cómo encontrar referencias circulares en Excel: desde la barra de estado

Siempre que la función de cálculo iterativo esté desactivada, puede ver las siguientes notificaciones sobre referencias circulares en la barra de estado de Excel:

• Referencias circulares: C3  (o cualquier otra referencia de celda) cuando se encuentra en una hoja de trabajo que contiene una fórmula circular en el libro de trabajo.

Mostrar referencias circulares en la barra de estado de Excel

• Solo verá Referencias circulares si está en una hoja de trabajo que no contiene ninguna, pero el libro de trabajo tiene algunas de estas fórmulas circulares.

Cómo encontrar referencias circulares en Excel: usando la verificación de errores

Es la mejor manera de buscar referencias circulares en un libro o una hoja de trabajo. Estos son los pasos que debes saber:

• Abra una hoja de cálculo que sospeche que contiene fórmulas circulares.
• Haga clic en el menú Fórmulas en la cinta de opciones de Excel.

Use la verificación de errores para Cómo encontrar referencias circulares en Excel

• En el grupo de comandos Auditoría de fórmulas , haga clic en la lista desplegable Comprobación de errores  .
• Pase el cursor sobre la opción Referencias circulares para ver todas las instancias disponibles en todas las hojas de trabajo.
• Seleccione cada instancia y Excel identificará esas celdas automáticamente.

Cómo encontrar referencias circulares en Excel: desde el seguimiento de fórmulas

Si no encuentra el método de Comprobación de errores lo suficientemente eficiente como para detectar un gran número de fórmulas con referencias circulares, puede probar estos pasos para utilizar el método de seguimiento de fórmulas:

• Seleccione cualquier celda a la que se haga referencia o la celda que contiene la fórmula.
• Vaya a la pestaña Fórmulas en el menú de cinta de Excel y luego haga clic en Rastrear precedentes .
• Excel resaltará las celdas que afectan el resultado de la fórmula en la celda actual con flechas.

Use el seguimiento de fórmulas para Cómo encontrar referencias circulares en Excel

• También puede hacer clic en Rastrear dependientes para descubrir celdas afectadas por el valor de la referencia de celda seleccionada.
• Si Excel no muestra ningún error después de hacer clic en los comandos anteriores, no hay referencias circulares en su hoja de cálculo.
• Repita los pasos en todas las hojas de trabajo para verificar todo el libro de trabajo.

Este método es particularmente importante cuando las funciones de cálculo iterativo están activas en la hoja de cálculo de Excel.

Cómo encontrar referencias circulares en Excel: uso de las opciones de Excel

• Haga clic en la pestaña Archivo en la cinta de Excel y luego abra Opciones de Excel desde la barra lateral que aparece.
• En el cuadro de diálogo Opciones de Excel , seleccione el menú Fórmulas de la barra lateral y luego desmarque la casilla de verificación Habilitar cálculo iterativo .

Cómo desactivar el cálculo iterativo en Excel

• Haga clic en Aceptar para guardar los cambios.
• Ahora, el libro de trabajo o la hoja de trabajo le mostrará automáticamente la advertencia de referencia circular.
• Si no aparece al instante, guarde el libro de trabajo, ciérrelo y vuelva a abrirlo para ver la advertencia.
• Una vez que vea la advertencia, verifique la barra de estado de Excel para saber qué celda está en la referencia circular.

Cómo resolver referencias circulares en Excel

La forma segura de corregir uno o varios errores de referencia circular en Excel es editar manualmente cada fórmula que lo contiene. De esta forma, puede resolver referencias circulares tanto accidentales como indirectas en Excel.

Cómo resolver referencias circulares en Excel

Otra forma fácil es eliminar la fórmula de la celda actual y colocarla en otro lugar dentro de la misma hoja de trabajo.

Cómo encontrar referencias circulares en hojas de cálculo de Google (método de bonificación)

Al igual que Excel, Google Sheets no permite referencias circulares en una o más fórmulas si no ha activado la función de cálculo iterativo. Por lo tanto, Google Sheets mostrará inmediatamente el #REF. error en la celda que contiene una fórmula circular.

Hojas de cálculo de Google Cómo encontrar referencias circulares

Por lo tanto, simplemente puede seleccionar todas las celdas que contienen el #REF. advertencia para encontrar y resolver referencias circulares en Hojas de cálculo de Google.

Referencias circulares de Excel: palabras finales

Entonces, ahora conoce todos los secretos sobre cómo encontrar referencias circulares en Excel. Use el método que desee y elimine todas las referencias circulares para asegurarse de no confundirse con datos poco confiables de un informe de Excel o un tablero matemático.

A continuación, las  mejores plantillas de calendario de Excel para cualquier año  y las  mejores plantillas de presupuesto de Excel .