Error #¡REF! en Excel: por qué aparece y cómo arreglarlo

#REF!, en Excel en español #¡REF!, significa que una fórmula se refiere a una celda que no está. La tabla muestra los nombres de error en inglés. La causa habitual es una fila, una columna o una hoja borrada: cuando se borra la columna C, Excel reescribe =B2*C2 como =B2*#¡REF!, y desde entonces el resultado es #¡REF!. Pulsa Ctrl+Z (Cmd+Z en Mac) justo después del borrado para recuperar la columna y la fórmula.

FórmulasRestablecer

D2fx

=B2*#REF!

	A	B	C	D
1	Product	Price	Qty	Total
2	Apple	1.2	10	#REF!
3	Pear	1.5	20	#REF!
4	Plum	0.8	15	#REF!
5	Bread	2.4	5	#REF!

#REF! La fórmula apunta a una celda que no existe.En Excel en español: =B2*#REF!

La columna de cantidades se borró y se volvió a escribir, pero la fórmula sigue diciendo #REF!: Excel nunca repara una referencia una vez perdida. Haz clic en D2, cambia #REF! por C2 y pulsa Entrar. Toda la columna la sigue, y D2 muestra 12.

Cómo entra #¡REF! en una fórmula

Excel escribe #¡REF! en una fórmula siempre que desaparece una celda que la fórmula usaba:

Borrar celdas dentro de un rango es seguro: =SUMA(B2:D2) pasa a ser =SUMA(B2:C2) cuando se borra la columna C. Borrar la primera o la última celda de un rango solo lo reduce. Por eso =SUMA(B2:D2) es más segura que =B2+C2+D2, que pasa a ser =B2+#¡REF!+C2. La tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México).

Por qué BUSCARV devuelve #¡REF!

El tercer argumento de BUSCARV (VLOOKUP en inglés) cuenta columnas dentro del rango de la tabla. Si es mayor que el número de columnas del rango, el resultado es #¡REF!.

FórmulasRestablecer

F2fx

=VLOOKUP(E2,A2:C6,4,FALSE)

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Pear	#REF!
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

#REF! La fórmula apunta a una celda que no existe.En Excel en español: =BUSCARV(E2;A2:C6;4;FALSO)

A2:C6 tiene tres columnas, así que la 4 no existe. Cambia el 4 por 3 y F2 muestra 25. Pasa sobre todo después de borrar una columna de la tabla de búsqueda: el rango se reduce, el número de columna escrito a mano no. BUSCARX (XLOOKUP en inglés) o INDICE con COINCIDIR lo evitan porque nombran directamente la columna de retorno, como en =BUSCARX(E2;A2:A6;C2:C6). Consulta BUSCARV para el resto de sus argumentos.

#¡REF! con INDICE y DESREF

INDICE (INDEX en inglés) devuelve #¡REF! cuando el número de fila o de columna está fuera de su rango, y DESREF (OFFSET en inglés) cuando se desplaza por encima de la fila 1 o antes de la columna A.

FórmulasRestablecer

B2fx

=INDEX(A2:A6,6)

	A	B	C
1	Score	Result	What it asks for
2	88	#REF!	6th value of 5
3	72	95	3rd value of 5
4	95	#REF!	2 rows above A2
5	64	81	4 rows below A2
6	81

#REF! La fórmula apunta a una celda que no existe.En Excel en español: =INDICE(A2:A6;6)

A2:A6 tiene cinco notas, así que INDICE(A2:A6;6) es #¡REF! mientras que INDICE(A2:A6;3) devuelve 95. La fila 0 no existe, así que DESREF(A2;-2;0) es #¡REF!, y DESREF(A2;4;0) cae en A6: 81. Cuando la posición viene de otra fórmula (un COINCIDIR, un CONTAR), revisa primero esa fórmula. Hay más en la página de INDICE.

INDIRECTO también da #¡REF! cuando su texto no es una dirección válida (=INDIRECTO("ZZZ1"), porque la última columna es XFD) o apunta a un libro cerrado.

#¡REF! al copiar una fórmula

Una referencia relativa se mueve con la fórmula. Cópiala lo bastante hacia arriba o hacia un lado y la referencia se sale de la hoja:

C3:  =B2*2        (one row up, one column back)
copy C3 to B2:  =A1*2
copy C3 to A2:  =#REF!*2    (there is no column before A)

Lo mismo pasa cuando una fórmula copiada a otra hoja u otro libro apunta a celdas que allí no existen. Fija con $ las celdas que no deben moverse (=$B$2*2), o copia el texto de la fórmula desde la barra de fórmulas en lugar de la celda. Las referencias absolutas explican el $.

Encontrar y quitar todos los #¡REF! de un libro

1. Pulsa Ctrl+B (Ctrl+F en Excel en inglés, Cmd+F en Mac), escribe #¡REF!, abre Opciones, pon Buscar en en Fórmulas y haz clic en Buscar todos. La lista muestra todas las fórmulas con una referencia rota.
2. Para arreglar muchas a la vez, usa Ctrl+L (Ctrl+H en Excel en inglés, Control+H en Mac): busca #¡REF! y reemplázalo por la referencia correcta, pero solo cuando todas las coincidencias deban recibir la misma celda.
3. Revisa Fórmulas > Administrador de nombres: un nombre cuya columna Hace referencia a muestra #¡REF! rompe todas las fórmulas que lo usan.
4. Si los datos borrados ya no están y la fórmula ya no hace falta, selecciona las celdas y sustituye las fórmulas por sus valores (Copiar y luego Inicio > Pegar > Valores). Los valores de error siguen siendo errores, así que borra después esas celdas.

Arreglar una búsqueda que devuelve #¡REF!

FórmulasRestablecer

F2fx

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Plum
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Tu turno: =VLOOKUP(E2,A2:C6,4,FALSE) devolvió #REF!. Escribe en F2 una búsqueda que funcione y devuelva las existencias del producto de E2.

ComprobarVer respuesta

Pasa cualquier búsqueda que devuelva 60 aquí y siga a los datos: el BUSCARV con la columna 3, =BUSCARX(E2;A2:A6;C2:C6) o =INDICE(C2:C6;COINCIDIR(E2;A2:A6;0)).