Cómo resolver el error #REF! en las hojas de cálculo

Tanto si eres un usuario avanzado de hojas de cálculo, como si te sientes más cómodo con las funciones más básicas de las hojas de cálculo, todos hemos experimentado un #REF! error en algún momento. Sin embargo, estos errores plantean un problema más difícil en un entorno empresarial, especialmente cuando comparte sus archivos con otros usuarios o colabora en ellos simultáneamente. Si un error #REF! se deja sin corregir, existe un alto riesgo de perder columnas, filas o incluso hojas enteras de datos.

Exploremos los tipos más comunes de errores #REF! que puedes encontrar al trabajar con una hoja de cálculo, y cómo puedes rectificarlos para evitar el riesgo de perder datos importantes.

¡¿Por qué recibo #REF! en las hojas de cálculo?

Hay muchas maneras de acabar con un ¡#REF! en una de tus celdas. Puede ser el resultado de borrar accidentalmente una fila/columna, poner referencias incorrectas en una fórmula o mover fórmulas/hojas a una nueva ubicación. Aunque los errores #REF! pueden aparecer en cualquier número de situaciones, siempre se originan en uno de estos dos lugares:

Perder la referencia de una celda concreta
Perder la referencia de una hoja de cálculo concreta

Veamos con más detalle estas dos situaciones y cómo corregir los errores #REF! en cada una de ellas.

¡Cómo resolver #REF! Cuando ha perdido la referencia de una celda en particular

¡Esta es la forma más común de que los usuarios reciban el error #REF! Perder la referencia de una celda concreta puede ocurrir de muchas formas distintas al utilizar fórmulas en las hojas de cálculo:

Suprimir una célula esencial
Suprimir una fila/columna esencial
Suprimir una ficha esencial
Copiar y pegar la fórmula en otra celda/hoja

Puede rectificar todo lo anterior utilizando los siguientes métodos. Veamos un caso de uso como ejemplo para mostrar cómo corregir errores #REF! en estas circunstancias.

Imagine que estoy gestionando los pedidos de mi cadena de ropa deportiva. La siguiente captura de pantalla muestra mi hoja de cálculo que contiene el número de pedidos realizados para cada producto, para cada ubicación de la tienda. He utilizado una sencilla fórmula SUMA para calcular el número total de pedidos de cada tienda.

He cancelado todos los pedidos de baloncesto de mis tiendas. Para mantener mi hoja de cálculo actualizada, borro la columna de baloncesto por completo. ¡Cuando hago esto, aparecen errores #REF!.

Entonces, ¿cómo puedo rectificar esto? ¡Veamos las dos formas principales de solucionar mis errores #REF!.

Deshacer acciones y reinsertar entradas

La forma más sencilla de rectificar este error es deshacer tus acciones para volver a la hoja de cálculo original y encontrar otra forma de cumplir tu objetivo.

Deshaga sus acciones pulsando el botón Deshacer de la barra de herramientas, o utilizando los atajos Ctrl + Z, o Cmd + Z para Mac.
Deberían aparecer tus datos originales. En este ejemplo, mi columna de baloncesto ha vuelto a aparecer.
Puedes optar por buscar una solución alternativa a tu problema. En este caso, podría sustituir las celdas de la columna de baloncesto por un valor cero ‘0’, de forma que la fórmula siga siendo válida y obtenga el resultado que busco.

Utilice la función IFERROR

Aunque esta función no corrige un error #REF!, es extremadamente útil emplearla en sus hojas de cálculo cuando decida compartirlas con otros usuarios.

No sólo te permite limpiar tu hoja de cálculo de múltiples errores, sino que puedes ajustar la fórmula para dejar un mensaje a futuros usuarios. Esto podría ser para hacerles saber que hay un problema con la fórmula. Como resultado, las cosas son más claras para los recién llegados a la hoja de cálculo, en lugar de simplemente ser presentado con un #REF!.

¡Haz doble clic en la celda que contiene #REF!
Vuelva a escribir su fórmula utilizando el IFERROR. Puedes elegir reemplazar tus errores con una celda en blanco o un valor cero, o puedes optar por añadir un mensaje para alertar a otros usuarios del problema. Por ejemplo, en mi fórmula, utilizaré lo siguiente:

=IFERROR(#REF!, "pedidos de baloncesto cancelados")

¡Para más información sobre cómo utilizar la función IFERROR para #REF! y otros errores de hoja de cálculo, eche un vistazo a nuestro artículo sobre Cómo utilizar la función IFERROR en Google Sheets.

¡Cómo resolver #REF! Cuando has perdido la referencia de una hoja de cálculo concreta

Hay dos formas principales de perder la referencia de una hoja de cálculo concreta:

Eliminación de un archivo de hoja de cálculo que se ha utilizado dentro de una fórmula en otro archivo de hoja de cálculo
Compartir una hoja de cálculo con otros usuarios que no tienen acceso a los demás archivos a los que se hace referencia en una fórmula.

En primer lugar, anota siempre las fuentes que intervienen en las fórmulas de tus hojas de cálculo. Puedes hacerlo añadiendo un comentario a la celda que contiene la fórmula. Así sabrás exactamente qué archivos no puedes eliminar sin que ello afecte a tus fórmulas.

Es más, puedes evitar por completo los errores #REF! haciendo una copia de los archivos a los que se hace referencia en la fórmula y añadiéndolos a la carpeta donde se encuentra tu hoja de cálculo actual. De esta forma, aunque se eliminen las fuentes originales, dispondrás de tus propias copias que permitirán que la fórmula funcione. Esta es también una gran solución cuando compartes tu hoja de cálculo con otros usuarios - ellos tendrán acceso a todos los datos dentro de esa carpeta.

Una alternativa importante, más eficaz y fácil de gestionar

Empezar la prueba gratuita

Cómo eliminar múltiples errores #REF! a la vez

Si desea deshacerse de numerosos errores #REF!, tiene dos opciones: puede corregir cada uno de ellos manualmente utilizando las técnicas mostradas anteriormente, o puede tratar de corregirlos todos a la vez. Esto último le ahorrará mucho tiempo, aunque puede que no le muestre cómo se produjo cada error de referencia.

Si desea localizar todos los errores #REF! al mismo tiempo, siga estos pasos.

1. Abra la función Buscar y reemplazar mediante la combinación de teclas Ctrl + H/Cmd + Mayús + H, o diríjase a Edición > Buscar y reemplazar.

2. Cuando aparezca el cuadro, escriba ‘#REF!’ en la casilla Buscar casilla. Para reemplazar con una celda en blanco, deje la casilla Sustituir por en blanco. Marque la casilla, Buscar también dentro de las fórmulas.

3. Haga clic en el botón Sustituir todo para aplicarlo. Debería ver un mensaje notificándole cuántos errores #REF! se han encontrado y sustituido.

4. Haga clic en Hecho.

#REF error Google spreadsheet

¡Y ahí lo tienes! Cuando se trata de errores #REF, la prevención es la mejor cura. Cuando se trata de estos errores en un único archivo de Google Sheets, suele ser mejor deshacer las acciones para revertir los cambios y encontrar una solución alternativa. Por otro lado, si se trata de errores #REF! que abarcan varias hojas de cálculo en distintas ubicaciones, toma nota de los distintos archivos implicados en tus fórmulas y haz copias para evitar arriesgarte a repercusiones peores. En última instancia, la mejor solución es deshacerse de los errores #REF! utilizando la sencilla función Buscar y reemplazar.

¿Quieres saber más sobre cómo revertir cambios y proteger tus hojas de modificaciones accidentales? Consulte nuestro artículo sobre Cómo ver el historial de edición en Google Sheets.

Si lo prefiere, descubra a continuación las entradas del blog relacionadas.

Nota del editor: Esta es una versión revisada de un post anterior que se ha actualizado para mayor precisión y exhaustividad.