Cómo crear un dashboard dinámico en Excel: guía práctica

Un dashboard dinámico en Excel es una hoja de resumen que cambia al instante según lo que elijas con el ratón: cambias una región en una lista desplegable y los totales, los gráficos y los comparativos se recalculan solos sin tocar fórmulas. En este tutorial vas a montar uno sobre una hoja de ventas por vendedor, región, producto y mes: al final sabrás crear la tabla de origen, la tabla dinámica que resume todo, los gráficos que cuelgan de ella y la lista desplegable (con su Segmentación de datos) que lo gobierna. Lo que se explica sirve para Excel 2016 en adelante (las Segmentaciones de datos existen desde 2010; las tablas dinámicas con modelo de datos, desde 2013) y para Microsoft 365. Si tu Excel está en español con decimales con punto, recuerda que el separador de argumentos de las fórmulas pasa de «;» a «,».

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

Imagina una hoja llamada Ventas donde cada fila es una venta concreta: fecha, vendedor, región, producto y un importe. Tienes cientos de filas y necesitas ver, de un vistazo, cuánto vendió cada persona en su región en marzo. El dashboard dinámico es una hoja de resumen, separada de los datos, que muestra esos totales y los pinta con gráficos, y que responde a lo que tú le pidas: eliges una región en una lista desplegable y, al instante, los números y los gráficos cambian para mostrar solo esa región.

La gracia está en el adjetivo «dinámico»: nada está escrito a fuego. Cambias la región, cambias el mes o marcas un vendedor en una Segmentación de datos, y el dashboard entero se reordena. No hay que rehacer fórmulas ni copiar y pegar rangos cada vez. Sirve para direcciones comerciales que quieren ver cómo va la cuota de cada vendedor, para tiendas que comparan productos entre regiones o para cualquier persona que tenga un histórico de datos y quiera entenderlo sin liarse con tablas enormes.

La idea clave es separar tres cosas: los datos crudos (la hoja Ventas), el resumen (una tabla dinámica que agrupa esos datos) y la visualización (gráficos y listas que se apoyan en el resumen). Esa separación es lo que hace que el dashboard pueda cambiar con un solo clic: si solo cambias la lista desplegable y el resumen se actualiza, los gráficos que dependen del resumen se actualizan solos. Para que eso ocurra hace falta que los datos estén bien estructurados como una Tabla de Excel, que el resumen sea una tabla dinámica y que los gráficos apunten a esa tabla, no a un rango fijo.

Las piezas que forman un dashboard en Excel: tablas, tablas dinámicas y gráficos

Un dashboard no es una sola cosa: son tres piezas encajadas. Si una falla, las demás dejan de actualizarse. Conocer qué hace cada una y cómo se conecta con las otras es lo que te evita pasar horas peleándote con gráficos que «no se mueven».

La tabla de origen, convertida en Tabla de Excel

La base de todo dashboard es una tabla de datos limpia y estructurada: una fila por registro, una columna por dato, y una fila de encabezados en la primera fila. En Excel, esa tabla se convierte en una Tabla de Excel (a veces llamada «tabla inteligente» por la traducción de smart table) usando Ctrl+T o Inicio > Estilos > Dar formato como tabla. Lo importante es que esa Tabla de Excel tiene tres superpoderes: crecen solas cuando añades filas al final, sus fórmulas se replican automáticamente en cada nueva fila, y los gráficos y las tablas dinámicas que apunten a ella se enteran del cambio (en muchos casos basta con Actualizar).

La tabla dinámica que calcula los totales

La tabla dinámica es el «cerebro» del dashboard. Se crea con Insertar > Tablas > Tabla dinámica y resume la Tabla de Excel anterior: arrastras campos a Filas, a Columnas y a Valores (Suma, Cuenta, Promedio, Máx…). En un dashboard típico, Filas será el vendedor o la categoría, Columnas será el mes o el trimestre, y Valores será la suma del importe. Si haces clic derecho sobre un valor y entras en Resumir valores por, cambias Suma por Cuenta o por Promedio sin rehacer la tabla.

Los gráficos que muestran la tabla dinámica

El gráfico es la cara visible del dashboard. Se crea desde la pestaña Insertar, eligiendo Barras, Líneas, Circular o cualquier otro. El truco está en que el gráfico no lee la hoja Ventas: lee la tabla dinámica. Si cambias un cálculo en la tabla dinámica (clic derecho > Resumir valores por > Cuenta) el gráfico se actualiza solo, porque su origen es la tabla dinámica y no un rango de celdas escrito a mano.

Construir el primer dashboard con una lista desplegable

Vamos a montarlo paso a paso sobre la hoja Ventas que crece con cada nueva venta. Empiezas con doce filas que cubren enero, febrero y marzo, cuatro vendedores y dos regiones.

Convertir los datos en Tabla y nombrarla

1. Selecciona A1:E13.
2. Pulsa Ctrl+T (o ve a Inicio > Estios > Dar formato como tabla).
3. En el cuadro que aparece, marca La tabla tiene encabezados y pulsa Aceptar.
4. En la pestaña Diseño de tabla que aparece a la derecha, cambia el nombre a Ventas (por defecto es «Tabla1»; el panel Nombre de la tabla, arriba a la izquierda).

Crear la tabla dinámica que resumirá el dashboard

1. Haz clic en cualquier celda de la tabla Ventas.
2. Ve a Insertar > Tablas > Tabla dinámica.
3. Deja marcada Nueva hoja de cálculo y pulsa Aceptar.
4. En el panel Campos de tabla dinámica que aparece a la derecha, arrastra Vendedor a Filas, Mes (no Fecha) a Columnas y Importe a Valores.
5. Haz clic derecho sobre cualquier valor en la hoja de la tabla dinámica > Resumir valores por > Suma.
6. Cambia el nombre de la hoja a Resumen (clic en la pestaña de abajo, doble clic y escribes).

Añadir la lista desplegable que filtra el dashboard

1. Crea una hoja nueva llamada Dashboard (clic en el + junto a las pestañas).
2. En la celda B1 escribe el rótulo Región (en A1) y deja B1 vacía, que será la celda de la lista.
3. Selecciona B1.
4. Ve a Datos > Herramientas de datos > Validación de datos.
5. En Permitir, elige Lista.
6. En Origen, escribe =Ventas[Región] (es la columna Región de la Tabla llamada Ventas, con los corchetes y todo).
7. Pulsa Aceptar.

Los dos gráficos que cuelgan de la tabla dinámica

1. Ve a la hoja Resumen y clica en cualquier celda de la tabla dinámica.
2. Ve a Insertar > Gráficos > Gráfico de barras agrupadas.
3. Colócalo a la derecha de la tabla.
4. Haz clic en la tabla dinámica otra vez, ve a Insertar > Gráficos > Gráfico de líneas.
5. Coloca el segundo gráfico debajo del primero.

Conectar la lista desplegable a la tabla dinámica

Ahora viene la parte que convierte la lista en un filtro de verdad: que al elegir Norte o Sur los dos gráficos se reordenen solos. Lo haremos con dos técnicas combinadas: una Segmentación de datos (que es el filtro visual de la tabla dinámica) y un rango dinámico con DESREF (en inglés OFFSET) para que el gráfico no dependa de un rango fijo.

Insertar una Segmentación de datos y vincularla

1. En la hoja Resumen, clica en cualquier celda de la tabla dinámica.
2. Ve a Insertar > Filtros > Segmentación de datos.
3. Marca Región y Vendedor en el cuadro y pulsa Aceptar.
4. Mueve los dos paneles flotantes al lado de los gráficos.

Filtros que nacen de una celda con DESREF

La Segmentación de datos es muy visual, pero si quieres que el dashboard se filtre cuando cambia B1 (nuestra lista desplegable), necesitas una fórmula que monte el rango que leerá el gráfico. La fórmula clave es DESREF, y la escribimos en la hoja Resumen para alimentar el gráfico de líneas.

1. En la hoja Resumen, ve a una zona vacía (por ejemplo G1).
2. Escribe esta fórmula y Entrar:
3. Selecciona G1:G13, pulsa Ctrl+C y luego Ctrl+Alt+V > Pegar valores para obtener la lista de meses como referencia.

=DESREF(Resumen!A1;1;COINCIDIR($B$1;Resumen!$A$1:$D$1;0)-1;12;1)

• $B$1 es la celda con la región elegida en la lista desplegable (hoja Dashboard).
• Resumen!$A$1:$D$1 es la fila de encabezados de la tabla dinámica: «Ene», «Feb», «Mar».
• COINCIDIR busca la región en esa fila de encabezados y devuelve la columna.
• DESREF parte de A1 y se mueve el número de columna devuelto por COINCIDIR, con una altura de 12 filas.

Después, en el gráfico de líneas: clic derecho > Seleccionar datos > Agregar serie y en Valores escribes Resumen!$G$1:$G$13. A partir de ahora, al cambiar la lista desplegable B1, la columna G se recalcula y el gráfico de líneas toma solo los datos de esa región.

Errores típicos del dashboard dinámico y cómo arreglarlos

Antes de dar por terminado el dashboard conviene saber qué se rompe y por qué. Estos son los cinco tropiezos más comunes cuando se empieza con tablas dinámicas, gráficos y fórmulas volátiles.

La tabla dinámica no se actualiza al cambiar la lista

Si tras elegir otra región en B1 el gráfico sigue mostrando los mismos datos, casi siempre es porque la Segmentación de datos apunta a otra tabla dinámica distinta o porque el libro no se ha actualizado al abrirlo. Vuelve a la hoja Resumen, clica en la tabla dinámica y ve a Diseño > Actualizar. Para que no tengas que hacerlo a mano cada vez, activa Tabla dinámica > Opciones > Actualizar al abrir el archivo.

El gráfico muestra el mismo dato siempre

Si al elegir Sur el gráfico sigue dando los totales de Norte, es porque el rango del gráfico está fijo, no dinámico. Solución: entra en Seleccionar datos y sustituye el rango por la fórmula DESREF anterior (Resumen!$G$1:$G$13) o vuelve a crear el gráfico desde cero usando la tabla dinámica como origen.

#¡REF! al mover o borrar la tabla dinámica

Borrar la hoja Resumen o cambiar su nombre deja los gráficos con el icono de error #¡REF!, porque apuntaban a esa hoja. La solución: no borres la hoja, muévela o renómbrala sin más. Si ya la borraste, ve a Insertar > Gráficos > Seleccionar datos y reasigna el rango a la nueva hoja de la tabla dinámica.

La lista desplegable muestra valores repetidos

Si la lista ofrece nombres duplicados, es porque la columna de la Tabla de Excel tiene celdas repetidas o espacios invisibles. Revisa la columna Región en Ventas, quita espacios con =ESPACIOS(D2) y un copiar-pegar de valores, y recrea la lista con =Ventas[Región] después.

DESREF devuelve 0 o un número raro

Si al cambiar B1 la columna G da 0 o un número que no toca, casi siempre es porque el encabezado de la columna buscada no coincide exactamente con lo que escribiste en la validación de datos. Compara el rótulo de la tabla dinámica (por ejemplo Ene) con el texto exacto que admitiría la fórmula.

Lo que ya sabes hacer con un dashboard en Excel

• Pasar una hoja plana de datos a una Tabla de Excel con Ctrl+T para que crezca sola.
• Crear una tabla dinámica que resume por vendedor, mes y región con un par de arrastres.
• Colgar dos gráficos de esa tabla dinámica para tener lectura inmediata.
• Poner una lista desplegable que controla el filtro desde una sola celda.
• Usar DESREF para que el gráfico responda a la celda de la lista en vez de tener un rango fijo.

El siguiente nivel es un solo paso: añadir formato condicional a la columna de totales por vendedor (Inicio > Estilos > Formato condicional > Barras de datos) para ver al instante quién pasa el objetivo del mes. Cuando lo tengas, lo único que diferencia tu dashboard de los profesionales es que ellos suelen conectar la misma tabla dinámica a un mapa geográfico y a tarjetas de KPI con IMPORTARDATOSDINAMICOS (en inglés GETPIVOTDATA), que ya entra en la familia de los paneles de Power BI.

Ficha oficial de DESREF (OFFSET)

Dudas al montar un dashboard dinámico en Excel