Excel : créer un tableau de bord financier dynamique

Ce que vous saurez faire à la fin

1. Structurer un fichier Excel dashboard en 3 feuilles propres
2. Créer les KPIs clés (CA, marge, DSO) avec formules dynamiques
3. Filtrer tout le dashboard avec 2-3 segments connectés
4. Actualiser automatiquement à l’ouverture du fichier
5. Mettre en forme conditionnelle pour alerter visuellement

Durée : 2 heures. Pré-requis : Excel 365 ou 2021. Données de vente mensuelles avec 500-5000 lignes.

Étape 1 — Structurer en 3 feuilles

1. Ouvrez un nouveau classeur Excel. Enregistrez sous Dashboard-Financier-2026.xlsx.
2. Renommez Feuil1 en DATA (double-clic sur l’onglet). Couleur onglet : gris (clic droit > Couleur de l’onglet > gris).
3. Ajoutez 2 nouvelles feuilles : clic droit sur un onglet > Insérer > Feuille de calcul. Nommez-les DASHBOARD (bleu) et PARAM (vert).
4. Rôles :
   • DATA = données brutes, jamais touchées visuellement
   • DASHBOARD = la vue destinée aux utilisateurs, lecture seule
   • PARAM = listes de référence (régions, comptes, seuils d’alerte)
5. Sur DASHBOARD, masquez les lignes de grille : Affichage > décochez Quadrillage. Look plus épuré.

Étape 2 — Préparer les données dans DATA

1. Collez vos 500-5000 lignes de ventes dans DATA à partir de A1.
2. Colonnes type : Date | Region | Commercial | Produit | CA | Cout | Marge.
3. Vérifiez chaque colonne : format Date pour Date (pas texte), format numérique pour CA/Cout.
4. Sélectionnez A1 jusqu’à la dernière cellule. Appuyez Ctrl+T. Validez Mon tableau a des en-têtes.
5. Onglet contextuel Création de tableau > Nom du tableau : saisissez tblVentes. Entrée.
6. Test : ajoutez une ligne tout en bas, Excel doit l’intégrer automatiquement au tableau.

Étape 3 — KPI 1 : CA filtré par période et région

1. Allez sur DASHBOARD. En A1, tapez « Dashboard Financier 2026 ». Formatez en taille 20, gras.
2. Créez deux cellules de filtre utilisateur :
   • A3 : label « Région : ». B3 : cellule de saisie. Bordure épaisse bleue.
   • A4 : label « Mois : ». B4 : cellule de saisie. Idem.
3. Ajoutez une validation de données sur B3 :
   • Sélectionnez B3. Onglet Données > Validation des données.
   • Autoriser : Liste. Source : =PARAM!$A$2:$A$10.
   • OK. Un menu déroulant apparaît.
4. Sur PARAM en A1, tapez « Regions ». En A2:A10 listez vos régions.
5. Répétez pour B4 avec la liste des mois en PARAM!B2:B13 (1 à 12).
6. KPI 1 en A7 :

=SOMME.SI.ENS(tblVentes[CA];
    tblVentes[Region]; B3;
    tblVentes[Date]; ">="&DATE(2026;B4;1);
    tblVentes[Date]; "<"&DATE(2026;B4+1;1))

7. Formatez la cellule : taille 28, gras, format numérique #,##0 "FCFA".
8. Test : changez B3 et B4, la valeur se met à jour instantanément.

Étape 4 — KPI 2 : Marge pondérée moyenne

1. En A10, label « Marge pondérée ».
2. En A11, formule :

=SOMMEPROD(
    (tblVentes[Region]=B3) * 
    (tblVentes[CA]) * 
    (tblVentes[Marge])
  )
 / SOMME.SI.ENS(tblVentes[CA]; tblVentes[Region]; B3)

3. Format : pourcentage, 1 décimale. Couleur verte si > 25 %, rouge si < 15 %.
4. Pour la mise en forme conditionnelle :
   • Sélectionnez A11. Accueil > Mise en forme conditionnelle > Nouvelle règle.
   • Mettre en forme uniquement les cellules qui contiennent.
   • Valeur de la cellule > supérieure à > 0.25. Format : fond vert clair. OK.
   • Nouvelle règle : valeur inférieure à 0.15. Fond rouge clair.

Étape 5 — KPI 3 : Évolution N vs N-1

1. Label en A14 : « Évolution vs mois précédent ».
2. Formule en A15 :

=LET(
  ca_actuel; SOMME.SI.ENS(tblVentes[CA];
              tblVentes[Region]; B3;
              tblVentes[Date]; ">="&DATE(2026;B4;1);
              tblVentes[Date]; "<"&DATE(2026;B4+1;1));
  ca_prec; SOMME.SI.ENS(tblVentes[CA];
            tblVentes[Region]; B3;
            tblVentes[Date]; ">="&DATE(2026;B4-1;1);
            tblVentes[Date]; "<"&DATE(2026;B4;1));
  SI(ca_prec=0; "N/A"; (ca_actuel - ca_prec) / ca_prec))

3. Format pourcentage. MFC : vert si positif, rouge si négatif.

Étape 6 — Évolution mensuelle 12 mois (graphique)

1. Sur PARAM en D1:E13, créez un mini-tableau pour la source du graphique :
   • D1 : Mois. E1 : CA.
   • D2:D13 : =DATE(2026;LIGNE()-1;1) tiré vers le bas.
   • E2 :

=SOMME.SI.ENS(tblVentes[CA];
    tblVentes[Region]; DASHBOARD!$B$3;
    tblVentes[Date]; ">="&D2;
    tblVentes[Date]; "<"&D3)

2. Tirez E2 vers E13.
3. Sur DASHBOARD : sélectionnez la zone où vous voulez le graphique (par ex D3:K15).
4. Insertion > Graphique en courbes > Ligne avec marqueurs.
5. Clic droit sur le graphique > Sélectionner des données. Source : =PARAM!$E$2:$E$13. Étiquettes axe X : =PARAM!$D$2:$D$13.
6. Supprimez le titre redondant, la légende. Ajoutez les labels des valeurs (clic sur la courbe > +).
7. Couleur courbe : bleu marque. Épaisseur 3 pt. Marqueurs en cercles.

Étape 7 — Top 5 commerciaux

1. En F3 sur DASHBOARD, label « Top 5 commerciaux ».
2. En F4 :

=PRENDRE(
  TRIERPAR(
    UNIQUE(tblVentes[Commercial]);
    SOMME.SI(tblVentes[Commercial]; UNIQUE(tblVentes[Commercial]); tblVentes[CA]);
    -1);
  5)

3. En G4, CA correspondant :

=SOMME.SI(tblVentes[Commercial]; F4#; tblVentes[CA])

4. Le # après F4 indique la plage de débordement complète.
5. Format G4:G8 en #,##0 "FCFA".
6. Ajoutez une barre de données MFC : sélectionnez G4:G8 > Accueil > MFC > Barres de données > Bleu.

Étape 8 — Segment (Slicer) connecté

Remplacez les menus déroulants par des segments plus visuels.

1. Créez un TCD à partir de tblVentes : Insertion > Tableau croisé dynamique > Nouvelle feuille.
2. Dans le TCD, cochez juste Région en Lignes et CA en Valeurs.
3. Cliquez dans le TCD. Analyse du TCD > Insérer un segment > cochez Region.
4. Déplacez le segment sur DASHBOARD (copier/coller).
5. Masquez la feuille du TCD : clic droit sur l’onglet > Masquer.
6. Modifier les formules : remplacez B3 par LIREDONNEESTABCROISDYNAMIQUE ou gardez B3 et ajoutez un lien entre segment et B3 via une macro :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ' Quand le segment change le TCD, récupérer la région sélectionnée
    Dim pf As PivotField
    Set pf = Target.PivotFields("Region")
    If pf.VisibleItemsList(1) = "(All)" Then
        Range("B3").Value = "Toutes"
    Else
        Range("B3").Value = pf.VisibleItemsList(1)
    End If
End Sub

7. Alternative plus simple : gardez vos listes déroulantes et oubliez les segments pour un dashboard basique.

Étape 9 — Tableau détail avec MFC

1. Sur DASHBOARD, zone J3:N30 pour un tableau de commandes filtrées.
2. J3 : formule dynamique FILTRE :

=FILTRE(
  tblVentes;
  (tblVentes[Region]=B3) * 
  (MOIS(tblVentes[Date])=B4);
  "Aucune ligne")

3. La formule déborde automatiquement. Appliquez un format tableau propre (bordures, en-têtes bleus).
4. Sélectionnez la colonne CA du tableau débordé : MFC > Échelle 3 couleurs (rouge-jaune-vert selon valeur).

Étape 10 — Sparklines dans les cellules

1. Pour chaque commercial dans le top 5, ajoutez une mini-courbe 12 mois en H4:H8.
2. Sélectionnez H4. Insertion > Sparklines > Ligne.
3. Plage de données : la ligne de ce commercial dans un tableau intermédiaire mensuel sur PARAM.
4. Étendez H4 vers H8.
5. Style : couleur bleu, marker point élevé en rouge, point bas en gris.

Étape 11 — Protection du dashboard

1. Sélectionnez les cellules de SAISIE utilisateur (B3, B4). Clic droit > Format de cellule > Protection > décochez Verrouillée. OK.
2. Révision > Protéger la feuille. Cochez :
   • Sélectionner les cellules déverrouillées
   • Utiliser les graphiques

   Décochez tout le reste.

3. Mot de passe : « dash2026 » (exemple).
4. L’utilisateur final peut changer B3 et B4 mais rien d’autre. Le dashboard ne peut pas être cassé.

Étape 12 — Actualisation automatique

1. Si votre source est un TCD ou Power Query : Données > Requêtes et connexions > clic droit sur la requête > Propriétés.
2. Cochez Actualiser à l’ouverture du fichier et Actualiser toutes les N minutes : 15.
3. Pour que le TCD se mette à jour quand de nouvelles lignes sont ajoutées : clic droit dans le TCD > Options du TCD > Données > cocher Actualiser à l’ouverture.
4. Ajoutez un bouton Actualiser sur DASHBOARD : Développeur > Insérer > Bouton de formulaire. Affectez la macro :

Sub ActualiserDashboard()
    Application.ScreenUpdating = False
    ThisWorkbook.RefreshAll
    Application.ScreenUpdating = True
    MsgBox "Dashboard actualisé", vbInformation
End Sub

Étape 13 — Tester sur 3 scénarios

1. Scénario 1 : Région Dakar, Mois mars. Valeurs attendues : cohérentes avec DATA.
2. Scénario 2 : Région vide (sélectionnez une région sans données). Attendu : 0 partout, pas #DIV/0!.
3. Scénario 3 : Mois futur sans données. Attendu : 0, pas d’erreur.
4. Protégez avec SIERREUR toutes les formules qui peuvent planter :

=SIERREUR(votre_formule; 0)

Étape 14 — Partager le fichier

1. Enregistrez d’abord la version master (avec macros) en .xlsm.
2. Pour la diffusion à des lecteurs non-admin : Fichier > Informations > Protéger le classeur > Marquer comme final.
3. OneDrive / SharePoint : Fichier > Partager > destinataires en Peut afficher.
4. Alternative : publiez en PDF un snapshot hebdomadaire via Fichier > Exporter > PDF.
5. Pour distribution large sans Excel : Power BI (import Excel direct, mise à jour automatique).

Étape 15 — Maintenance mensuelle

1. Premier jour du mois : remplacez DATA par les nouvelles données consolidées du mois N-1.
2. Cliquez Actualiser tout (Ctrl+Alt+F5).
3. Vérifiez les 3 KPI principaux visuellement.
4. Sauvegardez une copie archive : Dashboard-Financier-2026-[AAAAMM].xlsx.
5. Comparez avec le mois précédent : les évolutions ont-elles du sens ? Ecarts trop importants = erreur à investiguer.

Erreurs courantes

• #DIV/0! : une division par zéro. Enveloppez avec SIERREUR(formule; 0).
• #N/A sur RECHERCHEX : le 4ᵉ argument (valeur si non trouvée) n’est pas défini. Ajoutez "" ou 0.
• Valeurs qui ne bougent pas : formules calculées sur mode manuel. Formules > Options de calcul > Automatique.
• Le fichier devient très lent : limite SOMMEPROD sur colonnes entières. Préférez plages explicites. Ou passez à Power Query.
• Références cassées après refonte : les formules pointent sur d’anciennes cellules. Nommez vos plages (Formules > Gestionnaire de noms) pour plus de robustesse.

Checklist de livraison

✓ Structure 3 feuilles (DATA, DASHBOARD, PARAM)
✓ Tableau structuré tblVentes nommé
✓ 3+ KPI principaux avec mise en forme
✓ Mise en forme conditionnelle verte/rouge
✓ Graphique évolution 12 mois
✓ Top 5 avec formule dynamique
✓ Segments ou listes déroulantes connectés
✓ Gestion d'erreurs SIERREUR partout
✓ Protection feuille DASHBOARD
✓ Rafraîchissement automatique à l'ouverture
✓ Test 3 scénarios (nominal, vide, futur)
✓ Documentation dans une cellule cachée ou onglet README