Un dashboard de compras es una hoja (o varias conectadas) que reúne, en una sola pantalla, el estado de las Órdenes de Compra, el comportamiento de los proveedores y los ahorros conseguidos en el período. Con él, de un vistazo se ve qué entregas están atrasadas, qué proveedor falla más y cuánto se ha ahorrado este mes, sin tener que revisar pedidos uno a uno.
En este tutorial vas a montar ese panel sobre un libro de ejemplo con seis hojas: Dashboard, Órdenes de Compra, Scorecard Proveedores, Negociaciones, Vencimientos y Registro de Ahorros. Al terminar tendrás un panel con KPIs que se calculan solos, un semáforo de entregas por colores, una puntuación ponderada de proveedores y alertas para los contratos que vencen. Funciona en Excel 2010 en adelante; las funciones nuevas (BUSCARX, matrices dinámicas) se mencionan solo donde aporta algo.
Qué es una dashboard de compras y para qué sirve
Una dashboard de compras es un panel visual, normalmente una hoja principal con números grandes y gráficos, que resume la actividad del área de compras de una empresa. La gracia está en que, en lugar de abrir seis informes para saber cómo va el mes, abres una sola hoja y lees lo importante en menos de un minuto.
En compras, lo importante suele ser: cuántas Órdenes de Compra (OC) están abiertas, cuántas entregas no han llegado a tiempo, cuánto se ha ahorrado respecto al presupuesto, qué proveedor cumple mejor y qué contratos están a punto de vencer. Cada una de esas preguntas se responde con un indicador (KPI) que se recalcula solo a partir de los datos de las otras hojas.
A diferencia de un informe de Excel, la dashboard no se imprime ni se envía por correo: se consulta a diario, como un cuadro de mando. Por eso se construye con fórmulas que apuntan a las hojas de detalle y con formato condicional que pinta las celdas en verde, amarillo o rojo según el estado.
Las hojas que forman el panel y cómo se conectan
El libro de ejemplo se llama Compras 2026.xlsx y tiene seis hojas que se reparten el trabajo. La hoja Dashboard solo muestra números y los trae de las demás con BUSCARV (VLOOKUP), CONTAR.SI.CONJUNTO (COUNTIFS) y SUMAR.SI (SUMIF); las otras cinco son la fuente de datos: allí se cargan los pedidos, se puntúan los proveedores y se apuntan los ahorros.
| Hoja | Qué contiene | Apoya a |
|---|---|---|
| Dashboard | KPIs, scorecard y resúmenes | (consume datos) |
| Órdenes de Compra | Cada pedido con proveedor, fecha, entrega y estado | Dashboard, Scorecard |
| Scorecard Proveedores | Notas 0-100 y media ponderada por proveedor | Dashboard |
| Negociaciones | Estado de cada negociación con proveedor | Dashboard |
| Vencimientos | Contratos con fecha de fin y días que faltan | Dashboard |
| Registro de Ahorros | Ahorro conseguido por cada OC | Dashboard |
Cada celda del Dashboard apunta a su hoja de origen con una referencia como 'Órdenes de Compra'!E:E. Si se cambia el nombre de una hoja, esas referencias se rompen y aparece #¡REF!; por eso conviene no renombrar las hojas a la ligera.
Los KPIs de arriba: OC abiertas, entregas pendientes y ahorros
Los tres números grandes que van arriba de la Dashboard (B2, B3 y B4) son el resumen del día: OC abiertas, entregas atrasadas y ahorros del mes en curso. Se calculan solos a partir de las hojas de detalle y se quedan ahí para que cualquier persona del equipo los vea al abrir el archivo.
Primero, escribe los datos de ejemplo en la hoja Órdenes de Compra. Los encabezados van en la fila 1 y los pedidos en las filas 2 a 11, con la columna E reservada al estado (Abierta, Cerrada, Atrasada) y la columna D a los días restantes (positivo si faltan, negativo si se pasó).
- En la hoja Órdenes de Compra, escribe los encabezados en A1:E1 y los 10 pedidos de la cuadrícula.
- En la hoja Registro de Ahorros, escribe los encabezados en A1:E1 y las 5 filas de la cuadrícula (una por OC cerrada con ganancia).
- En la hoja Dashboard, haz clic en B2 y escribe la fórmula que cuenta las OC abiertas.
- Copia la fórmula a B3 cambiando solo el criterio de estado.
- En B4, escribe la fórmula que filtra por el mes en curso.
| A | B | |
|---|---|---|
| 1 | Indicador | Valor |
| 2 | OC abiertas | 4 |
| 3 | OC atrasadas | 2 |
| 4 | Ahorros del mes | 1.250,00 |
| 5 | Proveedores activos | 5 |
Contar OC por estado con CONTAR.SI.CONJUNTO
CONTAR.SI.CONJUNTO (COUNTIFS) cuenta cuántas celdas de un rango cumplen uno o varios criterios. Aquí se usa con un solo criterio: que la columna E de Órdenes de Compra diga «Abierta». Al copiar la fórmula a B3 se cambia solo el texto del criterio, y el rango se queda igual.
| A | B | |
|---|---|---|
| 1 | Indicador | Valor |
| 2 | OC abiertas | 4 |
| 3 | OC atrasadas | 2 |
=CONTAR.SI.CONJUNTO('Órdenes de Compra'!E:E;"Abierta")Sumar los ahorros del mes en curso
SUMAR.SI (SUMIF) suma los importes de la columna E del Registro de Ahorros que cumplan una condición en la columna D (la fecha). El truco está en construir la fecha del primer día del mes en curso con FECHA (DATE), AÑO (YEAR) y MES (MONTH), y compararla como texto con ">=" concatenado con «&».
=SUMAR.SI('Registro de Ahorros'!D:D;">="&FECHA(AÑO(HOY());MES(HOY());1);'Registro de Ahorros'!E:E)Con los datos de ejemplo, da 1.250,00: la suma del único ahorro de octubre (1.250 €). Los demás ahorros son de meses anteriores y quedan fuera del filtro.
El semáforo de entregas: formato condicional con colores
El semáforo convierte la columna Días restantes de Órdenes de Compra en tres colores: verde si aún falta tiempo, amarillo si la entrega está próxima o recién vencida, rojo si ya pasó la fecha con margen. Se hace con formato condicional, una herramienta que pinta una celda según una regla sin que tengas que hacerlo a mano.
Se aplica sobre la columna D (Días restantes), con tres reglas: mayor o igual que 0 en verde, entre -3 y -1 en amarillo, y menor que -3 en rojo. El resultado es una columna que se lee de un vistazo: lo verde va bien, lo amarillo hay que vigilarlo, lo rojo ya llegó tarde.
Aplicar formato a todas las celdas según los valores
Resaltar toda la fila según la fecha de entrega
A veces no basta con colorear la celda de días restantes: conviene ver de un vistazo toda la fila del pedido que está mal. Para pintarla entera se usa una regla con fórmula que mire la celda D pero aplique el formato a las columnas A:F.
- Selecciona el rango A2:F11 en Órdenes de Compra.
- Abre Inicio > Estilos > Formato condicional > Nueva regla.
- Elige «Aplicar formato a todas las celdas según una fórmula».
- Escribe la fórmula
=$D2<-3y elige relleno rojo.
=$D2<-3Una alerta para los vencimientos de contrato
En la hoja Vencimientos hay una columna Días al vencimiento (positivos si faltan días, 0 si vence hoy). La idea es la misma: pintar de rojo lo vencido, de amarillo lo que vence en los próximos 15 días, y dejar sin color lo que aún tiene margen.
Se aplica formato condicional sobre la columna Días al vencimiento con tres reglas: valor 0 en rojo, entre 1 y 15 en amarillo, mayor que 15 sin color. La regla para «sin color» basta con no poner regla: lo que no cumple ninguna condición queda con su formato normal.
Aplicar formato a todas las celdas según los valores
El scorecard de proveedores con cuatro criterios
El scorecard es la puntuación que recibe cada proveedor en cuatro criterios: Entrega (cumplió fechas), Calidad (defectos), Precio (competitividad) y Documentación (facturas y albaranes al día). Cada criterio vale de 0 a 100, y la nota final es una media con pesos: 30% Entrega, 25% Calidad, 25% Precio y 20% Documentación.
Los pesos están en las celdas H2:H5 del propio Scorecard, así basta cambiarlos ahí para ajustar el resultado de todos los proveedores sin tocar las fórmulas. Para evitar que alguien escriba un valor raro en H2:H5 se aplica Validación de datos tipo Lista con los cuatro porcentajes.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Proveedor | OC | Entrega | Calidad | Precio | Doc. | Nota | Peso |
| 2 | Aceros del Sur | 150 | 85 | 90 | 78 | 92 | 86,25 | 0,30 |
| 3 | Suministros Norte | 120 | 70 | 80 | 88 | 75 | 78,75 | 0,25 |
| 4 | Logística Express | 95 | 95 | 85 | 70 | 88 | 85,00 | 0,25 |
| 5 | Papeles y Más | 60 | 60 | 95 | 92 | 80 | 80,75 | 0,20 |
| 6 | Componentes Este | 40 | 90 | 75 | 65 | 85 | 79,50 |
=C2*$H$2+D2*$H$3+E2*$H$4+F2*$H$5Sobre la columna G se aplica una barra de datos (Inicio > Estilos > Formato condicional > Barras de datos) para que la nota se vea como una barra horizontal: la barra más larga es el mejor proveedor.
Cambiar los pesos del scorecard con celdas auxiliares
Si en lugar de escribir los pesos a mano en la fórmula los dejas en celdas auxiliares, puedes ajustarlos sin tocar la fórmula y sin miedo a romperla. La validación de datos limita las celdas H2:H5 a una lista desplegable con los cuatro valores válidos (0,30; 0,25; 0,25; 0,20), de modo que la suma siempre sea 1 y nadie pueda escribir, por error, «30» en vez de «0,30».
Por qué los KPIs muestran 0 o #¡REF!
Los tres errores más habituales al montar una dashboard son: referencias que se rompen al renombrar hojas, formato condicional que no pinta la fila entera y SUMAR.SI que devuelve 0 porque los importes son texto en lugar de números. Los tres tienen la misma pinta (un número que no cuadra o un mensaje de error) pero arreglos muy distintos.
| Síntoma | Causa probable | Arreglo |
|---|---|---|
| La celda muestra #¡REF! | Se renombró una hoja sin actualizar la fórmula | Reescribir la fórmula con el nombre nuevo de la hoja |
| El formato solo pinta D2 | La regla no ancla la columna con $ | Reescribir la regla con $D2 |
| SUMAR.SI suma 0 | La columna tiene texto en vez de números | Datos > Herramientas de datos > Texto en columnas |
La referencia a otra hoja no se actualiza al cambiar el nombre
Cuando se renombra la hoja «Órdenes de Compra» a «OC», las fórmulas del Dashboard que decían 'Órdenes de Compra'!E:E se quedan como estaban apuntando al nombre antiguo, y Excel devuelve #¡REF!. El arreglo es volver a escribir cada fórmula con el nombre nuevo o, más cómodo, usar el botón de autocompletar al escribir el nombre de la hoja en la barra de fórmulas; Excel lo actualiza solo.
| A | B | |
|---|---|---|
| 1 | Indicador | Valor |
| 2 | OC abiertas | #¡REF! |
| 3 | OC atrasadas | #¡REF! |
El formato condicional no pinta la fila completa
El error clásico al colorear toda la fila es olvidar el ancla de columna en la fórmula de la regla. Si escribes =D2<-3, Excel evalúa la celda D2 (que vale -10) y la pinta de rojo, pero al pasar a la celda C2 evalúa C2 (que vale «Suministros Norte», un texto) y no pinta nada. La fórmula correcta es =$D2<-3, con el dólar solo delante de la D para que la columna quede fija y la fila cambie al moverse a la derecha.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Proveedor | Producto | Cantidad | Días rest. | Estado | Importe |
| 2 | Aceros del Sur | Chapa 2mm | 500 | -10 | Atrasada | 4.500 |
| 3 | Suministros Norte | Tornillería | 1.200 | -7 | Atrasada | 1.800 |
| 4 | Logística Express | Embalaje | 300 | 5 | Abierta | 950 |
| 5 | Papeles y Más | Cartón | 800 | 12 | Abierta | 620 |
$D2<-3, las filas 2 y 3 aparecen en rojo y las demás, sin pintar. Si solo D2 y D3 aparecen rojas, falta el dólar en la regla.SUMAR.SI suma importes con texto y da error
Cuando una columna de importes se importa de otro programa o se copia de una web, muchas veces llega como texto: «1.250,00» en lugar de 1250. SUMAR.SI ignora los textos y devuelve 0, aunque visualmente el dato esté. El arreglo más rápido es seleccionar la columna, ir a Datos > Herramientas de datos > Texto en columnas y aceptar; eso convierte el texto en número. Otra opción, sin tocar los datos, es multiplicar por 1 dentro de la fórmula.
Lo que tu versión de Excel trae y lo que no
Power Query, la herramienta que conecta el panel con archivos externos para que se actualice solo, está integrada en Excel desde 2016; en 2013 viene como complemento. BUSCARX y las matrices dinámicas (escribir una fórmula en E2 y que se «derrame» sola a E2:E10) solo existen en Excel 2021, 2024 y Microsoft 365.
Para los KPIs que se enseñan aquí no hace falta nada de eso: CONTAR.SI.CONJUNTO, SUMAR.SI, BUSCARV y el formato condicional están en Excel desde 2007, así que el panel funciona igual en cualquier versión de los últimos años. Donde sí hay diferencia es en opciones avanzadas de formato condicional (alertas con iconos personalizados, reglas que dependen de otra hoja): están en Excel 2010 en adelante, pero en Excel para la web faltan algunos diálogos y hay que crearlas desde Inicio > Formato condicional con menos controles.
| Función o herramienta | Versión mínima |
|---|---|
| CONTAR.SI.CONJUNTO / SUMAR.SI / SUMAR.SI.CONJUNTO | Excel 2007 |
| BUSCARV | Excel 2000 |
| Formato condicional con reglas «según fórmula» | Excel 2007 |
| Conjuntos de iconos en formato condicional | Excel 2010 |
| Power Query integrado | Excel 2016 |
| BUSCARX y matrices dinámicas | Excel 2021 y Microsoft 365 |
Lo que ya sabes hacer con tu dashboard de compras
- Tienes tres KPIs automáticos en la hoja Dashboard que cuentan OC abiertas, atrasadas y suman ahorros del mes.
- El semáforo de Órdenes de Compra colorea de verde, amarillo o rojo según los días restantes, y resalta la fila entera si está muy vencida.
- Los vencimientos de contrato se avisan solos con el mismo semáforo en la hoja Vencimientos.
- El scorecard ponderado puntúa a cada proveedor con cuatro notas y permite ajustar los porcentajes con una validación de lista.
- Las seis hojas se conectan con BUSCARV, CONTAR.SI.CONJUNTO y SUMAR.SI sin pegar datos a mano.
- La columna de Nota del scorecard lleva una barra de datos que muestra visualmente quién es el mejor proveedor.
El siguiente paso natural es conectar el libro con Power Query: en lugar de escribir las nuevas OC a mano, se importa el CSV de pedidos cada mañana y el panel se actualiza con Datos > Actualizar todo. Así el dashboard pasa de actualizarse a mano una vez por semana a hacerlo solo cada mañana.
Dudas al montar un dashboard de compras en Excel
1. ¿Cómo hacer una plantilla en Excel que controle las compras de la empresa?
Crea una tabla con Inicio > Dar formato como tabla para que cada nueva fila se registre sola. Añade columnas de proveedor, fecha, importe, IVA y forma de pago, y al final una fila de totales con SUMA. Después inserta un gráfico dinámico en Insertar > Gráficos para ver el gasto por proveedor o por mes sin tocar la tabla.
2. ¿Cómo llevar en Excel el IVA de compras y ventas para la declaración?
Usa dos tablas separadas, una para ventas con base imponible, IVA repercutido y total, y otra igual para compras con IVA soportado. Con SUMAR.SI.CONJUNTO puedes obtener los totales por trimestre, y una simple resta te da el IVA a ingresar o a compensar. En tu panel mensual coloca un pequeño cuadro con base imponible, IVA repercutido, IVA soportado y resultado.
3. ¿Cuál es la mejor forma de llevar un control de compras sencillo en Excel?
La forma más sencilla es una tabla con una fila por compra: fecha, proveedor, descripción, unidades, precio unitario, IVA y total. Al convertirla en tabla con Inicio > Dar formato como tabla, los totales y los filtros aparecen solos, y arrastrando el controlador de relleno o usando Tabla dinámica puedes agrupar el gasto por mes o por proveedor en segundos.