Rango dinámico en Excel: qué es y cómo crearlo

Imagina una hoja de Ventas con productos en la columna A e importes en la columna B. Cuando llega una fila nueva, el total de abajo se queda corto, el gráfico deja un espacio en blanco y, si copiaste la fórmula a mano, salta un #¡REF!. La solución es un rango dinámico, que crece y se encoge según los datos reales de la hoja. Al terminar este tutorial sabrás crear uno con DESREF (OFFSET) y con INDICE (INDEX) + CONTARA (COUNTA), lo guardarás en un nombre definido para reutilizarlo, y harás que una lista desplegable recoja las categorías nuevas sin tocarla. Funciona desde Excel 2007 en adelante; en 2021 y Microsoft 365, además, tienes Tabla y FILTRAR (FILTER) como alternativas modernas.

Qué es un rango dinámico y por qué lo necesitas

Una hoja de Ventas con productos en A e importes en B es el ejemplo perfecto. Llegas un día, escribes la fila 12 con un importe nuevo, y el total de abajo sigue siendo el mismo de ayer porque la fórmula =SUMA(B2:B11) solo llegaba hasta la fila 11. Lo mismo le pasa al promedio, al máximo, a una lista desplegable que se queda corta o a un cuadro con datos complejos que pierde las últimas filas. Un rango dinámico es un rango cuyo tamaño lo decide el contenido real de la hoja: si añades una fila, el rango crece; si borras, se encoge. Sirve para cualquier fórmula que reciba un rango: SUMA, MAX, PROMEDIO, BUSCARV, una Validación de datos, el rango de un gráfico.

La opción "clásica" y por qué falla

El truco clásico es reservar más filas de las que tienes: =SUMA(B2:B1000). Funciona, pero reserva sitio de más, descuadra un PROMEDIO porque divide entre 999 aunque tengas 10 importes, y un gráfico basado en ese rango sale con una línea plana larguísima hasta la fila 1000. La fórmula no se entera de cuántos datos hay de verdad.

La idea del rango dinámico

Que el tamaño del rango lo decida el contenido real de la hoja. Se hace con DESREF, con INDICE + CONTARA, con una Tabla de Excel o con un nombre definido que apunte a la fórmula. El resto del tutorial lo construye sobre la hoja de Ventas del ejemplo.

Las cuatro piezas que construyen un rango dinámico

Cualquier rango dinámico se apoya en cuatro piezas que se combinan según el caso. Una es la Tabla de Excel, que ya crea rangos con nombre que crecen al añadir filas; útil cuando los datos son una lista limpia. Otra es DESREF (OFFSET), que devuelve un rango que se "mueve" desde una referencia: tú le das el punto de partida, cuántas filas bajar, cuántas columnas desplazar y qué alto/ancho quieres. La tercera es INDICE (INDEX) + CONTARA (COUNTA), la alternativa sin volatilidad: INDICE devuelve una celda concreta, y con dos INDICE separados por dos puntos construyes un rango. La cuarta son los nombres definidos, que guardan una fórmula larga bajo un nombre corto para reutilizarla en otras fórmulas y herramientas.

Convertir los datos en Tabla

Una Tabla de Excel (no una tabla de datos ni una tabla dinámica) ya es un rango dinámico. Al crear Tabla con Insertar > Tablas > Tabla y escribir una fila nueva debajo, la tabla se estira sola. Y trae las referencias estructuradas: Ventas[Importe]. Si tu hoja es una lista limpia, con la Tabla basta y no necesitas DESREF.

DESREF (OFFSET): un rango que se mueve

DESREF devuelve un rango a partir de una referencia. La sintaxis es =DESREF(ref; filas; columnas; [alto]; [ancho]). Para una columna con encabezado en A2 y datos debajo, =DESREF($A$2;0;0;CONTARA($A:$A)-1;1) devuelve desde A2 hasta la última celda no vacía de la columna A. Su pega es que es volátil, se vuelve a calcular con cualquier cambio del libro y en libros grandes lo notas.

INDICE y CONTARA: la alternativa sin volatilidad

=INDICE($A:$A;2):INDICE($A:$A;CONTARA($A:$A)) hace lo mismo: empieza en la fila 2 y termina en la última con datos. INDICE no se recalcula con cada cambio, así que es más ligero que DESREF en hojas con muchas fórmulas.

Nombres de rango que apuntan a la fórmula

En vez de escribir la fórmula larga dentro de cada SUMA, la guardas en un nombre definido (Fórmulas > Nombres definidos > Definir nombre) y usas el nombre. Si la fórmula cambia, la cambias en un sitio y todas las que la usan se actualizan.

Total, promedio y máximo que crecen con tus datos

Hoja de ejemplo. Tienes una hoja llamada Ventas con encabezado en A1:B1 y diez filas de datos en A2:B11. En B13 quieres el total. La fórmula clásica =SUMA(B2:B11) se queda corta al añadir la fila 12. Vas a crear un nombre llamado Ventas que cubra solo las filas con datos.

1. Abre tu libro de Ventas con encabezado en A1:B1 y datos en A2:B11. Asegúrate de que la columna B son los importes (números).
2. Crea el nombre: Fórmulas > Nombres definidos > Definir nombre. Escribe Nombre = Ventas y Se refiere a = =DESREF($B$2;0;0;CONTARA($B:$B)-1;1). Acepta.
3. En B13 escribe =SUMA(Ventas) y pulsa Entrar. Verás la suma de los diez datos.
4. Añade una fila nueva en A12:B12 con un producto y su importe. Verás que B13 se actualiza sola.

Suma total con un rango dinámico

La fórmula clave es =SUMA(Ventas). El nombre Ventas apunta a =DESREF($B$2;0;0;CONTARA($B:$B)-1;1). CONTARA cuenta las celdas con algo (texto o número) en toda la B; como la fila 1 es el encabezado de texto, CONTARA devuelve 11 cuando hay 10 importes; se resta 1 para compensar.

Promedio y máximo sobre el mismo nombre

=PROMEDIO(Ventas) y =MAX(Ventas) usan el mismo nombre y se comportan igual: crecen al añadir filas. Sin rango dinámico, =MAX(B2:B11) ignoraría el importe nuevo de la fila 12.

Con DESREF y CONTARA

La fórmula del nombre es =DESREF($B$2;0;0;CONTARA($B:$B)-1;1). CONTARA cuenta toda la columna B, así que se resta 1 para no incluir el encabezado de la fila 1.

Con INDICE y CONTARA

Si te preocupa la volatilidad de DESREF, cambia el nombre a =INDICE($B:$B;2):INDICE($B:$B;CONTARA($B:$B)). Hace lo mismo, no es volátil y va mejor en libros con miles de fórmulas.

Una lista desplegable que incluye las categorías nuevas

Tienes una hoja Categorías con encabezado en A1 y valores en A2:A5 (Electrónica, Hogar, Ropa, Juguetes). En la hoja Ventas, una celda con Validación de datos debe mostrar esa lista. Al añadir una categoría nueva en A6, la lista desplegable tiene que recogerla sola.

1. En Hoja1 escribe el encabezado Categoría en A1 y los valores Electrónica, Hogar, Ropa, Juguetes en A2:A5.
2. Crea el nombre: Fórmulas > Nombres definidos > Definir nombre. Nombre = Categorías, Se refiere a = =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1). Acepta.
3. En la hoja Ventas, selecciona la celda donde quieres la lista desplegable (por ejemplo D2).
4. Abre Validación de datos: Datos > Herramientas de datos > Validación de datos. En Permitir elige Lista y en Origen escribe =Categorías. Acepta.
5. Ve a Hoja1 y añade "Deportes" en A6. Vuelve a Ventas, abre la lista de D2 y verás Deportes sin tocar el Origen.

Crear el nombre Categorías con DESREF

La fórmula del nombre es =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1). Igual que antes: CONTARA devuelve 5 con 4 datos, se resta 1, el rango cubre A2:A5. Al escribir A6, CONTARA pasa a 6 y el rango pasa a A2:A6.

Apuntar la Validación de datos al nombre

El Origen de la Validación de datos puede ser un rango con el signo igual delante: =Categorías. Excel lo trata como un nombre y lo trata solo.

Por qué el rango dinámico deja de actualizarse

Cuatro tropiezos típicos con DESREF y CONTARA, todas parecidos con el mismo culpable: CONTARA no cuenta lo que no es un dato.

Hay celdas vacías entre los datos

CONTARA no cuenta vacías, así que si la fila 6 está vacía entre las filas 5 y 7, CONTARA devuelve 9 en vez de 10 y el rango termina en B10, dejando fuera B11. La solución es limitar CONTARA a un rango que sepas que tiene datos: =DESREF($B$2;0;0;CONTARA($B$2:$B$1000);1), o pasar los datos a una Tabla.

El encabezado se cuenta como dato

CONTARA cuenta cualquier celda con algo, texto también. Si el encabezado es la palabra Importe, CONTARA devuelve 11 con 10 importes. Por eso se resta 1. Si en vez de restar 1 se te olvida, el rango se pasa una fila y el total descuadra. Revisa que la primera fila sea texto (el encabezado) y que los datos empiecen en la fila 2.

Filas de más o de menos tras borrar

Al borrar una fila intermedia, CONTARA baja y el rango se acorta, dejando huérfanas a las filas nuevas que están por debajo. Al borrar, CONTARA puede no bajar lo esperado. La solución más robusta es convertirlos en Tabla y usar referencias estructuradas como Ventas[Importe], que crecen y se encogen solas sin escribir DESREF.

Cuándo no conviene un rango dinámico con DESREF

DESREF es volátil: se recalcula con cualquier cambio del libro, incluso con cambios que no le afectan. En un libro con miles de fórmulas DESREF, abrir un cuadro con datos complejos o cambiar una sola celda puede notarse. En Excel 2021 y Microsoft 365, además, FILTRAR (FILTER) y las matrices dinámicas hacen lo mismo sin DESREF. La opción recomendada hoy es: convierte los datos a Tabla (Insertar > Tablas > Tabla) y usa las referencias estructuradas como Ventas[Importe]. La propia Tabla crece al añadir filas, no es volátil y, además, te regala filtros y estilos. FILTRAR existe desde Excel 2021; en Excel 2019, 2016 y 2013, la opción recomendada es Tabla + referencias estructuradas, no DESREF.

Lo que ya sabes hacer con rangos dinámicos

• Elegir entre Tabla, INDICE + CONTARA o DESREF según el caso: Tabla para listas limpias, INDICE + CONTARA para evitar la volatilidad, DESREF solo cuando lo necesites de verdad.
• Crear un nombre definido (Fórmulas > Nombres definidos > Definir nombre) que apunte a la fórmula y usarlo en cualquier sitio.
• Apuntar la Validación de datos a un nombre para que la lista desplegable crezca sola al añadir categorías.
• Usar referencias estructuradas como Ventas[Importe] como atajo moderno que ya es un rango dinámico sin escribir DESREF.

El siguiente paso es combinar el rango dinámico con un gráfico: crea el gráfico sobre la Tabla o sobre el rango con nombre, y al añadir filas el gráfico se estira solo sinhuecos ni líneas planas. Si tu versión es 2021 o Microsoft 365, prueba FILTRAR sobre la Tabla para hacer resúmenes que crezcan solos.