practicaexcel

#¡REF! en Excel: qué significa y cómo arreglarlo

#¡REF! · en inglés #REF! · Errores

Actualizado el

#¡REF! quiere decir que la fórmula apunta a una celda que no existe. La causa más común: borras una fila, una columna o una hoja que la fórmula usaba, y Excel cambia esa referencia por #¡REF!. En el Excel en inglés se llama #REF!.

También aparece sin borrar nada: cuando pides a BUSCARV o a INDICE una columna o una fila que el rango no tiene, o cuando copias una fórmula y sus referencias se salen de la hoja. No lo escondas con SI.ERROR: un #¡REF! es una fórmula rota, y hay que repararla.

Separador:

Sintaxis

=B2+#¡REF!+B3
B2, B3
Las referencias que siguen en pie.
#¡REF!
El hueco de la celda borrada. Excel no guarda cuál era, así que la fórmula no se arregla sola: deshaz el borrado con Ctrl+Z o escribe otra vez la referencia buena.

Así queda =B2+B3+B4 si eliminas la fila 3: B3 desaparece y la antigua B4 sube a B3. Si la fórmula usa un rango, como =SUMA(B2:B4), y eliminas una fila del medio, Excel encoge el rango y no hay error. Por eso los rangos aguantan mejor los borrados que las celdas sueltas.

Ejemplos

Ejemplo 1: eliminar una fila que usaba la fórmula

Una cadena de heladerías de Córdoba (Argentina) sumaba sus cuatro locales con =B2+B3+B4+B5 y, en otra fila, con =SUMA(B2:B5). Cerró el local de Nueva Córdoba y alguien eliminó su fila. Así quedan los dos totales.

B5fx=B2+#¡REF!+B3+B4
AB
1LocalVentas
2Centro18400
3Güemes15250
4Cerro12900
5Total con +#¡REF!
6Total con SUMA46550

La fórmula con + perdió una de sus celdas, y Excel puso #¡REF! en su lugar. La de SUMA usaba un rango: Excel lo encogió de B2:B5 a B2:B4 y el total sigue bien. Para arreglar la primera, borra +#¡REF! en la barra de fórmulas, o cámbiala por la de SUMA.

Ejemplo 2: BUSCARV pide una columna que el rango no tiene

Un almacén de Barcelona busca el proveedor de un artículo. El proveedor está en la columna D, la cuarta de la tabla, pero el rango de la fórmula de G2 llega solo hasta la C.

G3fx=BUSCARV(F2;A2:D6;4;FALSO)
ABCDEFG
1CódigoArtículoStockProveedorCódigoProveedor
2A-110Silla plegable35Muebles GarbíA-130#¡REF!
3A-120Mesa auxiliar12Fusta NordMuebles Garbí
4A-130Estantería8Muebles Garbí
5A-140Taburete50Tallers Puig
6A-150Perchero21Fusta Nord

A2:C6 tiene 3 columnas, así que la 4 no existe y BUSCARV responde #¡REF!. En G3 el rango llega hasta la D, A2:D6, y la columna 4 ya es la del proveedor. Cuenta siempre desde la primera columna del rango, no desde la A de la hoja.

Ejemplo 3: INDICE y COINCIDIR con rangos distintos

En un club de pádel de Málaga, COINCIDIR busca a un jugador en la lista y INDICE trae su nivel. Se apuntaron dos jugadores nuevos al final, y en E2 solo se amplió uno de los dos rangos.

E3fx=INDICE(B2:B7;COINCIDIR(D2;A2:A7;0))
ABCDE
1JugadorNivelBuscarNivel
2Elena3,5Nerea#¡REF!
3Marcos43,5
4Lucía2,5
5Javier3
6Iván4,5
7Nerea3,5

COINCIDIR encuentra a Nerea en la posición 6 de A2:A7, pero B2:B5 solo tiene 4 filas: no hay sexta y sale #¡REF!. Los dos rangos tienen que empezar y acabar en la misma fila, como en E3. Si la lista crece a menudo, conviértela en una tabla de Excel (Insertar > Tabla) y sus rangos crecerán solos.

Pruébalo aquí

Una mensajería de México tiene su tarifa en B2:D6. La fórmula =BUSCARV(F2;B2:D6;4;FALSO) da #¡REF!: quien la escribió contó las columnas desde la A. En G2, escribe la búsqueda corregida, que devuelva la tarifa del destino de F2.

G2fx
ABCDEFG
1NºDestinoZonaTarifaDestinoTarifa
21PueblaCentro95Mérida
32TijuanaNorte180
43MéridaSur150
54LeónCentro90
65MonterreyNorte140
Pulsa el botón (o toca la hoja) y escribe la fórmula en G2: se corrige al momento.

Errores frecuentes

Eliminaste una fila, una columna o una hoja
La fórmula usaba una celda de lo que eliminaste. Excel la cambia por #¡REF! y no guarda cuál era. Pulsa Ctrl+Z enseguida. Si ya es tarde, escribe de nuevo la referencia. Para quitar datos sin romper fórmulas, borra el contenido con Supr en lugar de eliminar la fila.
BUSCARV pide una columna de más
El número de columna es mayor que las columnas del rango, muchas veces porque se cuenta desde la A de la hoja. Cuenta desde la primera columna del rango, o amplía el rango.
INDICE pide una posición que no está
La fila o la columna que pides es mayor que el rango, a menudo porque COINCIDIR busca en un rango más largo. Usa las mismas filas en INDICE y en COINCIDIR.
Al copiar, la fórmula se sale de la hoja
Una fórmula que mira la fila de arriba, como =B2-B1, copiada a la fila 1 tendría que mirar la fila 0, que no existe. Excel escribe =B1-#¡REF!. Pasa lo mismo a la izquierda de la columna A. No copies esa fórmula a la primera fila o columna, o fija con $ la referencia que no debe moverse.
Cortaste y pegaste encima de celdas que usa otra fórmula
Al pegar con Ctrl+X encima, las celdas de destino se sustituyen y las fórmulas que las usaban se quedan con #¡REF!. Pega en celdas vacías, o copia (Ctrl+C) en lugar de cortar.

Trucos

Preguntas frecuentes

¿Qué significa #¡REF! en Excel?

Que la fórmula hace referencia a una celda que no existe: la eliminaste, o pides una fila o una columna fuera del rango. En el Excel en inglés es #REF!, de «reference».

¿Cómo arreglo una fórmula con #¡REF!?

Si acabas de borrar algo, Ctrl+Z. Si no, haz clic en la celda, busca el #¡REF! en la barra de fórmulas y cámbialo por la celda correcta. Excel no recuerda cuál era, así que tienes que saberlo tú.

¿Por qué BUSCARV me da #¡REF!?

Porque el número de columna es mayor que las columnas del rango. En =BUSCARV(F2;A2:C6;4;FALSO) el rango tiene 3 columnas y pides la 4.

¿Puedo quitar el #¡REF! con SI.ERROR?

Poder, puedes: =SI.ERROR(B2+#¡REF!+B3;0). Pero no lo hagas. Un #¡REF! es una fórmula rota, no un dato que falta: si lo escondes, el resultado estará mal y nadie lo verá.

Funciones relacionadas