Dashboard de KPI en Excel: cómo elegir indicadores, calcularlos y montar un cuadro de mando con semáforos
Guía para montar un dashboard de KPI en Excel: elegir 5–8 indicadores, ficha de cada KPI, hoja de datos y de cálculo, semáforos para indicadores donde más es mejor y donde menos es mejor, tendencias y un ejemplo completo de tienda online.
Respuesta rápida: un dashboard de KPI en Excel es una sola pantalla que muestra entre 5 y 8 indicadores clave, cada uno con su valor real, su objetivo, la variación frente al periodo anterior y un semáforo (verde, ámbar, rojo). Se construye en tres capas: una hoja de datos con los registros, una hoja de cálculo donde cada KPI se obtiene con fórmulas como SUMAR.SI.CONJUNTO, y la hoja del dashboard, que solo muestra resultados. Lo más importante no es el diseño, sino elegir bien los indicadores y definir cómo se calcula cada uno.
Los nombres de funciones de esta guía son los de Excel en español, con punto y coma como separador. Las cifras del ejemplo son ficticias.
KPI, métrica y dato: no es lo mismo
| Concepto | Qué es | Ejemplo |
|---|---|---|
| Dato | Un registro individual | Pedido nº 1043, 62 € |
| Métrica | Una medida agregada | Ventas de octubre: 30.000 € |
| KPI | Una métrica ligada a un objetivo y a una decisión | Ventas de octubre frente al objetivo de 32.000 € |
Si una métrica cambia y nadie haría nada distinto, probablemente no es un KPI. Esa pregunta ayuda a mantener el cuadro de mando corto.
Paso 1: Elegir los indicadores
Parte de los objetivos del negocio y elige uno o dos KPI por objetivo. Para una pyme suelen bastar:
- Crecimiento: ventas netas, número de clientes nuevos.
- Rentabilidad: margen bruto %, resultado de explotación (de la cuenta de pérdidas y ganancias).
- Eficiencia comercial: ticket medio, tasa de conversión, coste de adquisición de cliente.
- Calidad: porcentaje de devoluciones, incidencias por cada 100 pedidos.
- Liquidez: días medios de cobro, saldo de tesorería.
Paso 2: La ficha de cada KPI
Antes de escribir fórmulas, documenta cada indicador en una hoja Fichas. Evita discusiones del tipo “¿las ventas incluyen IVA?”:
| Campo | Ejemplo: tasa de conversión |
|---|---|
| Definición | Porcentaje de visitas a la tienda online que terminan en pedido |
| Fórmula | Pedidos / sesiones |
| Fuente | Pedidos: tienda online; sesiones: herramienta de analítica |
| Frecuencia | Mensual |
| Objetivo | 2,0 % |
| Sentido | Más es mejor |
| Umbral ámbar | 90 % del objetivo |
| Responsable | Responsable de marketing |
El campo Sentido es clave: en ventas, más es mejor; en coste de adquisición, devoluciones o días de cobro, menos es mejor. El semáforo debe tenerlo en cuenta.
Paso 3: Hoja de datos
Guarda los registros en tablas de Excel (Ctrl+T), una por fuente: Ventas (fecha, pedido, cliente, importe neto, coste, devuelto sí/no), Marketing (mes, sesiones, inversión, clientes nuevos) y Cobros (factura, fecha emisión, fecha cobro). Si ya llevas el control de ventas en Excel, esa tabla sirve directamente. Añade a cada tabla una columna Mes: =FIN.MES([@Fecha];-1)+1.
Paso 4: Hoja de cálculo
En la hoja Calculo, una fila por KPI y columnas para el mes seleccionado (celda Dashboard!C2) y el mes anterior (=FECHA.MES(Dashboard!C2;-1)):
| KPI | Fórmula |
|---|---|
| Ventas netas | =SUMAR.SI.CONJUNTO(Ventas[Importe neto];Ventas[Mes];$C$2) |
| Margen bruto % | =SI.ERROR(1-SUMAR.SI.CONJUNTO(Ventas[Coste];Ventas[Mes];$C$2)/Ventas_mes;"") |
| Nº pedidos | =CONTAR.SI.CONJUNTO(Ventas[Mes];$C$2) (una fila por pedido) |
| Ticket medio | =SI.ERROR(Ventas_mes/Pedidos_mes;"") |
| Tasa de conversión | =SI.ERROR(Pedidos_mes/BUSCARX($C$2;Marketing[Mes];Marketing[Sesiones]);"") |
| Coste de adquisición (CAC) | =SI.ERROR(BUSCARX($C$2;Marketing[Mes];Marketing[Inversión])/BUSCARX($C$2;Marketing[Mes];Marketing[Clientes nuevos]);"") |
| % devoluciones | =SI.ERROR(CONTAR.SI.CONJUNTO(Ventas[Mes];$C$2;Ventas[Devuelto];"Sí")/Pedidos_mes;"") |
| Días medios de cobro | =SI.ERROR(PROMEDIO.SI.CONJUNTO(Cobros[Días];Cobros[Mes cobro];$C$2);"") |
En la tabla Cobros, la columna Días es =[@[Fecha cobro]]-[@[Fecha emisión]].
Paso 5: Semáforos que respetan el sentido del KPI
Calcula primero el grado de cumplimiento, de forma que 100 % o más sea siempre bueno:
=SI.ERROR(SI(Sentido="Más es mejor";Real/Objetivo;Objetivo/Real);"")
Después, el semáforo:
=SI(Cumplimiento="";"";SI(Cumplimiento>=1;"Verde";SI(Cumplimiento>=Umbral;"Ámbar";"Rojo")))
Aplica formato condicional con tres reglas (texto igual a Verde, Ámbar, Rojo) o un conjunto de iconos sobre la columna de cumplimiento. Usa también un texto o símbolo, no solo color: así el dashboard se entiende impreso en blanco y negro y por personas con dificultades para distinguir colores.
Ejemplo: tienda online, octubre
| KPI | Real | Objetivo | Sentido | Cumplimiento | Estado |
|---|---|---|---|---|---|
| Ventas netas | 30.000 € | 32.000 € | Más | 93,8 % | Ámbar |
| Margen bruto | 41 % | 40 % | Más | 102,5 % | Verde |
| Ticket medio | 50 € | 48 € | Más | 104,2 % | Verde |
| Tasa de conversión | 1,8 % | 2,0 % | Más | 90,0 % | Ámbar |
| Coste de adquisición | 14 € | 12 € | Menos | 85,7 % | Rojo |
| % devoluciones | 6 % | 5 % | Menos | 83,3 % | Rojo |
| Días medios de cobro | 32 | 30 | Menos | 93,8 % | Ámbar |
Con un umbral ámbar del 90 %: 30.000/32.000 = 93,8 %; 12/14 = 85,7 %; 5/6 = 83,3 %; 30/32 = 93,8 %. La lectura es clara: el margen y el ticket van bien, pero la tienda paga demasiado por cada cliente nuevo y devuelve demasiados pedidos. Las dos alertas rojas indican dónde mirar primero.
Paso 6: Diseño de la hoja Dashboard
- Arriba a la izquierda: el selector de mes (lista desplegable) y la fecha de actualización.
- Primera fila: tarjetas de los 3–4 KPI principales con valor grande, objetivo y variación frente al mes anterior:
=SI.ERROR(Real/Real_mes_anterior-1;""), con formato▲ 0,0 %;▼ 0,0 %. - Debajo: la tabla de todos los KPI con semáforo, como la del ejemplo.
- Abajo: un gráfico de líneas con los últimos 12 meses de ventas frente al objetivo y minigráficos (Insertar > Minigráficos) junto a cada KPI.
Cuando funcione, bloquea las hojas de cálculo y dashboard dejando libre solo el selector de mes; la guía para proteger fórmulas en Excel explica cómo hacerlo.
Errores frecuentes
- Demasiados KPI: con 20 indicadores, ninguno destaca.
- Semáforo sin sentido: marcar en verde un coste de adquisición que ha subido porque “es mayor que el objetivo”.
- Objetivos sin acordar: un indicador sin objetivo no puede estar en rojo ni en verde.
- Mezclar fuentes sin documentar: si las ventas de la tienda online y las de contabilidad no cuadran, la ficha debe decir cuál se usa.
- Introducir datos en la hoja Dashboard: se pierden al actualizar.
Preguntas frecuentes
¿Cuántos KPI debe tener un dashboard?
Entre 5 y 8 para la dirección de una pyme. Cada área puede tener su propio cuadro con indicadores más detallados.
¿Tabla dinámica o fórmulas?
Las tablas dinámicas con segmentaciones son rápidas para explorar datos. Para un cuadro de mando fijo con objetivos y semáforos, las fórmulas dan más control sobre el diseño. Se pueden combinar.
¿Funciona en Google Sheets?
Sí. SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, BUSCARX y el formato condicional existen en Google Sheets en español; los minigráficos se crean con la función SPARKLINE.
Más guías en Diseño de hojas de cálculo y dashboards.