Función DESREF en Excel (rangos dinámicos)

Aprende DESREF en Excel para devolver una referencia desplazada: el último valor de una columna, la suma de los últimos N y rangos dinámicos. Guía.

fxBlog

Función DESREF en Excel (rangos dinámicos)

Aprende DESREF en Excel para devolver una referencia desplazada: el último valor de una columna, la suma de los últimos N y rangos dinámicos. Guía.

Listo·Alejandro·14 min de lectura·Actualizado 4 Nov 2026

Hay una creencia muy repetida sobre DESREF: que es una función de búsqueda, una prima rara de BUSCARV. No lo es. DESREF no va a por ningún dato, solo fabrica una dirección. Le das un punto de partida, le dices cuántas filas y columnas te apartas de él y devuelve una referencia que otra fórmula (una suma, un gráfico, una lista desplegable) usará como si la hubieras escrito a mano.

Esa diferencia lo explica casi todo: por qué sirve para rangos que crecen solos, por qué recalcula sin parar y por qué se descoloca cuando alguien inserta una fila. Empezamos por la chuleta, seguimos con el concepto y las variantes con fórmulas, y acabamos con los fallos que más se repiten.

Función DESREF en Excel (rangos dinámicos)
Función DESREF en Excel (rangos dinámicos)

Ejemplo listo para abrir. Esta guía se entiende mejor con un archivo delante: la plantilla «Arqueo y Cuadre de Caja Diario» es gratis y no lleva macros.

⬇️ Descargar la plantilla (gratis)
Hoja Arqueo con fecha 08/09/2026 y filas de billetes y monedas; los billetes de 200, 50 y 20 euros tienen cantidades 2, 5 y 10 e importes 400, 250 y 200 euros
Captura real de la plantilla «Arqueo y Cuadre de Caja Diario», con datos de ejemplo. Ver la plantilla completa.

Repasa la chuleta de DESREF antes de escribir una sola fórmula

Si ya conoces la función y solo necesitas recordar cómo iba, esta lista te ahorra la lectura completa. Las fórmulas están en español de España, con punto y coma como separador de argumentos.

  • Sintaxis: =DESREF(referencia; filas; columnas; [alto]; [ancho]). Los tres primeros argumentos son obligatorios y los dos últimos, opcionales.
  • Dirección del desplazamiento: filas positivas bajan y negativas suben; columnas positivas van a la derecha y negativas, a la izquierda.
  • Una celda: =DESREF(A1;3;2) devuelve la celda C4. Con 0;0 te quedas en A1.
  • Un rango: =DESREF(A1;1;0;5;1) devuelve cinco filas por una columna a partir de una fila por debajo de A1, es decir, A2:A6.
  • Último valor de una columna: =DESREF($A$1;CONTARA($A:$A)-1;0).
  • Suma de los últimos 6 valores: =SUMA(DESREF($B$1;CONTARA($B:$B)-6;0;6;1)).
  • Rango dinámico con nombre: =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1).
  • Búsqueda desplazada: =DESREF($A$1;COINCIDIR("Producto B";$A:$A;0)-1;1).
  • Alternativa sin volatilidad para el último valor: =INDICE($A:$A;CONTARA($A:$A)).
  • Ancla fija: escribe la referencia de partida con $ para que no se mueva al copiar la fórmula.
  • Aviso permanente: DESREF es volátil y no reajusta sus desplazamientos si insertas o borras filas.
📌
La idea claveDESREF no calcula un valor: devuelve una referencia. Esa referencia es la que luego recoge otra función (SUMA, PROMEDIO, un gráfico, una lista desplegable) para trabajar con ella.

Piensa en DESREF como un GPS que devuelve direcciones de celda

Cuando escribes =A1 en otra celda, estás señalando un sitio fijo. Cuando escribes =DESREF(A1;3;2), en cambio, estás diciendo «sal de A1, baja tres filas, cruza dos columnas y dame lo que encuentres allí». El resultado es la misma celda C4 que habrías señalado a mano, pero el camino hasta ella queda en la fórmula. Y ese camino se puede calcular, que es justo lo que le da valor.

De ahí viene su parentesco lejano con INDICE y BUSCARV: las tres acaban entregando una celda, pero DESREF no busca nada. Es tu fórmula la que decide cuánto desplazarse, normalmente apoyándose en CONTARA para saber cuántas filas hay ocupadas o en COINCIDIR para localizar una posición. Por eso encaja tan bien en totales móviles, rangos que se adaptan al tamaño de una tabla y orígenes de datos que deben crecer solos.

Qué significa que sea una función volátil

DESREF pertenece a la familia de las funciones volátiles, igual que INDIRECTO, ALEATORIO o AHORA. Una fórmula normal se recalcula solo cuando cambia algo de lo que depende. Una volátil se recalcula cada vez que Excel recalcula cualquier cosa del libro, aunque sus celdas de origen no se hayan tocado.

En una hoja pequeña es imperceptible. En un cuadro de mandos con cientos o miles de fórmulas DESREF alimentando gráficos y tablas dinámicas, cada pulsación de tecla puede disparar un recálculo completo y el archivo empieza a ir a tirones. Si notas que Excel se queda pensando al escribir en una celda cualquiera, sospecha primero de las funciones volátiles.

Por qué se rompe con las inserciones de filas

Una referencia ordinaria como A1:A10 se ajusta sola cuando insertas o eliminas filas dentro del rango, porque Excel reescribe la dirección. En DESREF solo se ajusta el ancla; los desplazamientos (filas, columnas, alto y ancho) son números que Excel no toca. Si alguien inserta una fila entre el ancla y el destino, un =DESREF(A1;5;0) sigue contando cinco filas, pero ahora cinco filas más abajo ya no es la celda que querías. Lo peor es que no salta ningún error: la fórmula sigue dando un resultado, solo que equivocado.

Lo mismo ocurre con los rangos calculados con CONTARA sobre columnas con huecos. Cuanto más compleja es la hoja y más manos la editan, más probable es que un DESREF que funcionaba perfecto deje de tener sentido tras un cambio aparentemente inocente. La tabla compara el comportamiento de DESREF con sus alternativas habituales.

Opción¿Se ajusta al insertar o eliminar filas?¿Es volátil?Facilidad para auditarla
DESREFNo siempre: depende del desplazamiento calculado a manoSíBaja: hay que leer la fórmula para saber a qué apunta
INDICESí, se comporta como una referencia normalNoAlta
Tablas de ExcelSí, se expanden solas y las referencias estructuradas se mantienenNoAlta
Matrices dinámicas (@, #)Sí, el rango de derrame se adapta al tamaño real de los datosNoAlta
⚠️
Cuidado con el rendimientoSi tu libro acumula muchas fórmulas DESREF o INDIRECTO y notas que Excel tarda en recalcular, empieza la investigación por ellas antes de mirar cualquier otra cosa.

Elige la variante de DESREF que encaja con tu caso

Casi todos los usos reales caen en una de estas cinco familias. Mira cuál se parece más a lo que necesitas y copia la fórmula de ejemplo.

  • Último valor
  • Ventana móvil
  • Rango dinámico
  • Búsqueda desplazada
  • Cuándo evitarla

Variante 1: sacar el último dato de una columna que crece

Imagina una columna A en la que apuntas cada día un dato y quieres ver siempre el más reciente en una celda de resumen. La fórmula es =DESREF($A$1;CONTARA($A:$A)-1;0). CONTARA cuenta las celdas no vacías de la columna A, cabecera incluida; al restar 1, el desplazamiento cae exactamente sobre la última celda con dato.

  1. Fija la celda de partida, normalmente la cabecera o la primera fila de datos (por ejemplo, A1).
  2. Cuenta con CONTARA cuántas celdas ocupadas hay para saber cuántas filas bajar.
  3. Resta 1 si tu ancla es la primera celda con datos.
  4. Deja las columnas en 0, porque te quedas en la misma columna.
  5. Añade una fila de prueba: la fórmula debe moverse sola hasta ella.

Variante 2: sumar los últimos N valores

Para obtener la suma de las últimas 6 ventas o una media móvil de los tres últimos meses, combinas DESREF con SUMA o PROMEDIO usando el argumento alto: =SUMA(DESREF($B$1;CONTARA($B:$B)-6;0;6;1)). Traducido: «colócate seis filas antes del final y devuélveme un rango de seis filas de alto». SUMA hace el resto. Para otra ventana, cambia el 6 por los periodos que necesites, en las dos posiciones donde aparece.

Variante 3: crear un rango dinámico para gráficos y desplegables

Es el uso estrella. Defines un nombre, por ejemplo DatosVentas, en Fórmulas > Administrador de nombres con la fórmula =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1) y lo usas como origen de un gráfico (el paso a paso general está en cómo hacer un gráfico en Excel) o como origen de validación de datos para una lista desplegable en Excel que se autoexpande.

Frente a arrastrar el rango del gráfico cada mes, la ventaja es obvia: te olvidas de mantenerlo. El coste, el que ya conoces: ese nombre dinámico añade volatilidad al libro entero.

Variante 4: una búsqueda cuyo desplazamiento depende de lo que buscas

DESREF sola solo sabe moverse un número fijo de filas y columnas. Para que el salto dependa de un valor, la emparejas con COINCIDIR, que devuelve la posición de un dato dentro de un rango: =DESREF($A$1;COINCIDIR("Producto B";$A:$A;0)-1;1). COINCIDIR averigua en qué fila está «Producto B» y DESREF se desplaza hasta allí y recoge el dato de la columna contigua. Es prácticamente el patrón de INDICE y COINCIDIR, con la diferencia de que DESREF construye la referencia desde cero en lugar de indexar dentro de un rango que ya existe.

Variante 5: cuándo conviene cambiar a otra herramienta

Antes de escribir un DESREF nuevo, comprueba si alguna de estas tres opciones hace lo mismo con menos riesgo:

  • INDICE, no volátil y estable ante inserciones. Para el último valor de una columna, =INDICE($A:$A;CONTARA($A:$A)) hace lo mismo que el DESREF de la variante 1.
  • Una tabla de Excel (Ctrl+T): el rango crece al añadir filas y los gráficos o listas que apuntan a referencias estructurales como Tabla1[Ventas] se actualizan solos. Tienes el paso a paso en cómo dar formato como tabla.
  • Matrices dinámicas en Excel 365: funciones como ÚNICOS, FILTRAR o SECUENCIA se derraman solas y se referencian con el operador # (por ejemplo, A2#). Son más fáciles de leer y no son volátiles.
📌
Cuándo sí usar DESREFTiene sentido en libros heredados que ya la emplean, en versiones antiguas de Excel sin matrices dinámicas o en desplazamientos puntuales muy simples. Para rangos dinámicos nuevos, valora antes una tabla de Excel o una matriz dinámica.

Arregla los fallos típicos de DESREF uno a uno

Cuando DESREF falla, casi siempre es por una de estas cuatro causas. Léelas en orden: están ordenadas de más a menos frecuente.

¿Por qué me sale #¡REF!?

Porque el desplazamiento saca la referencia fuera de los límites de la hoja: restar más filas de las que hay por encima del ancla, irse a la izquierda de la columna A, o dejar alto o ancho en 0. Revisa el signo y la magnitud de filas y columnas, y si el desplazamiento sale de otra función, envuélvelo con MAX(0;...) para que nunca sea negativo.

¿Por qué da otro valor después de insertar una fila?

Porque el desplazamiento es un número que Excel no reajusta y ya no coincide con la posición que esperabas. La solución real es cambiar a INDICE o a una tabla; la provisional, revisar a mano el ancla y el desplazamiento tras cada inserción.

¿Por qué el libro va tan lento?

Hay demasiadas fórmulas volátiles (DESREF, INDIRECTO) recalculando en cada cambio. Sustituye las que puedas por INDICE, tablas o matrices dinámicas y deja DESREF solo donde no haya alternativa.

¿Por qué aparece #¡VALOR!?

Algún argumento (filas, columnas, alto o ancho) es texto o no es numérico. Comprueba que CONTARA u otra función auxiliar devuelve de verdad un número.

Si aun así no das con el origen, recorre esta lista de comprobación:

  1. Mira primero el signo de filas y columnas: un negativo de más te saca de la hoja.
  2. Verifica que la celda de referencia (el ancla) está bien fijada con $.
  3. Si alto o ancho vienen de una fórmula como CONTARA, asegúrate de que nunca puede valer 0 ni salir negativo.
  4. Si el error apareció justo después de editar la hoja, sospecha de una fila o columna insertada o eliminada.
💡
Números negativosNo les tengas miedo: =DESREF(C10;-2;-1) sube dos filas y retrocede una columna, y devuelve B8. Es tan válido como moverse hacia abajo o hacia la derecha.

Anota los cinco argumentos de DESREF en una ficha

Sintaxis
=DESREF(referencia; filas; columnas; [alto]; [ancho])
referencia
Celda o rango de partida. Obligatorio. Conviene fijarla con $.
filas
Cuántas filas se mueve: positivo hacia abajo, negativo hacia arriba. Obligatorio.
columnas
Cuántas columnas se mueve: positivo a la derecha, negativo a la izquierda. Obligatorio.
alto
Número de filas del resultado. Opcional.
ancho
Número de columnas del resultado. Opcional.
Devuelve
Una referencia (celda o rango), no un valor.
Volatilidad
Se recalcula con cada cambio del libro.

La referencia, tu punto de partida

Es la celda de anclaje desde la que Excel empieza a contar. Casi siempre conviene fijarla con referencias absolutas como $A$1 (si necesitas repasar el efecto del dólar, mira cómo fijar celdas en Excel), para que no se desplace sola si copias la fórmula a otras celdas. Todo el desplazamiento posterior se mide desde ese punto, así que si fallas aquí, la fórmula entera apunta al sitio equivocado.

Filas y columnas, el desplazamiento real

Son números que indican cuántas filas y columnas te alejas del ancla. =DESREF(A1;3;2) devuelve C4: tres filas hacia abajo y dos columnas a la derecha. Con 0;0 devolvería la propia A1, y con valores negativos retrocedes en lugar de avanzar.

Alto y ancho, cuando necesitas un rango

Si los dejas en blanco, DESREF devuelve una sola celda. Si indicas alto, ancho o ambos, devuelve un rango de ese tamaño que empieza en la celda desplazada. Con =DESREF(A1;1;0;5;1) obtienes A2:A6. Este es el truco que hay detrás de todos los rangos dinámicos.

Una nota para quienes trabajan fuera de Excel: en Google Sheets con la interfaz en español la función también se llama DESREF y se comporta igual, volatilidad incluida. Si abres la hoja con la interfaz en inglés, cambian el nombre de la función y los separadores pasan a ser comas (el desplazamiento de C4 se escribiría A1,3,2 en lugar de A1;3;2, y el final de la fórmula del último valor, -1,0 en lugar de -1;0), y hay que adaptar también las funciones auxiliares como CONTARA.

Lleva DESREF a un caso real con estas guías

Un rango que crece solo no sirve de mucho si no alimenta algo. Estas guías son el destino natural de lo que acabas de aprender.

Gráficos que se actualizan

Usa un nombre definido con DESREF como origen y el gráfico incorporará los datos nuevos sin retocarlo.

Listas desplegables que se expanden

Valida con un rango dinámico y las opciones nuevas aparecen al añadirlas a la lista.

INDICE y COINCIDIR

El sustituto más directo cuando buscas una posición sin pagar el precio de la volatilidad.

Formato como tabla

La vía más sencilla para que el rango crezca solo, sin una sola fórmula de desplazamiento.

La función ÚNICOS

Un ejemplo de matriz dinámica que se derrama con el tamaño exacto que necesita.

¿Tus datos crecen cada mes?

Nuestras plantillas ya traen los rangos y gráficos que se autoexpanden. Gratis.

Ver la plantilla →

Resuelve las dudas que suelen quedar sobre DESREF

¿En qué se diferencia DESREF de INDICE?
Las dos pueden entregar una celda o un rango a partir de una posición, pero DESREF es volátil y construye la referencia desde cero en cada recálculo, mientras que INDICE no es volátil y se ajusta como una referencia normal cuando insertas o eliminas filas. Para desarrollos nuevos suele recomendarse INDICE.
¿Por qué mi DESREF devuelve #¡REF!?
Porque el desplazamiento deja la referencia fuera de los límites de la hoja, por ejemplo al restar más filas de las que existen por encima del punto de partida.
¿Puedo usar DESREF para una lista desplegable que se amplíe sola?
Sí. Defines un nombre en el Administrador de nombres con un DESREF cuyo alto sale de CONTARA y lo usas como origen en Validación de datos. Al añadir elementos a la lista de origen, el desplegable los incorpora.
¿DESREF ralentiza siempre el libro?
No siempre. Al ser volátil se recalcula en cada cambio, pero en hojas pequeñas no se nota. En libros con muchas fórmulas DESREF o INDIRECTO que alimentan gráficos y tablas dinámicas la bajada de rendimiento sí es perceptible.
¿Existe DESREF en Google Sheets?
Sí. Con la interfaz en español se llama también DESREF; con la interfaz en inglés cambia el nombre y los argumentos suelen separarse con comas en lugar de punto y coma.
¿Qué le pasa a DESREF si inserto una fila en mitad de los datos?
Los desplazamientos son números que Excel no reajusta, así que insertar o eliminar filas entre el ancla y el destino puede dejar la fórmula apuntando a una celda distinta de la prevista, sin ningún aviso visible.
¿Cuándo uso DESREF en lugar de una tabla de Excel?
Casi nunca en trabajos nuevos. Para rangos que crecen, una tabla o una matriz dinámica es más robusta. DESREF queda para libros ya construidos con esa lógica o para versiones de Excel sin tablas ni matrices dinámicas.
¿Se puede combinar DESREF con SUMA o PROMEDIO?
Sí, es de lo más habitual: DESREF genera el rango (por ejemplo, los últimos 6 valores) y SUMA o PROMEDIO lo procesan, como en =SUMA(DESREF($B$1;CONTARA($B:$B)-6;0;6;1)).

Ten a mano el glosario mínimo de DESREF

  • Referencia
  • Ancla
  • Desplazamiento
  • Función volátil
  • Rango dinámico
  • Matriz dinámica

Referencia: la dirección de una celda o rango, no su contenido. Ancla: la celda de partida de DESREF, que conviene bloquear con $. Desplazamiento: el par de números (filas y columnas) que indica cuánto te mueves desde el ancla. Función volátil: la que se recalcula con cualquier cambio del libro. Rango dinámico: un rango cuyo tamaño o posición cambia solo según los datos. Matriz dinámica: resultado que se derrama por varias celdas y se ajusta al tamaño real de los datos.

Si te quedas con una sola idea, que sea esta: DESREF resuelve un problema muy concreto, generar una referencia que cambia de posición o de tamaño según parámetros que tú controlas, y lo paga con dos costes reales, la volatilidad y la fragilidad ante inserciones. Úsala cuando necesites de verdad ese desplazamiento calculado a mano y deja documentado desde dónde y cuánto se mueve, para que quien herede el libro lo entienda.

Excel® es una marca registrada de Microsoft Corporation. Este sitio no está afiliado a Microsoft.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *