Mesurer des KPI dans Excel semble simple jusqu’à ce que la feuille grandisse, que les données deviennent obsolètes et que personne ne fasse confiance au chiffre final. Voici les questions qui reviennent le plus souvent lorsqu’on tente de construire un véritable tableau de bord dans un tableur, avec des réponses directes et sans promesses magiques.
Quelle formule utiliser pour calculer un KPI mensuel qui ne casse pas lorsqu’on ajoute des lignes ?
La réponse courte : Tableau structuré (Ctrl+T) + références avec nom de colonne. Si vous écrivez =SOMME(B2:B500), toute nouvelle ligne en dehors de la plage est ignorée et le KPI ment sans prévenir. En convertissant la plage en tableau et en utilisant =SOMME(Tableau1[Mois]), Excel étend la référence automatiquement.
Une erreur que je vois souvent : utiliser RECHERCHEV avec des plages fixes pour récupérer l’objectif du mois. Lorsqu’une ligne est insérée au-dessus, la référence se décale et le KPI compare les ventes de mars avec l’objectif de février. Avec RECHERCHEX et un tableau structuré, cette erreur disparaît.
Comment comparer le KPI avec le mois précédent sans recalculer à la main ?
Avec une colonne auxiliaire de date et =SOMME.SI.ENS(Valeurs; Dates; “>=”&DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())-1;1); Dates; “<“&DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());1)). C’est la façon la plus robuste d’obtenir la période précédente complète, y compris les changements d’année.
Avertissement pratique : cette formule dépend du fait que les dates soient de vraies dates, pas du texte. Si le CSV exporté contient “01/03/2026” sous forme de chaîne, le calcul renvoie zéro et on croit qu’il n’y a pas eu de ventes. Vérifiez avec =ESTNUM(cellule) avant de blâmer la formule.
Excel est-il adapté aux KPI de plus de 100 000 lignes ?
Oui, mais avec des nuances. La limite est de 1 048 576 lignes par feuille, donc le volume n’est pas le problème ; le problème est la performance des formules volatiles (AUJOURDHUI(), MAINTENANT(), INDIRECT()) et des tableaux croisés dynamiques qui se recalculent à l’ouverture. Avec 200 000 lignes et plusieurs colonnes de SOMME.SI.ENS, la feuille peut mettre des dizaines de secondes à chaque modification.
Quand NE PAS utiliser Excel : si vous avez besoin d’une actualisation toutes les heures depuis une base de données, ou si plusieurs personnes modifient la même feuille en même temps sans contrôle de version. Là, Power Query + un entrepôt de données, ou un outil de BI, évite le chaos. Excel brille dans l’analyse ponctuelle et les tableaux de bord petits et maîtrisés.
Quels KPI a-t-il du sens de mettre dans un dashboard Excel ?
Cela dépend de l’entreprise, mais voici une structure qui fonctionne :
- Acquisition : sessions, coût par lead, taux de conversion du formulaire.
- Rétention : clients actifs, churn mensuel, fréquence d’achat.
- Revenus : panier moyen, marge brute, revenu récurrent.
- Efficacité : coût d’acquisition vs. valeur vie client.
Règle d’or : un KPI par ligne, une métrique par colonne et une colonne de date. Si vous mélangez les unités (pourcentages avec euros) dans la même colonne, le tableau croisé dynamique et les graphiques deviennent illisibles.
Comment éviter que le dashboard affiche des chiffres obsolètes ?
Deux mécanismes concrets :
- Power Query pour importer le CSV ou la requête SQL et actualiser en un clic (Données > Actualiser tout). Programmez l’actualisation à l’ouverture du fichier.
- Une cellule de contrôle avec la date de dernière actualisation : =SIERREUR(TEXTE(MAX(Tableau1[Date]);”dd/mm/aaaa”);”sans données”). Si cette date ne correspond pas à aujourd’hui, le KPI n’est pas fiable et celui qui le lit doit le savoir.
Cela ne garantit pas des données correctes : cela garantit que vous savez quand elles ont été chargées. La validation de la source reste votre responsabilité.
Dois-je mesurer les KPI dans Excel ou passer directement à Power BI ?
Si l’équipe compte une ou deux personnes, que les données tiennent dans une feuille et que le rapport est envoyé chaque semaine par e-mail, Excel suffit et est plus rapide à mettre en place. S’il y a de multiples sources, des permissions par rôle ou une actualisation en temps quasi réel, Power BI ou Looker Studio font gagner des heures. Selon la documentation officielle de Microsoft, Power Query est le même moteur dans Excel et dans Power BI, donc migrer ensuite n’implique pas de refaire les transformations : seulement le modèle et les visuels.
Ce qui ne fonctionne pas bien dans Excel : contrôler qui a modifié une formule clé. Il n’y a pas d’historique des modifications granulaire par cellule au-delà de la co-édition, et un #REF! peut passer inaperçu pendant des jours. Si le KPI alimente des décisions d’investissement, protégez-le avec une feuille masquée et un mot de passe, et documentez chaque formule dans un onglet de méthodologie.
Comment documenter la définition de chaque KPI ?
Avec un onglet « Dictionnaire » qui inclut : nom du KPI, formule exacte, source de la donnée, période de calcul et responsable. Sans cela, deux personnes calculent le même KPI de façon différente et la réunion se transforme en discussion sur les chiffres plutôt que sur les décisions. C’est la partie la moins spectaculaire et celle qui évite le plus de problèmes.