Tableau de bord Excel : 3 à 5 KPI et des filtres synchronisés, sans VBA

Un tableau de bord Excel rassemble les indicateurs essentiels d’une activité sur une page de synthèse. Pour le construire, partez de données structurées, utilisez des tableaux croisés dynamiques pour les résumer, puis ajoutez des graphiques et des filtres. Cette méthode permet de suivre les résultats, de repérer les écarts et d’explorer les chiffres sans programmer de macros VBA.

Prenons un suivi commercial comme fil conducteur : vous souhaitez visualiser le chiffre d’affaires, son évolution et les performances par région. La même architecture convient aux stocks, aux projets ou au suivi budgétaire, en adaptant les indicateurs.

1. Partir des décisions à prendre, pas des graphiques

Un dashboard n’est pas une collection de chiffres. Il doit répondre à trois questions : quelle est la situation, comment évolue-t-elle et quels points demandent une action ? Avant d’ouvrir les menus d’Excel, précisez son destinataire, sa fréquence de consultation et la décision qu’il doit faciliter.

Un responsable commercial cherchera les régions en retrait. Un gestionnaire de stocks surveillera les ruptures. Un chef de projet examinera les retards et les dépassements budgétaires. Ces besoins déterminent les indicateurs clés de performance, ou KPI.

Retenir 3 à 5 indicateurs avec une définition stable

Pour une première version, limitez-vous à 3 à 5 KPI. Pour chacun, définissez le calcul, l’unité, la période et la référence de comparaison. Un chiffre d’affaires mensuel n’a pas le même sens selon qu’il inclut les taxes, les avoirs ou uniquement les factures réglées.

• Ventes : chiffre d’affaires, marge, nombre de commandes, écart à l’objectif.
• Stocks : quantité disponible, articles en rupture, valeur immobilisée.
• Projets : avancement, tâches en retard, budget consommé.

Pensez votre tableau de bord comme un système en orbite autour d’une décision centrale : chaque indicateur doit entretenir un lien direct avec elle. Si l’objectif est de prévenir les ruptures, la valeur totale du stock ne suffit pas ; rapprochez disponibilité, consommation et délai de réapprovisionnement. Ce raisonnement évite qu’un indicateur spectaculaire, mais périphérique, détourne l’attention du véritable risque.

2. Organiser un classeur qui supporte les prochaines mises à jour

Créez trois feuilles : Données pour les enregistrements sources, Traitement pour les calculs et les tableaux croisés dynamiques, puis Dashboard pour la restitution. Cette séparation simplifie la maintenance et évite que les tableaux de calcul perturbent la présentation.

Construire une base exploitable

Utilisez une ligne par opération, avec des colonnes homogènes et des en-têtes uniques. Pour notre exemple, une ligne représente une vente et comprend une date, une région, un produit, une quantité et un chiffre d’affaires. Si une commande comporte plusieurs lignes, ajoutez un identifiant de commande pour éviter de confondre lignes vendues et commandes.

Évitez les cellules fusionnées, les sous-totaux et les lignes vides dans cette base. Vérifiez que les dates sont reconnues comme dates et que les montants sont numériques. Recherchez les doublons selon une clé métier pertinente : deux ventes identiques ne sont pas nécessairement une erreur.

Sélectionnez la base, puis utilisez Insertion > Tableau et confirmez la présence d’en-têtes. Nommez le tableau, par exemple Ventes. Les nouvelles lignes intégrées à ce tableau pourront être prises en compte lors de l’actualisation des analyses.

Choisir entre formules, TCD et Power Query

Les formules conviennent aux calculs ciblés et aux synthèses simples. Les tableaux croisés dynamiques, ou TCD, regroupent les données par mois, région ou produit sans multiplier les formules. Power Query intervient en amont : il importe, nettoie et consolide des fichiers ou des sources externes selon des étapes réutilisables.

3. Transformer les données en indicateurs et graphiques lisibles

Depuis une cellule du tableau Ventes, choisissez Insertion > Tableau croisé dynamique, puis placez le résultat dans la feuille Traitement. Les intitulés des commandes peuvent varier selon votre version d’Excel.

1. Glissez Région dans les lignes et CA HT dans les valeurs.
2. Vérifiez que le calcul affiche une somme, et non un nombre d’enregistrements.
3. Créez un deuxième TCD pour l’évolution du chiffre d’affaires par date.
4. Regroupez les dates par mois et par années si plusieurs années sont présentes.
5. Réservez suffisamment d’espace entre les TCD pour leur développement.

Pour faciliter les futures connexions de filtres, copiez le premier TCD puis modifiez ses champs. Vous conservez ainsi une base commune. Donnez aux tableaux des noms explicites, tels que CA_Region et CA_Mensuel.

Associer chaque visuel à une question

Insérez un graphique croisé dynamique depuis chaque TCD utile, puis déplacez-le dans Dashboard. Pour les cartes KPI, reliez des cellules de présentation aux résultats de synthèse ; évitez de ressaisir les montants. Vérifiez notamment qu’un taux de marge utilise la marge totale divisée par le chiffre d’affaires total, et non une moyenne simple des taux individuels.

Gardez un plafond de 5 KPI et 4 graphiques pour cette première page. Utilisez au maximum trois couleurs fonctionnelles : principale, alerte et neutre. Masquez le quadrillage, alignez les éléments et accompagnez les alertes d’un libellé : la couleur seule ne suffit pas.

4. Synchroniser les filtres sans créer d’ambiguïté

Cliquez dans un TCD, puis choisissez Analyse du tableau croisé dynamique > Insérer un segment. Sélectionnez Région ou Produit. Le segment affiche des boutons permettant de filtrer les résultats sans manipuler les données sources.

Pour piloter plusieurs tableaux, sélectionnez le segment, ouvrez Connexions de rapport et cochez les TCD concernés. S’ils n’apparaissent pas, vérifiez qu’ils partagent un cache ou un modèle de données compatible. Des TCD créés séparément ne sont pas toujours connectables au même segment.

Pour filtrer les dates, ajoutez une chronologie depuis les outils du TCD, si cette fonctionnalité est disponible dans votre version. Elle permet de sélectionner une période. Le champ utilisé doit contenir de véritables dates Excel.

Testez ensuite une région, plusieurs régions et une période sans ventes. Tous les visuels censés se synchroniser doivent suivre la sélection. Un objectif annuel fixe ne doit pas sembler filtré par mois s’il ne l’est pas réellement : indiquez clairement son périmètre. Prévoyez également une remise à zéro des filtres facilement identifiable.

5. Fiabiliser l’actualisation et le partage

Après l’ajout de données, utilisez Données > Actualiser tout. Selon les propriétés des connexions et des TCD, vous pouvez activer l’actualisation à l’ouverture. Un tableau structuré étend la source, mais ne garantit pas à lui seul le rafraîchissement des résultats affichés.

Avec Power Query, contrôlez aussi l’ordre des opérations : importation, transformation, chargement, puis mise à jour des analyses. Les possibilités d’actualisation diffèrent entre Excel sur ordinateur, Excel pour le Web et les environnements de partage. Testez le scénario réellement utilisé par vos collègues.

• Comparez le total affiché avec un total de contrôle indépendant.
• Vérifiez les filtres actifs, les périodes et les valeurs manquantes.
• Testez l’ajout de lignes et l’agrandissement des TCD.
• Affichez la période couverte et la date de dernière actualisation vérifiée.
• Contrôlez la lisibilité sur l’écran et le support de diffusion prévus.

Pour une consultation figée, un PDF peut suffire. Pour conserver l’interactivité, partagez le classeur avec des droits adaptés. Masquer une feuille ou protéger ses cellules ne constitue pas une protection de confidentialité : retirez les données sensibles avant diffusion.

Enfin, si les fichiers deviennent difficiles à maintenir, si les actualisations sont lentes ou si chaque utilisateur doit accéder à un périmètre distinct, évaluez un outil comme Power BI. Excel reste pertinent tant que le classeur demeure compréhensible, contrôlable et simple à actualiser.