Un dashboard o panel de control, es una herramienta visual que nos permite ver todos aquellos datos o medidores de desempeño de nuestra empresa o proyecto. A partir de esto junto a un análisis, podremos tomar decisiones diarias o mensuales de acuerdo a nuestros objetivos.
Un tablero te dice cómo va el negocio hoy. Si además quieres anticipar qué puede salir mal antes de que se note en los indicadores, mira cómo se arma una matriz de riesgos en Excel con probabilidad, impacto y mapa de calor.
Aprenderemos a crear un dashboard en Excel, paso a paso, ya que este contiene gráficos de barras, velocímetros, tacómetros, e imágenes interactivas entrelazadas a un total, que se puede filtrar según el área o categoría que deseemos
En este artículo analizaremos mediante un velocímetro de avance, el porcentaje de ingresos de cada producto según la zona y el trimestre.
El velocímetro es la pieza del tablero que más se mira: de un vistazo dice cuánto llevas de la meta y si vas en rojo, ámbar o verde. Si quieres montarlo tú, en velocímetro en Excel están las nueve formas de dibujarlo paso a paso, cada una con su archivo y su video.
Llévate la plantilla antes de empezar
Si prefieres ver el tablero funcionando mientras lees, este es el archivo del artículo: las tres hojas, los seis medidores y los dos filtros ya montados. Es gratis.
Dashboard de KPIs en Excel (plantilla gratis)
Lo esencial, en treinta segundos
- Un dashboard de KPIs en Excel es una hoja donde los indicadores se dibujan solos a partir de tus datos. Aquí se monta con tablas dinámicas y gráficos de velocímetro, sin una sola macro.
- Cada medidor son dos objetos superpuestos: un gráfico de anillo que hace de carátula de colores, y encima otro gráfico que lleva el valor y la aguja. Los dos girados para que la mitad de abajo del círculo quede escondida.
- Los filtros son segmentaciones de datos: los botones de Zona y Trimestre que filtran todo el tablero con un clic.
- El número, el nombre y el estado que ves bajo cada aguja no están escritos a mano: son cuadros de texto vinculados a celdas, así que cambian solos cuando cambian los datos.
- La plantilla trae 738 ventas de ejemplo (de 2020 a 2022), seis medidores montados y los dos filtros.
Descarga el dashboard ya montado
El archivo de este artículo, con las tres hojas (Dashboard, TD y BBDD), los seis medidores y los dos filtros funcionando. Dentro va además una guía con qué celda toca escribir, qué no se puede tocar y qué no hace esta versión.
Dashboard de KPIs en Excel (plantilla gratis)
Qué mide este tablero, y qué no
Antes de montarlo conviene entender qué número enseña la aguja, porque es el malentendido más caro de este tipo de paneles.
El porcentaje de cada medidor es participación: cuánto pesa ese producto sobre el total de ingresos del periodo que tengas filtrado. No es cumplimiento de una meta. Un producto marcado como «Muy bajo» no está vendiendo mal: está vendiendo poco comparado con los demás.
Y de ahí sale una decisión que conviene copiar: los tramos del semáforo dependen de cuántos productos tengas. Con seis, la participación media es 1 entre 6, o sea 16,7 %, así que unos cortes en 20 %, 35 % y 70 % dejarían a casi todos en la categoría más baja y harían la más alta inalcanzable. En la plantilla están puestos en 8 %, 17 % y 30 %, que es lo razonable alrededor de esa media. Si tú trabajas con diez productos, la media baja a 10 % y los cortes hay que bajarlos también.
En resumen: un panel de participación responde «dónde está mi facturación», y uno de cumplimiento responde «voy bien o voy mal». Son preguntas distintas y se montan distinto.
Cómo elaborar un dashboard de KPIs en Excel, paso a paso
El método son ocho pasos y ninguno necesita macros. Lo que sigue es el proceso completo, con las capturas de cada pantalla.
Paso 1. La tabla dinámica con el porcentaje de cada producto
Crearemos una tabla dinámica de nuestra base de datos, seleccionamos la tabla, e insertamos en una hoja nueva.
Vamos a tomar el campo de productos y lo colocamos en fila, eliminando el total, dejando solo el nombre. Haremos lo mismo con ingresos en el área de valores, dos veces.
Uno de los ingresos, lo vamos a convertir en el porcentaje basado en el monto que tenemos. Nos posicionamos en la columna, clic derecho > mostrar valores como > % del total de columnas.
Si queremos modificar el nombre de las tablas, podemos usar la tecla “Ctrl + B” Para reemplazar la palabra que no queremos.
Copiamos la tabla dinámica, usando el formato de “=” en la celda y arrastraremos hasta abajo, para que tome los datos, así se creará una nueva tabla que la usaremos como apoyo para crear los gráficos.
Paso 2. Las dos columnas que mueven la aguja
Agregaremos dos columnas más, una que nos representara la aguja de nuestro velocímetro, donde la columna tendrá un valor muy pequeño, y la otra será un ayudante para que la aguja se mueva.
Para que ambas marquen el avance, haremos la siguiente fórmula en el apoyo.
Paso 3. El gráfico de anillo: la carátula del velocímetro
Insertaremos un gráfico de anillo, al cual le agregaremos los datos, con una partición de 11 partes.
Daremos clic derecho > formato serie de datos para modificar el ángulo y el tamaño del agujero central, de igual forma los colores a cada parte. El lado más grande, lo dejaremos sin color y sin contorno.
Procedemos a cortar y pegar el gráfico en la hoja donde queremos colocar el dashboard. Lo posicionamos, damos clic derecho > seleccionar datos, y agregamos una nueva serie.
Paso 4. El gráfico circular encima: la aguja
El nombre de esta nueva serie será el “Producto A” y los datos, las tres filas consiguientes.
Modificaremos el tipo de gráfico por un gráfico circular en 2D, que será nuestro eje secundario.
De igual forma que con el primer gráfico, cambiaremos el ángulo, y los colores dejándolo sin relleno exceptuando la parte más delgada, ya que esta ser nuestra aguja.
A esta única parte le daremos grosor, color tanto de relleno como de contorno.
Paso 5. Repetir el medidor para los seis productos
Lo que haremos será copiar el gráfico en cada uno de los cuadros de los productos marcados. Y modificaremos los datos de cada uno, seleccionando el producto y datos correspondientes.
Paso 6. Los filtros: segmentaciones de datos
Para el filtro de zonas o región, volvemos a la tabla inicial, y en analizar > insertar segmentación de datos, escogeremos el filtro que deseemos.
Para este caso, copiaremos y pegaremos la tabla dinámica. Sacaremos de nuestras áreas, cada campo, dejándola vacía. Si queremos otro filtro como fecha ya sea mensual, trimestral. Lo que haremos será que la tabla dinámica nos fraccione las fechas. Agregaremos únicamente la fecha y luego las sacaremos del área. Ingresaremos de nuevo, la fecha fraccionada que nos interesa. Seleccionaremos la tabla inicial e iremos a analizar > insertar segmentación de datos, escogeremos el filtro que fracciono.
Una vez tengamos los filtros listos, los llevaremos a la hoja de dashboard.
Paso 7. El formato de cada medidor y la etiqueta de estado
Personalizaremos cada velocímetro, para visualizar el cambio porcentual que se genere según el filtro. Para esto, agregaremos un cuadro de texto o una figura. En la barra de fórmulas vincularemos con la celda del porcentaje del segundo cuadro realizado.
Podemos modificar el formato de texto, tamaño. De igual forma, podemos agregarle a nuestro velocímetro un círculo como base de la aguja, para darle más cuerpo.
Vamos a crear un indicador en texto con la función “si” para darle una valoración a cada porcentaje que nos marque el velocímetro. Hacemos una nueva columna que nos represente el “estado” con una pequeña leyenda de valoración que será nuestra guía. Ingresaremos la siguiente fórmula en cada celda de cada producto.
Agregamos un cuadro de texto en el dashboard, vinculándola con la celda de estado de nuestra tabla para que nos muestre un dato más preciso.
Paso 8. El texto que avisa cuando no hay datos
Para realizar esto, debemos ingresar una celda con la función “concatenar” uniendo tanto la celda de producto como de ingreso en monto, combinándola con la función “texto” para que nos dé un formato de moneda y decimal.
Arrastramos esa fórmula hasta completar los productos.
Ahora crearemos una condición, con la función “si” para que el dato que nos proporcione sea con números mayores a “0”
De igual manera arrastraremos para el resto de los productos.
Insertaremos un cuadro de texto o figura en nuestro dashboard, vinculando las celdas de la condición, cambiaremos el formato, y pegamos en el resto de nuestros velocímetros.
De esta manera, tendremos un dashboard de indicadores dinámico, que nos muestre el avance de las ventas de cada producto, según la zona y el trimestre correspondiente.
Dashboard de KPIs en Excel (plantilla gratis)
Dashboards modernos: segmentaciones y datos que se actualizan solos
Un dashboard actual se apoya en segmentaciones de datos para filtrar con clics y en matrices dinámicas (FILTRAR, UNICOS) para que las tablas de origen se actualicen automáticamente al añadir información. Menos mantenimiento, más impacto visual.
Seis errores que rompen el dashboard
1 · Pegar datos y no actualizar
Una tabla dinámica no se entera sola de que has añadido filas. Pegas cien ventas nuevas, vuelves al tablero y las agujas están donde estaban. Hay que actualizar: clic derecho sobre la dinámica y Actualizar, o Ctrl+Alt+F5 para todo el libro de una vez (Alt+F5 actualiza solo la dinámica seleccionada). Los dos atajos son de Excel de escritorio en Windows. Es el motivo número uno de «no me funciona».
2 · Espacios y caracteres invisibles en los nombres
«Producto A» y «Producto A » con un espacio al final son dos productos distintos para la tabla dinámica, y te van a salir como dos filas. Lo mismo con los caracteres invisibles que llegan al copiar desde un PDF o desde el sistema de la empresa.
Lo que no separa nada son las mayúsculas: «Producto A» y «producto a» caen en la misma fila, aunque la etiqueta que se muestre sea la de la primera que apareció.
Y el arreglo no es Buscar y reemplazar quitando espacios, que te dejaría «ProductoA» en toda la columna. En una columna auxiliar escribe =ESPACIOS(LIMPIAR(C4)), arrastra, y pega el resultado como valores sobre la columna original. ESPACIOS quita los espacios sobrantes del principio, del final y los dobles de en medio; LIMPIAR se lleva los caracteres que no se imprimen.
3 · El importe entra como texto
Si en la columna de ingresos escribes «S/ 1,200» a mano, Excel puede guardarlo como texto. Y el síntoma no es un cero: si había texto en el campo cuando creaste la tabla dinámica, Excel elige Cuenta en vez de Suma y verás «Cuenta de Ingreso» con números pequeñísimos; si la dinámica ya existía con Suma, las filas de texto suman cero y el total sale corto sin avisar.
El caso que más muerde por aquí es el contrario: pegar «1.200» exportado de un sistema configurado a la española, donde eso significa mil doscientos, y que en un Excel peruano o mexicano entre como 1,2. El tablero se descuadra por mil y a simple vista no se nota.
Para salir de dudas, =ESNUMERO(D4) en una celda al lado. Si devuelve FALSO, es texto. Y si el campo ya está en la dinámica: doble clic en su encabezado > Resumir valores por > Suma, después de convertir el texto en número.
4 · Fechas que no son fechas
Si la columna de fecha llegó de una exportación y entró como texto, el filtro de trimestres no puede agrupar. Se arregla seleccionando la columna y pasándola por Datos > Texto en columnas, eligiendo el formato de fecha correcto en el último paso. El mismo ESNUMERO sirve de detector: una fecha de verdad es un número por dentro.
Lo que no es un detector fiable es la alineación. Sí, los números se van a la derecha y el texto a la izquierda, pero eso solo mientras nadie haya tocado la alineación de la columna. En un archivo que ha pasado por varias manos ya no dice nada.
5 · Mover un solo gráfico del medidor
Cada velocímetro son dos objetos superpuestos: la carátula de colores por debajo y el gráfico del valor con la aguja por encima. Si arrastras uno, se separan. Para moverlos, selecciónalos juntos con Ctrl o agrúpalos antes con clic derecho > Agrupar.
6 · Filtrar una zona donde falta un producto
Este no rompe nada pero sorprende. Cada tarjeta está enganchada a una fila de la tabla dinámica, no a un producto concreto. Si filtras una zona en la que un producto no vendió nada, ese producto desaparece de la dinámica, las filas de abajo suben una posición y las tarjetas se recolocan.
El tablero sigue siendo correcto —el nombre, el porcentaje, el estado y la aguja de cada tarjeta vienen de la misma fila, así que se mueven juntos— pero el producto que estaba arriba a la izquierda ya no es el mismo. Y si quedan menos de seis productos, las tarjetas sobrantes se quedan en blanco. Vale la pena saberlo antes de enseñarle el tablero a alguien en una reunión.
Qué hay que saber del archivo que descargas
La plantilla trae tres hojas y conviene saber para qué sirve cada una antes de abrirla:
| Hoja | Qué es | ¿Se toca? |
|---|---|---|
| Dashboard | El tablero. Seis medidores, sus rótulos y los dos botones de filtro. No tiene ni una celda escrita: todo son gráficos y cuadros de texto flotando encima, y esos cuadros están vinculados a celdas de la hoja TD. | Solo para mirar y filtrar |
| TD | El motor. A la izquierda, las dos tablas dinámicas, que se actualizan solas. A la derecha, las columnas de aguja, apoyo y estado, escritas a mano, y la tabla de referencia del semáforo en F12:G16. | Solo si cambias los tramos o añades un producto |
| BBDD | Los datos. 738 ventas de ejemplo con fecha, producto, importe y zona, dentro de una tabla de Excel. | Sí: aquí pegas lo tuyo |
Para pegar tus ventas, respeta las cinco columnas y pega dentro de la tabla o justo en la fila siguiente, sin dejar ninguna en blanco: así la tabla se extiende sola y la dinámica recoge lo nuevo al actualizar.
El 200 % de la fórmula del apoyo
Es la parte que casi nadie explica. La columna que rellena lo que no se ve lleva esto:
Ese 200 % hace que la circunferencia entera valga el doble de la escala que quieres leer: 200 repartidos en 360 grados son 100 en la media vuelta de arriba, que es la que se ve. La porción de apoyo se queda con todo lo demás —la mitad escondida de abajo y el tramo que la aguja todavía no ha alcanzado— y va sin relleno.
De ahí una consecuencia que conviene saber: este medidor no tiene arco gris de fondo. Solo se ve el trocito de color y la aguja. Si quieres el fondo típico de un velocímetro, hacen falta cuatro celdas en vez de tres, separando el tramo pendiente visible de la mitad oculta.
Si algún día quieres otro ángulo, la cuenta es total = 360 dividido entre los grados que quieras ver:
| Arco visible | La fórmula pasa a ser | Ángulo del primer sector |
|---|---|---|
| 180° · media vuelta, la que trae | =200%-SUMA(G4:H4) | 270° |
| 240° | =150%-SUMA(G4:H4) | 240° |
| 270° · tres cuartos | =4/3-SUMA(G4:H4) | 225° |
Para el de 270° conviene escribir 4/3 y no 133,33 %, para que no se arrastre el redondeo. Y el ángulo hay que cambiarlo en los dos gráficos del medidor, o la aguja queda desfasada respecto de la carátula.
Un detalle de la aguja
El grosor de la aguja es ese 0,01 de la columna de al lado, y ocupa 0,01 sobre una escala de 2, o sea 1,8 grados. La aguja se dibuja desde el valor hacia adelante, no centrada en él, así que marca un pelín de más. Es inofensivo para repartos, pero conviene saberlo si reutilizas el motor para algo que haya que leer con precisión.
Cuando pegar los datos cada mes empieza a cansar
Este tablero es gratis y funciona, pero el trabajo manual sigue siendo tuyo: exportar, pegar, limpiar y actualizar. Las plantillas premium de Excel-2win registran y calculan en el mismo archivo, así que los indicadores están al día en cuanto anotas un movimiento, y además traen el detalle fila a fila detrás de cada número.
Ver el catálogo de plantillas premiumPreguntas frecuentes sobre los dashboards de KPIs en Excel
¿Qué versión de Excel necesito?
¿Funciona en Google Sheets?
¿Puedo añadir un séptimo producto?
¿Por qué mi medidor sale como un círculo completo?
¿Cómo cambio los tramos del semáforo?
¿El medidor se pone rojo cuando un producto va mal?
¿Sirve para otros indicadores que no sean ventas?
Llévate la plantilla
El dashboard completo, con los datos de ejemplo para que veas cómo se mueve antes de pegar los tuyos. Dentro va también una hoja con la guía y otra con la ruta para seguir aprendiendo.
Dashboard de KPIs en Excel (plantilla gratis)
Versiones premium
⚡ Lleva todo el negocio bajo control, no solo esto
Automatizadas y listas para usar: cobros, pagos, inventario, caja y proyectos. Pago único, acceso de por vida y funcionan sin internet.
Cuentas x Cobrar V1Registras la factura una vez y la plantilla te dice quien te debe, cuanto…USD 27Ver plantilla
Cuentas x Pagar V1Te avisa de lo que vence esta semana, para que no vuelvas a quedar…USD 27Ver plantilla
Control Flujo EfectivoCargas ingresos y gastos y ves de un vistazo si el mes cierra en…USD 27Ver plantilla
Macro Control Stock Basico V1Entradas, salidas y un panel que te avisa de lo que se esta agotando…USD 7Ver plantilla
Gestor de Cotizaciones o Proforma V1Eliges el cliente, eliges los productos y la cotizacion sale con tus datos y…USD 27Ver plantilla
Genera Orden de Compra o ServicioLa orden sale hecha, numerada y lista para enviar, sin copiar y pegar de…USD 27Ver plantilla
Curva S AutomatizadaCargas el avance y la curva se dibuja sola, con el programado y el…USD 20Ver plantilla📁 Este artículo forma parte de nuestra guía de Dashboards y KPIs, donde tienes todos los tutoriales y plantillas del tema ordenados.
Deja una respuesta