Cuadro de mando en Excel desde cero: masterclass

Cuadro de mando en Excel desde cero: masterclass

Cincuenta minutos para construir un cuadro de mando en Excel partiendo de una tabla en blanco. Es la versión larga y sin atajos: se explica el porqué de cada decisión, no solo dónde hacer clic.

Se cubre el recorrido completo:

- Preparar los datos para que el dashboard no se rompa al crecer
- Elegir qué métricas van arriba y cuáles son ruido
- Gráficos dinámicos, segmentación y filtros conectados
- Maquetación: rejilla, jerarquía visual y color con criterio
- Los errores que obligan a rehacer un dashboard a mitad

El fichero

Descarga el libro de la masterclass para seguir el vídeo con los mismos datos.

Si lo que buscas es la versión exprés, tienes el mismo montaje en 5 minutos.

Una masterclass de unos 50 minutos que recorre el camino completo: desde varios ficheros de texto sueltos en una carpeta hasta un cuadro de mando terminado, con filtros, gráficos y KPIs. Sin complementos de pago y sin salir de Excel.

Los tres pasos que tiene cualquier cuadro de mando

Antes de tocar nada, conviene tener claro el esqueleto. Todo cuadro de mando pasa por tres fases, y saltarse la primera es el error más caro:

  1. Traer los datos y consolidarlos desde su origen.
  2. Preparar y enriquecer la información para que la parte visual sea sencilla.
  3. Montar el cuadro final, que es lo que se ve y lo que se enseña.

El ejemplo: una empresa que comercializa vinos y licores, con las ventas del último trimestre repartidas en varios ficheros de texto, uno por mes y tipo de producto.

Fase 1: consolidar varios ficheros de una carpeta

En Datos → Obtener datos → De una carpeta. Excel abre un asistente, lee la carpeta entera y lista lo que hay dentro.

Aquí aparece el primer detalle práctico: en esa carpeta también hay libros de Excel que no queremos incluir. Se resuelve con un filtro por extensión, igual que filtrarías una columna en una hoja. También podrías filtrar por nombre o por fecha del fichero.

Con la selección hecha, Combinar archivos. Excel muestra cómo va a leer cada uno, confirmas la estructura y carga todo en una única tabla.

Por qué merece la pena hacerlo así y no copiando y pegando: un análisis puntual sirve una vez, pero lo que aporta valor de verdad es poder repetirlo cada cierre de mes sin volver a montarlo. Esta carga es Power Query, y queda grabada: el mes que viene, actualizas y ya está.

Fase 2: enriquecer los datos

Los datos que llegan casi nunca traen todo lo que necesitas. Aquí faltan dos cosas: el mes y la familia de producto. Se consiguen por dos caminos distintos, y los dos son muy reutilizables.

El mes, extraído del nombre del fichero

El periodo está dentro del nombre del fichero, entre corchetes. La técnica es localizar los dos corchetes y sacar lo que hay en medio:

  • ENCONTRAR devuelve la posición de la primera aparición de un carácter. Se usa dos veces: una para el corchete de apertura y otra para el de cierre.
  • EXTRAE saca el trozo intermedio: empieza en la posición del primer corchete más uno, y toma tantos caracteres como la diferencia entre ambas posiciones menos uno.

Con el octubre 2024 ya aislado, se parte en dos:

  • El año con DERECHA, cuatro caracteres, que siempre son cuatro.
  • El mes con IZQUIERDA, y aquí está el truco: como el nombre del mes varía de longitud, el número de caracteres se calcula con LARGO del texto completo menos cinco (los cuatro del año y el espacio).

Cinco funciones de texto que resuelven la inmensa mayoría de los "sácame este dato de dentro de esta cadena".

La familia de producto, con una tabla de mapeo

Un mapeo es una tabla de apoyo que enriquece los datos originales. Aquí, para cada producto indica su grupo grande (vino o licor) y su subcategoría.

Se trae con BUSCARV, usando el nombre del producto como campo de enlace: valor buscado, tabla de mapeo, número de columna que quieres de vuelta (2 para el grupo, 3 para la subcategoría) y coincidencia exacta, que es FALSO.

Este paso es el que permite aplicar jerarquías, y sirve para mucho más que productos: agrupar puntos de venta por zonas, personas por departamento, servicios por línea de negocio.

Fase 3: tablas dinámicas, una por gráfico

Lo que no se ve del cuadro de mando son las tablas dinámicas. Cada gráfico necesita la suya, con la estructura exacta que ese gráfico va a dibujar.

Una tabla dinámica tiene cuatro zonas: filas, columnas, valores y filtros. Dos recomendaciones del vídeo para no liarse: en columnas, una sola categoría; en filas puedes poner varias, porque la tabla simplemente se alarga y se sigue leyendo.

Se monta la primera (meses en filas, año en columnas, unidades en valores) y las siguientes se copian y se pegan debajo, cambiando el campo: por grupo de producto, por población, por tipo.

Segmentación de datos en vez de filtros

La zona de filtros funciona, pero la segmentación de datos es mucho más amable para quien va a usar el cuadro: botones, clic, y con Ctrl selección múltiple. Se insertan desde Analizar tabla dinámica → Insertar segmentación de datos.

Fase 4: los gráficos y los KPIs

Cada gráfico se crea seleccionando su tabla y usando Insertar → Gráficos recomendados. Después:

  • Ocultar los botones de campo, que ensucian el resultado.
  • Elegir un diseño de los precargados, evitando el primero, que es el que usa todo el mundo.
  • Aplicar la misma paleta de colores en todos los gráficos, que es lo que da coherencia visual al conjunto.
  • En las barras horizontales, si te descoloca ver el valor mayor arriba: botón derecho sobre el eje de categorías → Categorías en orden inverso.

Los cuadros de KPI (esos recuadros con un número grande) no son gráficos: son formas. Insertas un rectángulo, lo seleccionas y en la barra de fórmulas escribes = y la celda de la dinámica que contiene el valor. El número se actualiza al filtrar.

Un aviso que ahorra tiempo: al vincular una forma a una celda, la forma pierde el formato. Se recupera con el copiador de formato desde otra que ya esté bien.

El paso que lo convierte en un cuadro de mando

Al principio, al filtrar, solo cambia un dato. El motivo es que la segmentación se creó sobre una tabla dinámica concreta y solo controla esa.

La solución: seleccionar la segmentación → Conexiones de informes → marcar todas las tablas dinámicas del cuadro. Hay que repetirlo con cada segmentación. A partir de ahí, un clic mueve el cuadro entero.

Lo último es cosmético: logo, medidas, y ocultar las columnas donde viven las tablas dinámicas.

Funciones clave

  • ENCONTRAR, EXTRAE, IZQUIERDA, DERECHA, LARGO — el kit completo para sacar datos de dentro de un texto.
  • BUSCARV con coincidencia exacta — el mapeo que enriquece los datos de origen.

Conclusión

La parte visual es la que luce, pero ocupa la menor parte del trabajo. Lo que sostiene un cuadro de mando útil es la fase de carga y preparación: si esa parte está sistematizada, el mes siguiente es pulsar actualizar.

Y el objetivo final es más importante que el fichero: dejar de depender de que alguien te prepare el informe. Con Excel se llega a resultados profesionales, y en el grupo de WhatsApp de InflueXcel hay gente montando esto a diario y resolviendo dudas por el camino.

Más contenido de Excel en InflueXcel