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.
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.

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)
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. Con0;0te 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.
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 |
|---|---|---|---|
| DESREF | No siempre: depende del desplazamiento calculado a mano | Sí | Baja: hay que leer la fórmula para saber a qué apunta |
| INDICE | Sí, se comporta como una referencia normal | No | Alta |
| Tablas de Excel | Sí, se expanden solas y las referencias estructuradas se mantienen | No | Alta |
| Matrices dinámicas (@, #) | Sí, el rango de derrame se adapta al tamaño real de los datos | No | Alta |
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.
- Fija la celda de partida, normalmente la cabecera o la primera fila de datos (por ejemplo, A1).
- Cuenta con CONTARA cuántas celdas ocupadas hay para saber cuántas filas bajar.
- Resta 1 si tu ancla es la primera celda con datos.
- Deja las columnas en 0, porque te quedas en la misma columna.
- 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.
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:
- Mira primero el signo de filas y columnas: un negativo de más te saca de la hoja.
- Verifica que la celda de referencia (el ancla) está bien fijada con $.
- Si alto o ancho vienen de una fórmula como CONTARA, asegúrate de que nunca puede valer 0 ni salir negativo.
- Si el error apareció justo después de editar la hoja, sospecha de una fila o columna insertada o eliminada.
=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.
Usa un nombre definido con DESREF como origen y el gráfico incorporará los datos nuevos sin retocarlo.
Valida con un rango dinámico y las opciones nuevas aparecen al añadirlas a la lista.
El sustituto más directo cuando buscas una posición sin pagar el precio de la volatilidad.
La vía más sencilla para que el rango crezca solo, sin una sola fórmula de desplazamiento.
Un ejemplo de matriz dinámica que se derrama con el tamaño exacto que necesita.
Nuestras plantillas ya traen los rangos y gráficos que se autoexpanden. Gratis.
Resuelve las dudas que suelen quedar sobre DESREF
¿En qué se diferencia DESREF de INDICE?
¿Por qué mi DESREF devuelve #¡REF!?
¿Puedo usar DESREF para una lista desplegable que se amplíe sola?
¿DESREF ralentiza siempre el libro?
¿Existe DESREF en Google Sheets?
¿Qué le pasa a DESREF si inserto una fila en mitad de los datos?
¿Cuándo uso DESREF en lugar de una tabla de Excel?
¿Se puede combinar DESREF con SUMA o PROMEDIO?
=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.