Rango dinámico en Excel: cómo crearlo con DESREF y con tablas

Un rango dinámico es un rango de celdas que se ajusta solo cuando añades o quitas datos, sin tener que tocar las fórmulas a mano. En este tutorial vas a montar una hoja de ventas que crece cada semana y verás cómo las fórmulas, las listas desplegables y un BUSCARV (en inglés VLOOKUP) siguen funcionando al añadir filas. El contenido vale para Microsoft 365, Excel 2024, 2021, 2019 y 2016; las tablas de Excel como tales existen desde 2007, y DESREF (OFFSET) y CONTARA (COUNTA) desde hace décadas. Cuando veas un punto y coma entre argumentos, es la configuración con coma decimal; si tu Excel usa punto decimal, el separador será la coma.

Qué es un rango dinámico y por qué tu hoja lo necesita

Imagina una hoja de ventas en la que cada lunes apuntas una fila nueva: la fecha y el importe del día. La primera semana el rango va de la fila 2 a la 6; al mes siguiente ya tienes datos hasta la fila 30. Si en otra celda escribiste =SUMA(B2:B6), esa suma se quedó congelada el día que la tecleaste: la fila 7, la 15 y la 30 no se cuentan. Tienes que abrir la fórmula, mirar dónde acababan los datos y ampliar el rango a mano. Y si además tienes un BUSCARV que busca precios, o una lista desplegable que muestra los productos, el problema se repite: cualquier rango fijo se queda corto.

Un rango dinámico hace ese trabajo por ti. Se "estira" o se "encoge" cuando añades o borras filas, y las fórmulas que lo usan siguen dando el resultado correcto sin que toques nada. En una hoja de ventas esto significa que la suma del mes, la lista de productos y la búsqueda del precio se actualizan solas cada vez que llega una fila nueva. Es la diferencia entre una hoja que se queda obsoleta en una semana y una que sigue funcionando mientras le sumes datos.

Para llegar a eso, Excel ofrece tres caminos, que veremos en la siguiente sección: las tablas oficiales de Excel, la pareja DESREF + CONTARA con un nombre de rango, y las tablas dinámicas para informes más elaborados.

Las tres formas de conseguirlo en Excel

Tablas oficiales: la forma más sencilla

Convertir el rango en Tabla (con la herramienta Insertar > Tablas > Tabla) es el camino más corto. La Tabla es un objeto de Excel con nombre, con su propia pestaña Herramientas de tabla cuando la seleccionas, y todas las fórmulas que escribas con referencia estructurada —por ejemplo =SUMA(Ventas[tabla!Importe])— apuntan a la columna, no a unas celdas concretas. Cuando añades una fila debajo, la Tabla la absorbe y la fórmula suma la fila nueva sin que la toques. Lo único que pierdes al convertir son formatos personalizados fuera de la Tabla: el estilo de filas a bandas y los filtros automáticos se activan solos.

DESREF y CONTARA: el rango que cuenta filas

La fórmula clásica para un rango que crece es:

=DESREF(A2;0;0;CONTARA(A:A)-1;1)

Despiece:

• A2 es la celda de arranque, la primera con datos justo debajo de la cabecera.
• 0;0 son los desplazamientos en filas y columnas: nos quedamos en el mismo sitio.
• CONTARA(A:A)-1 cuenta cuántas celdas no vacías hay en la columna A y resta 1 para no contar la cabecera.
• 1 es el ancho, una sola columna.

El resultado es un rango que empieza en A2 y termina en la última celda escrita de la columna A. Envolviendo esa expresión con un nombre (lo vemos en el siguiente apartado) y usando el nombre en las fórmulas, todas las celdas que dependan de él se actualizan solas.

Nombres de rango con DESREF para listas y BUSCARV

La fórmula anterior se queda en una celda; para que sirva en varias fórmulas a la vez, guárdala como nombre definido. Ve a Fórmulas > Nombres definidos > Definir nombre, dale un nombre descriptivo (por ejemplo datosImporte) y pega la fórmula de DESREF en el campo "Se refiere a". A partir de ahí, cualquier fórmula, validación de datos o tabla dinámica puede usar datosImporte como si fuera un rango con nombre, y se actualizará al añadir filas. Es la forma clásica de tener una lista desplegable o un BUSCARV que no se rompen.

Crear tu primera tabla dinámica de datos

Empezamos con una hoja de ventas semanal. Tienes seis días apuntados y vas a convertir el rango en Tabla para que la suma se actualice sola cuando llegue la fila 8.

Convertir el rango en Tabla

1. Selecciona el rango A1:B7 con el ratón.
2. Abre Insertar > Tablas > Tabla.
3. En el cuadro de diálogo, confirma el rango y marca la casilla "La tabla tiene encabezados".
4. Pulsa Aceptar. Aparece la pestaña Herramientas de tabla con su cinta propia; la tabla se llama Tabla1 por defecto, pero el nombre se ve en la esquina superior izquierda y se puede cambiar.

Una fórmula que suma la columna Importe

Escribes en D1 la fórmula con referencia estructurada y Excel la entiende como "la columna Importe de la tabla, entera".

=SUMA(Ventas[Importe])

Ahora añade una fila más para ver cómo se comporta:

1. Ve a la celda A8 y escribe 07/10/2026.
2. En B8 escribe 175,50.
3. Pulsa Entrar.

La fila 8 se incorpora sola a la tabla (verás que el formato a bandas continúa) y la fórmula de D1 sube a 876,25 sin tocarla.

Crear un nombre con DESREF y CONTARA

Ahora vas a hacer lo mismo pero con un nombre definido, que te servirá después para el BUSCARV.

1. Abre Fórmulas > Nombres definidos > Definir nombre.
2. En Nombre escribe datosImporte.
3. En Se refiere a, escribe la fórmula siguiente (apunta a la columna Importe de la hoja Ventas, sin la cabecera).
4. Pulsa Aceptar.
5. En cualquier celda, por ejemplo D2, escribe =SUMA(datosImporte) y comprueba que da el mismo resultado que la fórmula con referencia estructurada.

=DESREF(Ventas!$B$2;0;0;CONTARA(Ventas!$B:$B)-1;1)

Listas desplegables y BUSCARV que no se rompen

Tienes en otra hoja, llamada "Productos", la lista de códigos y precios. La idea es que la lista desplegable de otra hoja incluya los productos nuevos automáticamente y que el BUSCARV encuentre el precio del código elegido, aunque la tabla crezca.

La lista desplegable crece sola

Conviertes primero el rango de productos en Tabla, con el mismo procedimiento del apartado anterior, para que la Validación de datos pueda usar el nombre de la tabla como origen.

1. En la hoja Productos, selecciona A1:B6 y conviértelo en Tabla (Insertar > Tablas > Tabla, con encabezados).
2. La tabla se llamará Tabla1 por defecto; si tu hoja ya tiene otra, renómbrala a Productos desde la pestaña Herramientas de tabla > Propiedades > Nombre de la tabla.
3. Ve a la hoja donde quieras la lista desplegable, por ejemplo una hoja "Pedidos", y selecciona la celda E2.
4. Abre Datos > Herramientas de datos > Validación de datos.
5. En Permitir, elige Lista.
6. En Origen escribe =Productos[Código] (la columna Código de la tabla) y acepta.

BUSCARV sobre un nombre dinámico

En la misma hoja Pedidos, en F2 escribes la fórmula que busca el precio del código elegido en E2. Como el rango de búsqueda es una Tabla, la referencia estructurada protege a la fórmula frente a filas nuevas.

=BUSCARV(E2;Productos;2;FALSO)

Detrás de Productos Excel entiende "toda la tabla Productos". El 2 indica que quieres la segunda columna (Precio) y FALSO, que la coincidencia sea exacta.

Ahora añade el código P-600 en Productos, ve a Pedidos y elige P-600 en la lista de E2: la celda F2 muestra el precio nuevo sin que hayas tocado la fórmula.

Errores típicos al montar un rango dinámico

El rango se queda corto al añadir datos

La fórmula =DESREF(A2;0;0;CONTARA(A:A)-1;1) asume que la cabecera está en A1 y los datos empiezan en A2. Si la columna tiene la cabecera en la fila 1 y los datos en la fila 2, CONTARA(A:A) cuenta también la cabecera: si tienes 6 datos, devuelve 7 y, al restar 1, da 6 (correcto). El error aparece si la cabecera está en otra fila o si la columna tiene celdas en blanco: CONTARA devuelve menos de lo esperado y el rango se queda corto. El arreglo más fiable es usar siempre CONTARA($A:$A)-1 y arrancar DESREF en $A$2 para que la resta de la cabecera cuadre siempre.

#¡REF! al mover o borrar la columna

Si al definir el nombre escribiste =DESREF($A$1;0;0;CONTARA($A:$A);1), el rango empieza en la cabecera y la incluye. Cuando borras la columna A o la mueves a otro sitio, las referencias absolutas se rompen y aparece #¡REF! en todas las celdas que usen ese nombre. Ajusta la fórmula a =DESREF($A$2;0;0;CONTARA($A:$A)-1;1) y comprueba que, al renombrar la hoja, el nombre se actualiza solo. La versión con Tabla oficial no tiene este problema: las referencias estructuradas apuntan al nombre de la tabla, no a la columna.

La hoja se vuelve lenta al cambiar celdas

DESREF es volátil: cualquier edición del libro fuerza un recálculo. En una hoja con cinco o diez fórmulas no pasa nada; con cientos de DESREF anidados en una hoja con 50.000 filas, abrir y guardar el archivo se nota lento. La solución depende del caso: si solo necesitas que la fórmula siga al rango, convierte los datos en Tabla y usa referencia estructurada; si necesitas un argumento de tipo rango para una función que no acepta tablas, recurre a INDIRECTO (INDIRECT) con un nombre de rango, aunque INDIRECTO también es volátil; la versión más rápida es un rango con nombre fijo y un recordatorio de ampliarlo a mano cuando cambien los datos.

Lo que ya sabes hacer con un rango dinámico

• Convertir un rango de datos en Tabla oficial con Insertar > Tablas > Tabla, y escribir fórmulas con referencia estructurada como =SUMA(Ventas[tabla!Importe]) que sigan las filas nuevas.
• Crear un nombre de rango con DESREF y CONTARA para usarlo en Validación de datos, en BUSCARV o en cualquier fórmula que necesite un argumento de tipo rango.
• Montar una lista desplegable basada en una Tabla para que los productos nuevos aparezcan solos al añadirlos a la lista.
• Escribir un BUSCARV sobre una Tabla para que el precio se encuentre aunque la lista crezca con el tiempo.
• Detectar cuándo un rango se queda corto por un CONTARA mal calculado, cuándo aparecen #¡REF! por una referencia a la fila de cabecera, y cuándo la hoja se vuelve lenta por funciones volátiles.

El siguiente paso natural es convertir esa misma hoja de ventas en una tabla dinámica (Insertar > Tablas > Tabla dinámica) para agrupar los importes por mes, por producto o por cualquier otro campo, y así pasar del rango dinámico a un informe interactivo que también se ajusta al crecer los datos.

Dudas al montar un rango dinámico en Excel