top of page

Comment créer des tableaux de bord Excel impressionnants

Dernière mise à jour : 11 août 2023


OBTENEZ UN MODÈLE EXCEL DE TABLEAU DE BORD GRATUIT À LA FIN DE CET ARTICLE !

Les tableaux de bord sont des outils de création de rapports destinés à aider les responsables à prendre des décisions business. Ils donnent un aperçu d'une situation ou d'une activité pour comprendre les résultats clés, les tendances et les points d'alerte.


Cet article vous fournira des principes de conception précieux et de nombreux conseils et astuces pour créer des tableaux impressionnants et faciles à mettre à jour.



TABLE DES MATIÈRES



Présentation


À quoi ressemble un bon tableau de bord ?


👉 C'est magnifique ! La plupart des gestionnaires ne jetteront qu'un coup d'œil à votre tableau de bord et décideront en 10 secondes ou moins s'ils veulent le vérifier. Vous devez le rendre attrayant pour qu'ils veuillent l'explorer, et ils doivent être capables de comprendre les données presque instantanément.


👉C'est dynamique ! Nous regardons tous les choses sous un angle différent. Un tableau de bord dynamique doit permettre aux responsables de jouer facilement avec les données et de les approfondir pour analyser un KPI spécifique par pays, produit, segment, etc.


👉La mise à jour prend 1 min ! Ne passez pas des heures ou des jours à mettre à jour votre tableau de bord. Rendez vos tableaux de bord super faciles à mettre à jour. De cette façon :

  • Le tableau de bord contiendra de nouvelles données et restera pertinent

  • Vos collègues peuvent mettre à jour le tableau de bord pour vous lorsque vous êtes en vacances

  • Vous pouvez gagner du temps et quitter le bureau plus tôt


Comment créer des tableaux de bord étonnants, flexibles et faciles à mettre à jour sur Excel


Avant de commencer : définissez vos KPI et vos sources de données

De toute évidence, la première chose que vous devez savoir avant de créer un tableau de bord est de savoir pourquoi.


Vous devez vous poser les questions clés : que voulez-vous montrer ? qui seront les destinataires ? quels sont vos KPI ? d'où viendront les données ?


Discutez des attentes avec chaque partie prenante pour vous mettre d'accord sur le contenu du tableau de bord avant de le créer. De plus, vous devrez rechercher où trouver les données correspondantes.


Cette étape semble évidente mais est souvent négligée, entraînant des pertes de temps considérables par la suite.

La structure : concevez votre tableau de bord autour de 4 types de feuilles

C'est peut-être la chose la plus critique. Un bon tableau de bord est d'abord une question de structure.


Il existe 4 types de feuilles différents dont vous aurez besoin :

  1. Les feuilles "Sources de données" contiennent les données brutes et les colonnes calculées

  2. Les feuilles "Référentiel" vous donnent les règles de conversion entre les données brutes et les colonnes calculées (ex. table de conversion pour changer EUR en USD, table de conversion pour changer les codes postaux en noms de ville, etc.)

  3. Les feuilles de tableaux croisés dynamiques agrègent votre base de données pour les préparer à la visualisation

  4. La feuille de tableau de bord est la seule feuille nécessaire à la visualisation et c'est là que vous aurez vos graphiques (c'est aussi là que les gens s'exclameront sur la qualité de votre tableau de bord).

Ces 4 types de feuilles ont des rôles bien distincts. Vous pourrez parfois avoir plusieurs feuilles de chaque type, mais chaque feuille appartiendra à l'une de ces quatre catégories.


Vous pouvez utiliser un jeu de couleurs pour les différencier rapidement, en particulier si vous avez plusieurs feuilles de source de données, ou plusieurs feuilles de tableaux croisés dynamiques.


Tableau de bord Excel - Structure des feuilles
Structurez votre fichier Excel en 4 feuilles

Voyons maintenant comment créer chacune de ces feuilles de calcul pour aboutir à un excellent tableau de bord Excel.

 

La feuille source de données

Les feuilles de sources de données hébergent les données à partir desquelles le tableau de bord est créé. Habituellement, les données proviennent d'extractions Excel bruts de vos CRM d'entreprise ou d'autres outils commerciaux. Vous pouvez bien sûr avoir plusieurs fiches sources de données si votre tableau de bord nécessite plusieurs extractions.

Les sources de données doivent être structurées comme une base de données


C'est LA règle la plus importante de tout cet article (par ailleurs très long) !

Les sources de données doivent être structurées comme des bases de données, ce qui signifie :

  • Chaque ligne représente une entrée unique (par exemple une transaction de vente)

  • Chaque colonne contient un champ unique qui sont des attributs de ces entrées.


Pour donner quelques exemples :

✅Le montant de la transaction, l'âge du client ou le sexe du client sont tous des champs distincts.

Mais le montant des ventes en mai et le montant des ventes en juin ne sont PAS des champs distincts ! Le "Montant des ventes" doit être dans une colonne et le "Mois" dans une autre colonne.


Lorsque les données sont extraites d'un système d'entreprise, elles sont généralement déjà structurées en tant que base de données.


💡Si les données ne sont pas structurées correctement, vous pouvez regarder la fonctionnalité Dépivoter pour aplatir les tableaux.


Tableau de bord Excel - Source de données
Voici à quoi vos sources de données devraient ressembler

Mettez votre base de données dans un Tableau


Sélectionnez votre base de données, allez dans l'onglet Insérer" et cliquez sur "Tableau". Renommez votre tableau en "MyData" par exemple, depuis l'onglet "Design".


Pourquoi utiliser un tableau ? Il existe de nombreuses raisons mais en voici 2 très importantes :


✅Lorsque vous créez des Tableaux Croisés Dynamiques (TCD) sur cette base de données, leur source de données s'ajustera automatiquement au nombre de lignes ou de colonnes que vous ajouterez à l'avenir. Cela vous évite de devoir mettre à jour manuellement la source de données de chaque TCD pour chaque nouvealle colonne ou ligne que vous ajouterez plus tard.


✅Les tableaux étendent automatiquement vos formules jusqu'à la dernière ligne. Ainsi, lorsque vous souhaitez mettre à jour le tableau de bord, vous n'aurez pas à faire cette éta^pe manuellement. Cela vous fera gagner du temps et réduira considérablement le risque d'erreurs.


💡Vous voulez en savoir plus sur les tableaux ? Voir 12 raisons d'utiliser des tableaux Excel.

Une alternative, seulement si vous détestez vraiment utiliser des tableaux, consiste à utiliser une plage nommée à la place.


Pour cela, allez dans l'onglet "Formule" et cliquez sur "Définir le nom". Dans le champ "Nom", tapez "myData" et dans le champ "Fait référence à", vous pouvez utiliser une formule comme celle-ci :


=DECALER('Data Source'!A1,,,NBVAL('Data Source'!A:A),NBVAL('Data Source'!1:1)) 

Cela crée une plage nommée "MyData" que vous pouvez référencer dans vos Tableaux Croisés Dynamiques (TCD), et de cette façon vous n'aurez pas à mettre à jour la source de données de vos TCD lorsque vos lignes ou colonnes sont ajoutées.


Tableau de bord Excel - Plage nommée avec taille variable
Création d'une plage nommée de taille variable

Autres bonnes pratiques importantes pour une feuille de source de données


✅Les sources de données doivent contenir à la fois les données brutes et les colonnes calculées, avec un jeu de couleurs pour les différencier.


✅Ne touchez pas aux données brutes (au-delà de la mise en forme). C'est à cela que serviront les colonnes calculées. Si vous apportez des modifications, vous devrez les répéter à chaque mise à jour, et nous ne voulons pas cela.


✅Les sources de données doivent avoir l'identifiant unique dans la première colonne. Cela facilitera l'utilisation des formules de recherche dans vos colonnes calculées.


✅Les colonnes calculées doivent être toutes à l'extrême droite. Cela facilitera les futures mises à jour des données brutes.



Prenons un exemple.


Dans notre extraction, il y a un champ "Sexe" qui contient 0 pour les hommes et 1 pour les femmes. Nous voulons changer cela en un texte lisible "Homme" et "Femme" car c'est ainsi que ce sera affiché dans le tableau de bord.


Pour ce faire, nous ajouterons une nouvelle colonne calculée tout à droite du tableau, qui aura pour rôle de convertir la valeur numérique en texte lisible.

❌N'insérez pas les colonnes calculées à côté de la colonne "Sexe" dans nos données brutes. C'est peut-être tentant, mais ce n'est VRAIMENT PAS une bonne idée. Cela rendrait très long, pénible et risqué la mise à jour de vos données brutes avec de nouvelles extractions, devant coller les colonnes une par une au lieu de toutes à la fois.


 

La feuille Référentiel

Cet onglet hébergera le référentiel de données.

Créez des Tableaux pour vos référentiels


Comme pour votre source de données, une fois que vous avez ajouté un référentiel dans votre feuille "Référentiel", sélectionnez-le et allez dans "Insérer" puis "Tableau". De cette façon, les formules de recherche dans les colonnes calculées de votre feuille "Source de données" s'ajusteront automatiquement si vous ajoutez de nouveaux éléments au référentiel.


Tableau de bord Excel - Tableaux de référence
Exemples de tables de référence

Utilisez les fonctions de recherche vers vos tables de référence pour convertir vos données brutes en colonnes calculées


Cela peut sembler évident, mais il est beaucoup plus simple de simplement modifier ou d'ajouter une valeur dans une table de référence que de mettre à jour plusieurs formules dans vos colonnes calculées. Donc, si vos données brutes sont en EUR et que vous avez besoin de plusieurs colonnes calculées pour les convertir en USD... vous référer à un tableau de référence pour les devises vous permettra de simplement mettre à jour le taux de change plutôt que de changer toutes vos formules.


💡Nous vous recommandons d'utiliser le Fonction Excel RECHERCHEX au lieu de l'ancienne RECHERCHEV ou d'INDEX/EQUIV, car cette nouvelle fonction fonctionne mieux par de nombreux aspects.



 

La feuille Tableaux Croisés Dynamiques (ou TCD)


Créez des Tableaux Croisés Dynamiques à partir du Tableau contenant votre source de données


Lors de la création de Tableaux Croisés Dynamiques (TCD), assurez-vous d'utiliser le nom du Tableau ("MyData" dans ce cas) comme source de données, en non pas l'adresse elle-même (pas quelque chose qui ressemble à "Data Source!A1:G5000").


Si vous sélectionnez l'ensemble du Tableau et insérez un Tableau Croisé Dynamique, le nom du Tableau devrait déjà être mis par défaut comme la source, c'est donc facile .

Et si vous avez opté pour une plage nommée au lieu d'un Tableau, tapez F3 pour afficher les noms dans la feuille de calcul et l'insérer dans la boîte de dialogue de référence.


De cette façon, vous n'aurez pas à modifier la source de données si vous ajoutez de nouvelles colonnes à l'avenir.

Tableau de bord Excel - Insérer un tableau croisé dynamique
Utilisation d'un nom de table ou de plage comme source de données de Tableau Croisé Dynamique

Préparez des Tableaux Croisés Dynamiques pour chaque graphique que vous aurez dans le tableau de bord


Les Tableaux Croisés Dynamiques sont le moteur sous le capot : les personnes qui voient votre tableau de bord ne les regarderont pas, mais ils sont l'élément essentiel qui automatisera l'ensemble du tableau de bord.

Il y a plusieurs raisons pour lesquelles vous devez utiliser les Tableaux Croisés Dynamiques. L'une de ces raisons est que tous les Tableaux Croisés Dynamiques du classeur peuvent être mis à jour en 1 seul clic. C'est ainsi que nous pourrons actualiser le tableau de bord instantanément.


Assurez-vous de conserver quelques lignes vides entre chaque Tableaux Croisés Dynamiques


Vos Tableaux Croisés Dynamiques pourront prendre plus d'espace lorsque vous les mettrez à jour ultérieurement et qu'il y aura davantage de dates ou de produits à afficher. Ainsi, pour éviter de recevoir des messages d'erreur lorsqu'un Tableau Croisé Dynamique ne disposera pas de l'espace dont il a besoin pour se mettre à jour, vous aurez besoin de quelques lignes et colonnes de marge.


De plus, nous ajouterons des Segments (comme expliqué plus loin) et les Segments nécessiteront des lignes supplémentaires pour chaque Tableau Croisé Dynamique, tout comme les filtres.

 

La feuille Tableau de bord

C'est enfin l'heure de la partie amusante ! Créons le magnifique tableau de bord lui-même, qui sera la seule feuille que les gens verront.

Utiliser uniquement les Graphiques Croisés Dynamiques


Tout l'avantage d'avoir créé des Tableaux Croisés Dynamiques dans la section précédente est que vous pouvez désormais créer des Graphiques Croisés Dynamiques.


Allez dans votre feuille de calcul "TCD", cliquez sur un Tableau Croisé Dynamique puis allez dans l'onglet "Analyser" et cliquez sur "Graphique Croisé Dynamique".


Les Graphiques Croisés Dynamiques ont exactement le même comportement que le Tableau Croisé Dynamique auquel ils sont associés. Ils seront notamment mis à jour automatiquement. Ainsi, lorsque vous mettrez à jour votre tableau de bord le mois prochain, vous n'aurez pas à étendre la source de données de chaque graphique pour prendre en compte le nouveau mois, cela se fera automatiquement et vous n'aurez pas à ajuster les plages de graphique manuellement.


Tableau de bord Excel - Graphique croisé dynamique

Formatez votre graphique comme vous le feriez avec n'importe quel graphique


Par défaut, les Graphiques Croisés Dynamiques sont créés avec des boutons. Faites un clic droit dessus et cliquez sur "Masquer tous les boutons de champ sur le graphique".

Tableau de bord Excel - Masquer tous les boutons de champ sur le graphique
Masquer tous les boutons de champ sur le graphique dans Excel

Mettez ensuite votre Graphique Croisé Dynamique en forme comme vous le souhaitez, comme vous le feriez avec n'importe quel autre graphique.


Quelques conseils en termes de mise en forme des graphiques :


Choisissez le bon type de graphique : vous n'avez pas besoin d'utiliser tout le temps des graphiques en colonne, en ligne ou des camemberts ! L'utilisation de différents types de graphiques vous permettra de mieux mettre en évidence les informations clés. Cela rendra également chaque KPI plus facile à retenir et à se démarquer de la masse de données !


💡Déverrouillez des dizaines de types de graphiques avancés avec la bibliothèque de graphiques




Moins c'est plus : supprimez les axes, les légendes, le quadrillage, les décimales d'étiquettes, etc. lorsqu'ils n'apportent rien. Moins vous aurez d'encombrement, plus il sera facile de lire les informations importantes.


Utilisez des couleurs cohérentes et significatives : utilisez les couleurs de votre entreprise au lieu des couleurs par défaut. Essayez de vous en tenir à quelques couleurs et de les appliquer de manière signifiante pour créer un contraste là où c'est utile.


Enregistrer le graphique en tant que modèles pour éviter de refaire la mise en forme manuellement à chaque fois


Ne réinventez pas la roue à chaque fois : enregistrez vos graphiques en tant que modèles afin de pouvoir réutiliser le même format ultérieurement.

✅Cela vous fera gagner beaucoup de temps

✅Cela vous aidera à maintenir la cohérence et à améliorer l'effet visuel global de votre tableau de bord


👉Pour enregistrer un graphique en tant que modèle, faites un clic droit dessus et choisissez "Enregistrer en tant que modèle".


Tableau de bord Excel - Enregistrer le graphique en tant que modèle
Enregistrer un graphique en tant que modèle pour réutiliser le format ultérieurement

Vous devrez créer un nouveau modèle pour chaque type de graphique (par exemple, 1 pour les graphiques en ligne, 1 pour les histogrammes empilés, etc.).


Ensuite, lorsque vous ajoutez un graphique, vous pouvez simplement appliquer le modèle au lieu de refaire tout le format manuellement.


👉Pour réutiliser le modèle, allez dans l'onglet Design, cliquez sur "Changer le type de graphique" puis "Modèles" sur la gauche.


Tableau de bord Excel - Réutiliser le modèle de graphique
Appliquer un modèle de format à un graphique existant

Créez le tableau de bord !


Vous pouvez désormais créer autant de graphiques que vous le souhaitez, les mettre en forme à l'aide des modèles enregistrés et les assembler pour créer votre tableau de bord étape par étape.


Rendez-le visuellement attrayant et explicite !


💡Power-user a 2 fonctionnalités utiles pour vous à cette étape :


Utilisez Segments et Chronologies pour simplifier votre tableau de bord


Les Segments sont un outil tout simplement incroyable. Les Segments fonctionnent comme des filtres, mais en mieux.


Un excellent tableau de bord est un tableau de bord qui peut être lu et analysé par différentes personnes dans l'organisation, pour éviter de multiplier partout des tableaux de bord avec des données partielles.


Demandez-vous comment les données pourraient être analysées en sous-segments :

  • Les directeurs voudront examiner les données agrégées pour voir les tendances clés globales

  • Chaque chef de produit voudra probablement voir les ventes de son propre produit

  • Chaque responsable de zone voudra consulter les données pour sa propre zone géographique

  • Etc.

La plupart des gens ne connaissent même pas l'existence des Segments dans Excel. Donc, ce qu'ils font, c'est qu'ils répliquent tous les graphiques pour chaque produit, puis répliquent à nouveau tous les graphiques pour chaque zone géographique, etc.


Mais si vous avez 10 produits et 10 zones, cela représente 100 variantes de chaque graphique !


Avec les Segments, vous pouvez n'avoir qu'un seul graphique et permettre aux utilisateurs de les filtrer en fonction de ce qu'ils veulent regarder et de la profondeur d'analyse qu'ils souhaitent. De cette façon, toutes les informations sont disponibles pour tout le monde, dans n'importe quelle combinaison, tout en gardant les choses simples.

👉Pour créer des Segments, sélectionnez un graphique, accédez à l'onglet "Analyser", puis cliquez sur "Segment". Choisissez le champ à filtrer, par ex. Produit ou Zone Géographique.


Maintenant, les gens vous cliquez sur une valeur sur le Segment, par exemple l'Europe. le graphique s'ajustera instantanément pour n'afficher que les valeurs pour l'Europe.


Les segments Excel rendent les graphiques croisés dynamiques


👉Liez vos Segments à plusieurs graphiques. De cette façon, cliquer sur un seul Segment ajustera l'ensemble du tableau de bord.

Pour ce faire, faites un clic droit sur un Segment et cliquez sur "Gérer les connexions". Vous verrez tous les graphiques auxquels vous pouvez le lier.

Tableau de bord Excel - Trancheurs

👉Les Chronologies sont exactement comme les Segments, sauf qu'elles sont mieux adaptées aux champs de Dates. Avec une Chronologie, vous pouvez filtrer les données par jour, mois, trimestre, année ou définir des dates de début et de fin spécifiques. Tout votre tableau de bord s'ajustera alors pour n'afficher que les données de cette période.

Excel Timelines filtre les dates
Utilisez les Chronologies pour permettre de filtrer facilement les dates

En résumé :

✅Les Segments et les Chronologies rendent votre tableau de bord dynamique

✅Les Segments vous permettent d'approfondir vos données et de les examiner sous différents angles pour vraiment analyser et comprendre les tendances clés

✅Les Segments simplifient votre tableau de bord, en minimisant le nombre de graphiques nécessaire

✅Les Segments permettent à différents profils (Directeurs, Chef de produit, Responsable de Zone etc.) d'utiliser le même tableau de bord et de regarder les mêmes chiffres, chacun avec son propre œil



Si vous êtes familier avec VBA, créez des boutons activant les vues personnalisées


Pour les personnes familiarisées avec VBA, vous pouvez donner un coup de pouce supplémentaire à votre tableau de bord en créant des vues personnalisées.


Ceci est intéressant si vous avez de nombreux graphiques et que vous souhaitez éviter aux utilisateurs d'avoir à faire défiler vers le haut/bas et à gauche/droite à la recherche de graphiques spécifiques.

👉Organisez d'abord manuellement vos graphiques dans différentes vues. Une vue sera le contenu d'1 écran et vous souhaiterez y afficher un ensemble cohérent de graphiques.


👉Ensuite, pour chaque vue, créez une macro avec une simple ligne de code :

Application.Goto Range("B1"), True

où B1 dans cet exemple est la cellule que vous voulez dans le coin supérieur gauche de votre vue (au lieu de B1, il est en fait préférable de nommer la cellule et de faire référence à ce nom).


👉Maintenant, vous pouvez attribuer cette macro à une forme (et pourquoi pas même une icône ?). Lorsque l'utilisateur clique sur cette forme, il accède à la vue personnalisée.

Vous pouvez l'utiliser pour créer une sorte de "menu" sur la gauche, avec des formes liées à différentes vues. Figez les volets pour que le menu soit toujours visible lorsque les utilisateurs accèdent à différentes vues.


 

Mise à jour du tableau de bord

Voyons maintenant combien de temps il faut pour mettre à jour le tableau de bord. Ce qu'il vous reste à faire :

✅Coller le nouvel extrait à la place de l'ancien dans la source de données

✅Sélectionnez un graphique, faites un clic droit et appuyez sur Actualiser. Tous les Tableaux Croisés Dynamiques et Graphiques Croisés Dynamiques seront mis à jour.


Et voilà. L'ensemble de votre tableau de bord a été mis à jour en moins d'une minute.





Présenter les données de votre tableau de bord avec PowerPoint

Préparez vos graphiques afin qu'ils puissent être facilement exportés vers PowerPoint


Très souvent, votre tableau de bord alimentera un reporting PowerPoint à partager avec la direction, envoyer aux clients, etc.


Vous avez probablement déjà constaté que copier/coller des graphiques dans PowerPoint vous oblige parfois à perdre beaucoup de temps à les redimensionner encore et encore.

Dans PowerPoint, insérez un rectangle aussi grand que votre diapositive. Collez-le dans Excel. Vous pouvez considérer cette forme comme un aperçu de la diapositive PowerPoint. En plaçant des graphiques à l'intérieur de ce cadre, vous verrez leur taille sur la diapositive lorsque vous les y collerez.


💡Vous pouvez trouver des explications plus détaillées sur cette astuce dans ce tutoriel dédié.

Tableau de bord Excel - Évitez la distorsion de vos graphiques lorsque vous les collez dans PowerPoint
Assurez-vous de créer des graphiques ayant déjà une taille et police adaptée à PowerPoint


Liez PowerPoint et Excel pour mettre à jour automatiquement la présentation


Si vous avez une présentation PowerPoint que vous devez mettre à jour chaque fois que vous actualisez votre tableau de bord, vous devez absolument envisager de coller vos graphiques d'Excel vers PowerPoint avec des liens.


Copiez un graphique dans Excel, allez dans PowerPoint, cliquez sur "Coller", "Collage spécial" et sélectionnez "Lien" à gauche de la fenêtre.


Tableau de bord Excel - Coller des graphiques dans PowerPoint avec des liens
Collez des graphiques sous forme de liens pour mettre à jour votre présentation automatiquement

Il est désormais possible d'actualiser les graphiques à partir de PowerPoint d'un simple clic droit.

Cependant, les liens Microsoft peuvent être rompus dans un certain nombre de cas, généralement lorsque les fichiers sont renommés, déplacés vers un autre dossier ou simplement si un autre utilisateur travaille dessus.


💡Consultez cet article détaillé sur les différents types de liens entre Excel et PowerPoint, mettant en évidence les forces et les faiblesses de chacun.


💡Le lien Power-user qui a été conçu pour la robustesse et résiste à tous les scénarios ci-dessus.


Power-user l Lien Excel-PowerPoint

 


Modèle GRATUIT - Cliquez pour télécharger ce tableau de bord

Vous voulez être une ROCK STAR d'Excel et de PowerPoint ? Téléchargez le Complément Power-user : il contient des modèles, des diagrammes, des graphiques, des cartes, des icônes et de nombreux outils qui feront de vous un super héros avec PowerPoint et Excel !


185 vues

Kommentare


bottom of page