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

Una hoja de ventas con los productos en la columna A es el ejemplo más típico de un libro que aún no se ha terminado. Hoy son siete, mañana diez, pasado quizá veinte, y cada vez que sumas una fila tienes que tocar la misma fórmula, el mismo desplegable y la misma lista del BUSCARV. Un rango dinámico es exactamente eso: una referencia a un rango que crece y se encoge solo, sin que tengas que editar nada a mano. En este tutorial verás cómo montarlo paso a paso con DESREF (OFFSET), con la pareja INDICE y CONTARA, y también con una tabla de Excel, que es la forma más cómoda cuando tu versión lo permite. Al terminar sabrás crear una lista desplegable que se rellena sola al añadir productos, conectar un BUSCARX (XLOOKUP) a esa lista y evitar los tres errores que la gente comete cuando se pone con ello. Funciona en Excel 2007 en adelante; la versión con tabla exige Excel 2007 o superior y la versión con INDICE y CONTARA también, mientras que BUSCARX y las matrices dinámicas solo viven en Excel 2021, 2024, Microsoft 365 y Excel para la web.

Qué es un rango dinámico y para qué sirve en Excel

Imagina la hoja Productos de tu tienda. En la columna A escribes los nombres: Lápiz, Cuaderno, Goma, Bolígrafo, Carpeta, etc., empezando en A2. En B2:B8 tienes los precios. La fórmula =SUMA(B2:B8) funciona, pero el día que añades Carpeta A4 en A9 y 3,20 en B9, la suma se queda en B2:B8 y se olvida del nuevo. Lo mismo pasa con la lista desplegable que uses en otra hoja para elegir el producto: si su Origen era $A$2:$A$8, el producto nuevo no aparecerá.

Un rango dinámico resuelve esto definiendo una sola vez una referencia que se ajusta al tamaño real de los datos. La idea es muy sencilla: en vez de decir "desde A2 hasta A8", le dices a Excel "desde A2 hasta la última celda escrita de la columna". El motor para encontrar esa última celda es, casi siempre, CONTARA (COUNTA), que cuenta cuántas celdas no están vacías en una columna.

Este mecanismo es la pieza clave de tres usos reales: alimentar listas desplegables que crecen sin tocarlas, servir como matriz_buscada y como matriz_devuelta en BUSCARX y, en general, cualquier fórmula que necesite saber "cuántos datos tengo". Sin un rango dinámico, cada fila nueva te obliga a editar la fórmula, lo cual es fuente de errores y de libros que se quedan desfasados.

Las piezas que componen un rango dinámico

La receta clásica usa dos ingredientes. El primero, DESREF (OFFSET), construye una referencia que se "mueve" desde un punto fijo; al combinarla con un contador de filas, su altura se adapta. El segundo, CONTARA (COUNTA), devuelve cuántas celdas con datos hay, que es lo que usamos como alto del rango. La alternativa moderna, disponible desde Excel 2021 y Microsoft 365, es INDICE (INDEX) junto a CONTARA, que cumple el mismo papel sin los inconvenientes de DESREF.

=DESREF($A$2;0;0;CONTARA($A$2:$A$100);1)

=INDICE(A:A;CONTARA(A:A))

Las dos devolvuelven, en la práctica, la última celda ocupada de la columna A; combinadas con el ancla A2 forman el rango completo. Más adelante verás cómo darles forma concreta al escribirlas en un nombre o dentro de una fórmula.

DESREF: la referencia que se mueve al sumar filas

DESREF toma una referencia de partida y la desplaza un número de filas y columnas; sus dos últimos argumentos, alto y ancho, dicen qué tamaño tiene el rectángulo que devuelve. La sintaxis es =DESREF(ref;filas;columnas;[alto];[ancho]). En Productos, partimos de $A$2 (anclada con dólares para que no se mueva al copiar la fórmula), le decimos que no se desplaza ni en filas ni en columnas (0;0), y le damos de alto lo que mida la lista: CONTARA($A$2:$A$100) y ancho 1. El resultado es el rango A2:A8 cuando hay siete productos y A2:A9 cuando añades el octavo. La función existe desde Excel 2007.

INDICE y CONTARA: la pareja estable

Otra forma de conseguir la última celda es =INDICE(A:A;CONTARA(A:A)). CONTARA cuenta los valores de la columna A y INDICE devuelve el contenido de la fila número n; juntos, te dan el último producto escrito. Para construir el rango completo lo más limpio es usarlo como tope en otra fórmula, o como segundo argumento de INDICE(:) con la posición de inicio. Esta pareja existe desde Excel 2007 y, a diferencia de DESREF, no es volátil.

Por qué DESREF vuelve lento el libro

DESREF es una función volátil: Excel la recalcula cada vez que cambia cualquier celda del libro, no solo cuando cambia su rango. En una hoja con cuatrocientos productos y tres tablas dinámicas eso se nota: el archivo "respira" al editar. La alternativa es cambiar a INDICE y CONTARA, que solo se recalcula cuando cambia algo de sus argumentos, o directamente pasar la lista a una tabla de Excel, que es el siguiente paso natural.

Dónde se escribe un rango dinámico

La forma más limpia no es escribir DESREF dentro de cada fórmula, sino guardarlo como un nombre definido, así lo reutilizas con su nombre (Productos) y las fórmulas se leen mucho mejor. Trabajaremos sobre la hoja Productos, con la lista en A2:A8 y una fórmula aparte que demuestra que el rango va cambiando.

Nombrar el rango para reutilizarlo

1. Selecciona la celda A2 para tener el punto de partida claro.
2. Abre Fórmulas > Nombres definidos > Definir nombre.
3. Escribe Productos en el cuadro Nombre.
4. En "Se refiere a", escribe la fórmula completa.
5. Pulsa Aceptar.

Comprobar el rango con SUMA o CONTARA

Ahora que Productos es un nombre, úsalo como si fuera un rango: =SUMA(B2:B8) se convierte en =SUMA(Productos*...) no, mejor probar de otra forma. La prueba más clara es =CONTARA(Productos), que debe devolver el número de productos escritos.

Si añades Regla 30 cm en A9 y 1,10 en B9, verás que D2 pasa automáticamente de 7 a 8 sin tocar la fórmula: el nombre Productos ya apunta a A2:A9.

Crear una lista desplegable con un rango fijo

El uso más cotidiano del rango dinámico es alimentar una lista desplegable. Primero verás el problema con un Origen fijo: si la lista de la columna A crece, la lista desplegable no se entera. Luego lo arreglas en un paso.

1. Selecciona B2:B20 en la hoja Ventas.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Lista.
4. En Origen escribe $A$2:$A$8 y pulsa Aceptar.

Ahora haz la prueba: añade Regla 30 cm en A9 de la hoja Productos, ve a Ventas!B2 y despliega la lista. Verás que Regla 30 cm no aparece, porque el Origen se quedó en $A$2:$A$8. Eso es justo lo que arregla el rango dinámico.

Una lista desplegable que crece con DESREF

1. Vuelve a seleccionar B2:B20 en Ventas.
2. Abre de nuevo Datos > Herramientas de datos > Validación de datos.
3. Cambia el Origen por =Productos (el nombre que creaste antes) y pulsa Aceptar.

Repite la prueba: añade otro producto en Productos!A10 (por ejemplo, Grapas) y al desplegar Ventas!B2 lo verás al final de la lista. El nombre Productos se ha redimensionado solo gracias a DESREF y CONTARA.

Una lista desplegable con la tabla de Excel

Desde Excel 2007 existe una forma aún más cómoda: convertir la lista en una **tabla de Excel. Excel le pone nombre (Tabla1` por defecto) y gestiona el tamaño por ti.

1. Selecciona A1:B8 en Productos.
2. Pulsa Ctrl+T para crear la tabla.
3. Confirma que la tabla tiene encabezados y pulsa Aceptar.
4. En la validación, cambia el Origen a =Tabla1[Producto].

Una tabla de Excel también crece sola: añadir filas dentro de la tabla las incorpora al rango, y el Origen =Tabla1[Producto] lo refleja automáticamente. Si tu Excel es 2007 o superior, esta es la opción más limpia para una lista desplegable; DESREF con nombre sigue siendo útil cuando no puedes usar tablas (por ejemplo, listas en libros de terceros).

Rango dinámico en una tabla dinámica o un BUSCARX

El rango dinámico también entra en fórmulas. El caso más útil es alimentar un BUSCARX (XLOOKUP): en vez de fijar la matriz_buscada y la matriz_devuelta, las nombras para que crezcan con la lista.

Antes de escribir esta fórmula tienes que crear un segundo nombre, Precios, con la misma estructura que Productos pero apuntando a la columna B:

=DESREF($B$2;0;0;CONTARA($B$2:$B$100);1)

BUSCARX está en Excel 2021, 2024, Microsoft 365 y Excel para la web. En Excel 2019 o anteriores muestra #¿NOMBRE?; si trabajas con una versión antigua, sustitúyelo por BUSCARV (VLOOKUP) o por la pareja INDICE y COINCIDIR (MATCH), que verás en la siguiente sección.

Cuándo usar DESREF y cuándo usar una tabla

Tres opciones razonables, según el caso. Si tu Excel es 2021 o posterior y solo consumes la lista desde fórmulas, usa INDICE y CONTARA o una tabla: el cálculo no penaliza el rendimiento. Si el libro pasa por manos de gente con Excel 2010 o 2013, quédate con DESREF y un nombre definido: es compatible con todo. Si la lista se usa sobre todo para alimentar una lista desplegable y nada más, ve directo a una tabla y olvídate del rango dinámico manual.

Por qué mi rango dinámico falla o no crece

Tres tropiezos típicos al trabajar con este tipo de rango: una celda vacía en mitad de la lista, un nombre que apunta a una hoja inexistente y un libro que se vuelve lento por culpa de DESREF.

La lista incluye una celda vacía y añade blancos

CONTARA cuenta celdas no vacías, pero si dejas una fila en blanco entre dos productos, la siguiente fila escrita no estira el rango como esperas: DESREF se queda corto y la lista desplegable puede mostrar una opción vacía al final. Para evitarlo, usa un tope fijo (CONTARA($A$2:$A$1000)) en vez de la columna entera, o sustituye el CONTARA por CONTARA($A:$A)-1 si sabes que la primera fila de datos siempre está en A2.

El nombre Productos da #¡REF! al abrir el libro

Si abres el libro en otro equipo y la hoja Productos fue renombrada o movida, el nombre apuntará a una hoja que ya no existe. Verás #¡REF! al usar el nombre desde una fórmula. Arréglalo desde Fórmulas > Nombres definidos > Administrador de nombres: edita Productos, revisa "Se refiere a" y corrige la referencia para que apunte a la hoja actual.

DESREF se recalcula con cada cambio y el libro va lento

Si notas que el archivo "responde tarde" al editar cualquier celda, y sabes que tienes varias fórmulas con DESREF, ahí está el origen: cada cambio en cualquier hoja dispara el recálculo de todos los rangos dinámicos basados en DESREF. Sustituye por INDICE y CONTARA o, mejor aún, convierte la lista en una tabla de Excel. La diferencia se nota incluso en hojas con cien filas.

Lo que ya sabes hacer con un rango dinámico

• Defines un rango que crece y se encoge solo con DESREF y un nombre, sin tocar la fórmula cada vez que añades filas.
• Usas INDICE y CONTARA como alternativa estable cuando DESREF te ralentiza el libro.
• Creas una lista desplegable que reconoce los productos nuevos en cuanto los escribes, ya sea con un nombre, con una tabla de Excel o con =Tabla1[Producto].
• Conectas un BUSCARX (XLOOKUP) a un rango dinámico para que las búsquedas no se queden obsoletas al ampliar la lista.
• Detectas y arreglas los tres fallos más comunes: blancos dentro de la lista, nombres que apuntan a una hoja renombrada y el coste de DESREF en libros grandes.

El siguiente paso natural es convertir la lista de productos en una tabla de Excel (Ctrl+T) y reemplazar el nombre Productos por la referencia estructurada Tabla1[Producto]. Es el mismo resultado que has visto aquí, pero sin mantener DESREF a mano y sin los problemas de rendimiento que arrastra.