Tableau de bord KPI sur Excel : choisir ses indicateurs, les calculer et les afficher avec des alertes
Créer un tableau de bord KPI sur Excel pour une TPE : choix de 5 à 8 indicateurs, fiche indicateur, calculs avec SOMME.SI.ENS, délai moyen de paiement clients, alertes vert-orange-rouge selon le sens de l'indicateur, et exemple chiffré.
Réponse rapide : un tableau de bord KPI sur Excel tient sur une seule page et affiche 5 à 8 indicateurs clés, chacun avec sa valeur du mois, son objectif, l’évolution par rapport au mois précédent et une alerte vert / orange / rouge. Il s’appuie sur trois feuilles : les données (ventes, devis, factures, temps passés), les calculs (formules SOMME.SI.ENS, NB.SI.ENS) et le tableau de bord lui-même, qui n’affiche que des résultats. La qualité du tableau de bord dépend d’abord du choix des indicateurs et de leur définition précise.
Les fonctions sont celles d’Excel en français, avec le point-virgule comme séparateur. Les chiffres de l’exemple sont fictifs.
Étape 1 : choisir les bons indicateurs
Un KPI doit être relié à un objectif et déclencher une décision quand il dérive. Pour une TPE de services ou de commerce, une sélection courante :
| Domaine | Indicateur | Ce qu’il dit |
|---|---|---|
| Activité | Chiffre d’affaires HT du mois | Le volume d’activité par rapport à l’objectif |
| Rentabilité | Taux de marge brute | Ce qu’il reste après les achats consommés |
| Commercial | Taux de transformation des devis | L’efficacité commerciale |
| Clients | Délai moyen de paiement clients (DSO) | Le temps que mettent les clients à payer |
| Trésorerie | Solde bancaire de fin de mois | La capacité à faire face aux échéances |
| Productivité | Taux d’heures facturables | La part du temps réellement vendue |
Pour chaque indicateur, rédigez une courte fiche : définition, formule, source, fréquence, objectif, sens (plus c’est haut, mieux c’est, ou l’inverse), seuil d’alerte et responsable. La fiche évite les débats du type « le CA, c’est HT ou TTC ? ».
Étape 2 : organiser les données
Chaque source devient un tableau Excel (Ctrl+L) avec une colonne Mois calculée par =FIN.MOIS([@Date];-1)+1 :
Ventes: date, client, montant HT, coût d’achat.Devis: numéro, date, montant, statut (Accepté, Refusé, Expiré, En attente) — par exemple issu de votre registre de devis.Factures: numéro, date, montant TTC, reste dû — issu du registre de votre modèle de facture.Temps: date, personne, heures, facturable (Oui/Non).
Étape 3 : les formules de calcul
Dans la feuille Calculs, le mois choisi se trouve en TdB!C2 et le mois précédent en =MOIS.DECALER(TdB!C2;-1).
| Indicateur | Formule |
|---|---|
| CA HT | =SOMME.SI.ENS(Ventes[Montant HT];Ventes[Mois];TdB!$C$2) |
| Taux de marge brute | =SIERREUR(1-SOMME.SI.ENS(Ventes[Coût];Ventes[Mois];TdB!$C$2)/CA;"") |
| Taux de transformation | =SIERREUR(NB.SI.ENS(Devis[Statut];"Accepté";Devis[Mois];TdB!$C$2)/(NB.SI.ENS(Devis[Mois];TdB!$C$2)-NB.SI.ENS(Devis[Statut];"En attente";Devis[Mois];TdB!$C$2));"") |
| DSO (jours) | =SIERREUR(SOMME(Factures[Reste dû])/SOMME.SI.ENS(Factures[TTC];Factures[Date];">"&MOIS.DECALER(FinMois;-3);Factures[Date];"<="&FinMois)*90;"") |
| Taux d’heures facturables | =SIERREUR(SOMME.SI.ENS(Temps[Heures];Temps[Facturable];"Oui";Temps[Mois];TdB!$C$2)/SOMME.SI.ENS(Temps[Heures];Temps[Mois];TdB!$C$2);"") |
| Trésorerie | Solde bancaire au dernier jour du mois, saisi ou repris du plan de trésorerie |
Le DSO de cette méthode simple rapporte les créances clients restant dues au chiffre d’affaires TTC des trois derniers mois, multiplié par 90 jours. Exemple : 72 800 € de créances et 126 000 € TTC facturés sur trois mois donnent 72 800 / 126 000 × 90 = 52 jours.
Étape 4 : des alertes qui respectent le sens de l’indicateur
Calculez d’abord un taux d’atteinte pour que 100 % soit toujours « bien » :
=SIERREUR(SI(Sens="Plus haut";Réel/Objectif;Objectif/Réel);"")
Puis l’alerte : =SI(Atteinte="";"";SI(Atteinte>=1;"Vert";SI(Atteinte>=Seuil;"Orange";"Rouge"))). Associez une mise en forme conditionnelle et, pour la lisibilité, un symbole (●, ▲, ▼) en plus de la couleur.
Exemple : tableau de bord d’octobre, seuil orange à 90 %
| Indicateur | Réel | Objectif | Sens | Atteinte | Alerte |
|---|---|---|---|---|---|
| CA HT | 42 000 € | 45 000 € | Plus haut | 93,3 % | Orange |
| Taux de marge brute | 38 % | 35 % | Plus haut | 108,6 % | Vert |
| Taux de transformation des devis | 32 % | 30 % | Plus haut | 106,7 % | Vert |
| DSO | 52 j | 45 j | Plus bas | 86,5 % | Rouge |
| Trésorerie fin de mois | 18 000 € | 15 000 € | Plus haut | 120,0 % | Vert |
| Heures facturables | 72 % | 75 % | Plus haut | 96,0 % | Orange |
Vérification : 42 000 / 45 000 = 93,3 % ; 38 / 35 = 108,6 % ; 32 / 30 = 106,7 % ; 45 / 52 = 86,5 % ; 18 000 / 15 000 = 120 % ; 72 / 75 = 96 %. Lecture : l’activité est un peu en dessous de l’objectif, mais la priorité du mois est le recouvrement : les clients paient en 52 jours au lieu de 45, ce qui immobilise de la trésorerie. Une relance systématique des factures échues est l’action la plus utile.
Étape 5 : la mise en page du tableau de bord
- En haut à gauche : la liste déroulante du mois et la date de mise à jour.
- Première ligne : 3 ou 4 cartes (valeur en grand, objectif, évolution
=SIERREUR(Réel/Réel_M-1-1;"")au format▲ 0,0 %;▼ 0,0 %). - Au centre : le tableau de tous les indicateurs avec leurs alertes.
- En bas : une courbe du CA sur 12 mois face à l’objectif, et des graphiques sparkline (Insertion > Graphiques sparkline) à côté de chaque indicateur.
Protégez les feuilles Calculs et Tableau de bord en ne laissant modifiable que la cellule du mois.
Erreurs fréquentes
- Trop d’indicateurs : au-delà de huit, plus rien ne ressort.
- Alerte à contresens : un DSO qui monte n’est pas une bonne nouvelle, même s’il « dépasse l’objectif ».
- Objectifs absents ou non partagés : sans objectif, pas d’alerte.
- Mélanger HT et TTC d’un indicateur à l’autre sans le préciser.
- Saisir des chiffres directement dans le tableau de bord au lieu des feuilles de données.
Questions fréquentes
Combien d’indicateurs prévoir ?
Cinq à huit pour la direction d’une TPE. Chaque fonction peut avoir son propre tableau de bord plus détaillé.
Faut-il un tableau croisé dynamique ?
Les tableaux croisés dynamiques et les segments sont parfaits pour explorer. Pour un tableau de bord fixe avec objectifs et alertes, les formules donnent plus de maîtrise sur la mise en page. Les deux peuvent cohabiter.
Et sur Google Sheets ?
SOMME.SI.ENS, NB.SI.ENS, MOIS.DECALER et la mise en forme conditionnelle fonctionnent ; les mini-graphiques se créent avec la fonction SPARKLINE.
D’autres guides dans Conception de tableurs et tableaux de bord.