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
La structure : concevez votre tableau de bord autour de 4 types de feuilles
Présenter les données de votre tableau de bord avec PowerPoint
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
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 :
Les feuilles "Sources de données" contiennent les données brutes et les colonnes calculées
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.)
Les feuilles de tableaux croisés dynamiques agrègent votre base de données pour les préparer à la visualisation
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.
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.
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.
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.
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.
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.
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".
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 !
💡Consultez cette infographie sur comment choisir le meilleur type de graphique pour vos 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".
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.
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 :
Copier/coller le graphique dimensions dans Excel est idéal pour vous assurer que vos graphiques ont la même taille et que votre tableau de bord n'a pas l'air désordonné.
Permuter la position des graphiques vous permet d'organiser facilement vos graphiques sur la feuille
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.
👉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.
👉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.
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"), Trueoù 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é.
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.
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.
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 !