Rango dinámico con la cámara de Excel: qué es y cómo se usa

Una hoja de ventas a la que cada semana le sumas una fila tiene un problema clásico: las fórmulas se quedan cortas y los gráficos dejan de mostrar los datos nuevos. En este tutorial vas a aprender a convertir ese rango fijo en un rango dinámico: una «ventana» que Excel estira sola cuando escribes debajo. Lo harás de dos maneras, con la función DESREF (en inglés OFFSET) y con las Tablas de Excel, y verás el resultado en un gráfico, en un BUSCARX (en inglés XLOOKUP) y en un SUMAR.SI.CONJUNTO (en inglés SUMIFS). Sirve en Microsoft 365, Excel 2024, 2021, 2019, 2016 y 2013; BUSCARX y las matrices dinámicas solo desde 2021.

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

Imagina que tu hoja registra las ventas de cada día: la fecha en la columna A y el importe en la B. El lunes escribes la fila 2, el martes la 3, y así hasta la 13 con los datos de enero. Tienes un SUMA que cubre los importes y un gráfico de líneas que muestra la evolución. El problema aparece el 1 de febrero: añades la fila 14 y el gráfico sigue terminando el día 13, mientras el SUMA devuelve el total de enero y se olvida del día nuevo.

El problema de los rangos fijos cuando los datos crecen

La fórmula =SUMA(B2:B13) está «escrita con números». Apunta a unas celdas concretas y, si los datos se mueven o aparecen filas nuevas, no se entera. Lo mismo pasa con el rango de datos del gráfico: cuando lo creaste seleccionaste A2:B13 y Excel guardó esa dirección fija. Cada vez que crece la hoja tienes que seleccionar el nuevo rango, copiar la fórmula una fila más abajo y volver a configurar el gráfico. No es un sistema, es un parche.

La idea de un rango que se ajusta solo al añadir datos

Un rango dinámico es una «dirección inteligente» que se calcula cada vez que Excel recalcula: en lugar de decir «voy de B2 a B13», dice «voy desde B2 hacia abajo, tantas filas como datos haya». Si hoy hay 13 filas, ocupa B2:B13; mañana, cuando escribas la fila 14, ocupa B2:B14 sin que toques la fórmula. Esa dirección inteligente se construye con la función DESREF, o se obtiene gratis cuando conviertes los datos en una Tabla.

Las dos piezas del rango dinámico en Excel

Cualquier rango dinámico se apoya en dos piezas: un nombre definido (una etiqueta que apunta a un rango y se reutiliza en fórmulas y gráficos) y una referencia variable, es decir, una expresión cuyo tamaño cambia según los datos. Las dos referencias variables más usadas son DESREF y las que crea Excel al convertir un rango en Tabla.

Los nombres definidos se gestionan desde la cinta: en la pestaña Fórmulas, dentro del grupo Nombres definidos, está el Administrador de nombres, que muestra todos los nombres del libro y permite crear, editar o borrar.

DESREF: una referencia que se mueve a tu alrededor

DESREF (en inglés OFFSET) no devuelve un valor, devuelve una referencia: un rectángulo de celdas. Su sintaxis es =DESREF(ref; filas; columnas; [alto]; [ancho]). El primer argumento, ref, es la celda ancla, el punto de partida. Los dos siguientes, filas y columnas, indican cuántos pasos te mueves desde el ancla hacia abajo y hacia la derecha (con números negativos vas hacia arriba o hacia la izquierda). Los dos últimos, alto y ancho, definen el tamaño del rectángulo que DESREF te entrega. Si los omites, el rectángulo mide una fila y una columna.

Piénsalo así: el ancla es la esquina superior izquierda del rectángulo, y los argumentos lo estiran. Con =DESREF($B$2;0;0;CONTARA($B$2:$B$100);1) le dices a Excel «ancla en B2, no te muevas ni abajo ni a los lados, y dame una columna de tantas filas como números haya entre B2 y B100». El resultado es la columna B con todos los importes, sea cual sea su número.

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

Tablas de Excel: el rango que ya crece solo

Una Tabla es un rango con «memoria». Al convertir A1:B13 en Tabla, Excel le pone un nombre interno, la viste con bandas y filtros, y, lo más importante, ajusta automáticamente las fórmulas que usan sus columnas. Las referencias tipo tblVentas[Importe] incluyen las filas nuevas sin tocarlas. La ruta para crear una Tabla está en Insertar > Tablas > Tabla, y la ficha Diseño de tabla que aparece al seleccionarla permite cambiar su nombre.

Crear un rango dinámico con DESREF para un gráfico

Vamos a usar la hoja Ventas de antes. El objetivo: un nombre definido VENTAS_TOTAL que abarque los importes actuales y los futuros, conectado a un gráfico de líneas que se estire solo.

El nombre definido con DESREF que se ajusta al añadir filas

Abre Fórmulas > Nombres definidos > Administrador de nombres y pulsa Nuevo. Escribe el nombre VENTAS_TOTAL y, en «Se refiere a», pega la fórmula. Luego cierra el cuadro con Aceptar.

La fórmula usa CONTARA (en inglés COUNTA) porque queremos contar celdas con algo dentro (números o texto), no solo números. Si tu columna puede tener celdas vacías entre datos, sustituye CONTARA por un CONTAR más estricto o usa una Tabla, que ignora los huecos.

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

Conectar el nombre al gráfico de líneas

Inserta un gráfico de líneas en la hoja (Insertar > Gráficos > Gráficos de líneas > Línea). Al crearlo, Excel le asigna un rango fijo. Para que use el nombre dinámico, haz clic en el gráfico, ve a Herramientas de gráficos > Diseño > Seleccionar datos, y agrega una serie nueva con el nombre VENTAS_TOTAL apuntando a la columna de importes. La fórmula para escribir el rango de la serie es =Libro1!VENTAS_TOTAL (sustituye Libro1 por el nombre de tu libro).

Tras aceptar, escribe un importe en la fila 14 y otro en la 15. La línea del gráfico debería alargar sola hasta esos puntos sin que toques el menú de Seleccionar datos.

Crear un rango dinámico con Tablas para BUSCARX y SUMAR

Ahora la misma hoja, pero convertida en Tabla, para aprovecharla con fórmulas. El objetivo: un total por mes que crezca y un BUSCARX que devuelva siempre la última venta del mes.

Convertir el rango en Tabla y nombrarla

Selecciona A1:B13 y ve a Insertar > Tablas > Tabla. Marca «La tabla tiene encabezados» y acepta. En la ficha Diseño de tabla que aparece, cambia el nombre a tblVentas en el cuadro «Nombre de la tabla», arriba a la izquierda.

Después añade una columna auxiliar llamada «Mes» (escribe «Mes» en C1 y =TEXTO(A2;"mmmm") en C2, arrastrando hasta C13).

=TEXTO(A2;"mmmm")

Una suma por mes que crece con la Tabla

Escribe los doce meses en F2:F13 («enero» en F2, «febrero» en F3, etc.) y en G2 la fórmula de suma. Al pulsar Entrar, Excel reconoce la referencia estructurada y la aplica a toda la columna, así que basta con arrastrar G2 hasta G13.

=SUMAR.SI.CONJUNTO(tblVentas[Importe];tblVentas[Mes];F2)

BUSCARX sobre la Tabla que devuelve el último importe

Para sacar la última venta de cada mes sin importar cuántas filas haya, usa BUSCARX con el modo de búsqueda -1, que recorre de abajo arriba. En H2 escribe:

=BUSCARX(2;1/(tblVentas[Mes]=F2);tblVentas[Importe];"Sin datos";0;-1)

El truco está en 1/(tblVentas[Mes]=F2): convierte los VERDADERO en 1 y los FALSO en errores de división, así BUSCARX busca el último 2 dentro de un rango de unos y errores, y devuelve el importe de esa posición.

Por qué un rango dinámico se rompe o no se actualiza

Aunque el sistema es fiable, hay tres tropiezos típicos que conviene reconocer a la primera.

DESREF recalcula el libro entero y lo vuelve lento

DESREF es volátil: si tu libro tiene muchas fórmulas, cada vez que escribes una celda, Excel recalcula todos los DESREF aunque no tengan nada que ver. En hojas grandes se nota. La alternativa moderna es una Tabla, que no es volátil y crece gratis. Si necesitas el comportamiento de DESREF pero sin la volatilidad, la fórmula equivalente con INDICE (en inglés INDEX) y CONTARA es =INDICE($B:$B;2):INDICE($B:$B;CONTARA($B:$B)+1).

El gráfico no se estira aunque el nombre se actualice

A veces el nombre VENTAS_TOTAL apunta a la columna correcta, pero el gráfico sigue mostrando los datos viejos. La causa es que el rango del eje X (las fechas) sigue fijo en Hoja1!$A$2:$A$13. Solución: crea también un nombre FECHAS_TOTAL con =DESREF($A$2;0;0;CONTARA($A$2:$A$100);1) y cámbialo en Herramientas de gráficos > Diseño > Seleccionar datos > Etiquetas del eje horizontal.

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

#¡REF! al borrar la fila que usa DESREF como ancla

Si borras la fila 2 (por ejemplo, una venta mal apuntada), DESREF pierde su ancla y devuelve #¡REF! en todas las fórmulas que dependen del nombre. La solución es poner el ancla una fila por encima del rango de datos y empezar a contar desde la siguiente: ancla en $B$1 y cuenta desde $B$2 con =DESREF($B$1;1;0;CONTARA($B$2:$B$100);1). Así, si borras una fila intermedia, solo cambia el conteo, no la referencia raíz.

Cuándo DESREF y cuándo Tabla, según tu Excel

La regla es simple: si tu Excel lo permite, usa Tabla; reserva DESREF para cuando necesitas un rango que la Tabla no te da, como un rango que empieza o termina en una celda que no es la primera o la última del bloque.

Si tu Excel es 2019 o anterior: solo DESREF o Tabla

Las Tablas existen desde Excel 2007 y son la opción más cómoda: convierten el rango en algo vivo sin fórmulas raras. DESREF también está desde 2007, así que tienes las dos herramientas. Lo que no tendrás es BUSCARX ni las matrices dinámicas (FILTRAR, ORDENAR, UNICOS), así que la alternativa a BUSCARX para buscar la última coincidencia es INDICE y COINCIDIR sobre una Tabla, no sobre DESREF.

Si tu Excel es 2021, 2024 o Microsoft 365

En estas versiones, BUSCARX, FILTRAR (en inglés FILTER), ORDENAR (en inglés SORT) y UNICOS (en inglés UNIQUE) devuelven rangos que ya son dinámicos por sí mismos, sin necesidad de DESREF ni de nombres definidos. Un =FILTRAR(tblVentas[Importe];tblVentas[Mes]=F2) derrama los importes del mes pedido debajo de la celda, y al añadir una fila a la Tabla, el resultado cambia solo. Para gráficos, la Tabla sigue siendo lo más limpio: los gráficos basados en una Tabla crecen automáticamente sin tocar nada.

Lo que ya sabes hacer con un rango dinámico en Excel

• Un rango dinámico es una referencia que se estira sola: con DESREF cuenta filas con CONTARA, y con una Tabla lo hace Excel en segundo plano.
• Un nombre definido (Fórmulas > Nombres definidos > Administrador de nombres) sirve para conectar un rango dinámico a un gráfico, de modo que la línea se actualice al añadir datos.
• Las referencias estructuradas (tblVentas[Importe]) hacen que SUMAR.SI.CONJUNTO, BUSCARX y el resto de fórmulas crezcan con la Tabla sin tocar la fórmula.
• DESREF es volátil: en libros grandes prefiere Tablas o la pareja INDICE:CONTARA, que no fuerzan un recálculo completo.

El siguiente paso es claro: abre tu hoja de ventas, selecciónala y conviértela en Tabla con Insertar > Tablas > Tabla. Verás cómo las fórmulas que ya tenías se ajustan solas cuando escribas la fila de mañana.