Un tableau de bord Excel peut être un outil étonnant pour suivre les KPI, comparer des points de données, et obtenir des vues basées sur des données qui peuvent aider la direction à prendre des décisions.
Dans ce didacticiel, vous apprendrez à créer un tableau de bord Excel, les meilleures pratiques à suivre lors de sa création, les fonctionnalités et les outils que vous pouvez utiliser dans Excel , choses à éviter à tout prix, et matériel de formation recommandé.
Ce tutoriel couvre:
Quest-ce quun tableau de bord Excel et en quoi diffère-t-il dun rapport?
Comprenons dabord ce quest un tableau de bord Excel.
Un tableau de bord Excel est une page (la plupart du temps, mais pas toujours nécessaire) qui aide les gestionnaires et les chefs dentreprise à suivre les indicateurs clés de performance clés ou les mesures et prendre une décision sur cette base. Il contient des graphiques / tableaux / vues qui sont soutenus par des données.
Un tableau de bord est souvent appelé un rapport, cependant, tous les rapports ne sont pas des tableaux de bord.
Voici la différence:
- Un rapport ne collecterait et afficherait les données quà un seul endroit. Par exemple, si un responsable souhaite savoir comment les ventes ont augmenté au cours de la dernière période et quelle région a été la plus rentable, un rapport ne pourra pas y répondre. Il rapporterait simplement toutes les données de vente pertinentes. Ces rapports sont ensuite utilisés pour créer des tableaux de bord (dans Excel ou PowerPoint) qui aideront à la prise de décision.
- Un tableau de bord, en revanche, répondrait instantanément à des questions importantes telles que les régions qui fonctionnent le mieux et les produits sur laquelle la direction doit se concentrer. Ces tableaux de bord peuvent être statiques ou interactifs (où lutilisateur peut faire des sélections et modifier les vues et les données se mettre à jour dynamiquement).
Maintenant que nous avons compris ce quest un tableau de bord, plongeons-nous dans et apprenez à créer un tableau de bord dans Excel.
Comment créer un tableau de bord Excel?
La création dun tableau de bord Excel est un processus en plusieurs étapes et vous devez garder à lesprit certains éléments clés lors de sa création.
Avant même de lancer Excel, vous devez être clair sur les objectifs du tableau de bord.
Par exemple, si vous créez un tableau de bord KPI pour suivre les KPI financiers dune entreprise, votre objectif serait de montrer la comparaison de la période actuelle avec la période passée (s).
De même, si vous créez un tableau de bord pour le service des ressources humaines pour suivre la formation des employés, alors lobjectif serait de montrer combien demployés ont été formés et combien doivent être formés pour atteindre la cible.
Choses à faire avant même de commencer à créer un tableau de bord Excel
Beaucoup de gens commencent à travailler sur le tableau de bord dès quils mettent la main sur les données.
Et dans la plupart des cas, ils apportent sur eux la misère de retravailler sur le tableau de bord car les objectifs du client / des parties prenantes ne sont pas atteints.
Voici quelques-unes des Vous devez avoir répondu avant de commencer à créer un tableau de bord Excel:
Q: Quel est le but du tableau de bord?
La première chose à faire dès que vous obtenez les données ( ou même avant dobtenir les données), consiste à clarifier ce que veut votre partie prenante. Indiquez clairement à quoi doit servir le tableau de bord.
Sagit-il de suivre les KPI une seule fois ou de manière régulière? Doit-il suivre les indicateurs de performance clés pour toute lentreprise ou au niveau de la division ?. Poser les bonnes questions vous aiderait à comprendre de quelles données vous avez besoin et comment concevoir le tableau de bord.
Q: Quelles sont les sources de données?
Sachez toujours doù et dans quel format. Dans lun de mes projets, les données ont été fournies sous forme de fichiers PDF en espagnol. Cela a complètement changé la portée et la plupart de notre temps a été absorbé par le tri manuel des données. Voici les questions que vous devriez vous poser: À qui appartiennent les données? Dans quel format obtiendrez-vous les données? À quelle fréquence les données sont-elles mises à jour?
Q: Qui utilisera ce tableau de bord Excel?
Un responsable ne serait probablement intéressé que par les informations fournies par votre tableau de bord, mais certains analystes de données dans son équipe peut avoir besoin dune vue plus détaillée. En fonction de la personne qui utilise votre tableau de bord, vous devez structurer les données et le résultat final.
Q: À quelle fréquence le tableau de bord Excel doit-il être mis à jour?
Si vos tableaux de bord doivent être mis à jour chaque semaine ou chaque mois, il vaut mieux créer un modèle plug-and-play (où vous copiez-collez simplement les données et il se mettrait à jour automatiquement). Sil sagit dun exercice ponctuel, vous pouvez omettre une partie dautomatisation et le faire manuellement.
Q: Quelle version dOffice le client / partie prenante utilise-t-il?
Il vaut mieux ne pas supposer que le client / partie prenante dispose de la dernière version de MS Office. Une fois, jai créé un tableau de bord pour savoir que ma partie prenante utilisait Excel 2003.Cela a conduit à quelques retouches car la fonction IFERROR ne fonctionne pas dans la version Excel 2003 (que javais largement utilisée lors de la création du tableau de bord).
Obtenir les données dans Excel
Une fois que vous avoir une bonne idée de ce que vous devez créer, les prochaines étapes sont de mettre la main sur les données et de les obtenir dans Excel.
Votre vie est facile lorsque votre client vous donne des données dans Excel, cependant, si ce nest pas le cas, vous devez trouver un moyen efficace de lobtenir dans Excel.
Si vous êtes fourni avec des fichiers CSV ou des fichiers texte, vous pouvez facilement les convertir dans Excel. Si vous avez accès à une base de données qui stocke les données, vous pouvez créer une connexion et la mettre à jour indirectement.
Une fois que vous avez les données, vous devez les nettoyer et les standardiser.
Par exemple, vous devrez peut-être vous débarrasser des espaces de début, de fin ou des doubles espaces, rechercher et supprimer les doublons, supprimer les espaces et les erreurs, etc.
Dans certains cas, vous devrez peut-être même restructurer données (par exemple, vous devez créer un tableau croisé dynamique).
Ces étapes dépendent du projet et de lapparence de vos données dans Excel.
Présentation de la structure du tableau de bord
Une fois que vous avez les données dans Excel, vous saurez exactement ce que vous pouvez et ne pouvez pas utiliser dans votre tableau de bord Excel.
À ce stade, cest une bonne idée de revenir avec votre parties prenantes avec un aperçu du tableau de bord Excel.
Comme meilleure pratique, je crée un plan simple dans PowerPoint avec des notes supplémentaires. Le but de cette étape est de sassurer que votre partie prenante comprend à quel type de tableau de bord il / elle peut sattendre avec les données disponibles.
Elle est également utile car la partie prenante peut suggérer des changements qui lui ajouteraient plus de valeur.
Voici un exemple dexemple de plan que jai créé pour lun des tableaux de bord KPI:
Une fois le plan élaboré, il est temps de commencer à créer le tableau de bord Excel.
Comme bonne pratique, divisez votre classeur Excel en trois parties (ces sont les feuilles de calcul que je crée avec le même nom):
- Données – Cela peut être une ou plusieurs feuilles de calcul contenant les données brutes.
- Calculs – Ceci est où vous faites tous les calculs. Encore une fois, vous pouvez avoir une ou plusieurs feuilles pour les calculs.
- Tableau de bord – Il sagit de la feuille qui contient le tableau de bord. Dans la plupart des cas, il s’agit d’une vue sur une seule page qui présente des analyses / informations étayées par des données.
Tableau Excel – La sauce secrète dun tableau de bord Excel efficace
La première chose que je fais avec les données brutes est de les convertir en un tableau Excel.
Le tableau Excel offre de nombreux avantages qui sont cruciales lors de la création dun tableau de bord Excel.
Pour convertir des données tabulaires en un tableau Excel, sélectionnez les données et allez dans longlet Insertion et cliquez sur licône Tableau.
Voici les avantages de lutilisation dun tableau Excel pour votre tableau de bord:
- Lorsque vous convertissez un ensemble de données tabulaires en un tableau Excel , vous navez pas à vous soucier de la modification ultérieure des données. Même si vous obtenez des données supplémentaires, vous pouvez simplement les ajouter à la table sans vous soucier de la fausse route des formules. Ceci est vraiment utile lorsque je crée des tableaux de bord plug-and-play.
- Avec un tableau Excel, vous pouvez utiliser les noms des colonnes au lieu de la référence. Par exemple, à la place est C2: C1000, vous pouvez utiliser «Ventes».
Fonctions Excel importantes pour les tableaux de bord
Vous pouvez créer beaucoup de bons tableaux de bord Excel interactifs en en utilisant simplement des formules Excel.
Lorsque vous effectuez une sélection dans une liste déroulante, ou utilisez une barre de défilement ou cochez une case, il existe des formules qui se mettent à jour en fonction des résultats et vous donnent les données mises à jour / voir dans le tableau de bord.
Voici mes cinq principales fonctions Excel pour les tableaux de bord Excel:
- Fonction SUMPRODUCT: Cest ma fonction préférée lors de la création dun tableau de bord Excel interactif. Cela me permet de faire des calculs complexes lorsquil y a de nombreuses variables. Par exemple, supposons que jai un tableau de bord des ventes et que je veuille savoir quelles ont été les ventes effectuées par le représentant Bob au troisième trimestre dans la région de lEst. Je peux simplement créer une formule SUMPRODUCT pour cela.
- INDEX / MATCH Fonction: Je suis un grand partisan de lutilisation de la combinaison de la formule INDEX et MATCH pour rechercher des données dans les tableaux de bord Excel. Vous pouvez également utiliser la fonction RECHERCHEV, mais je trouve que INDEX / MATCH est un meilleur choix.
- Fonction IFERROR: Lorsque vous effectuez des calculs sur les données brutes, vous vous retrouvez souvent avec des erreurs. Jutilise largement IFERROR pour masquer les erreurs dans le tableau de bord (et souvent dans les données brutes également).
- Fonction TEXT: Si vous souhaitez créer des titres ou des titres dynamiques, vous devez utiliser la fonction TEXT pour
- ROWS / COLUMNS Fonction: Je les utilise souvent lorsque je dois copier une formule et que lun des arguments doit être incrémenté lorsque nous descendons / droite de la cellule.
Points à garder à lesprit lorsque vous travaillez avec des formules dans un tableau de bord Excel:
- Évitez dutiliser des formules volatiles (telles que OFFSET, NOW, TODAY, etc. sur..). Cela ralentira votre classeur.
- Supprimez les formules inutiles. Si vous avez des formules supplémentaires dans la feuille de calcul, supprimez-les lors de la finalisation du tableau de bord.
- Utilisez des colonnes daide, car cela peut vous aider à éviter les formules de tableau long.
Interactif Outils pour rendre votre tableau de bord Excel génial
Il existe de nombreux outils interactifs que vous pouvez utiliser pour rendre votre tableau de bord Excel dynamique et convivial.
En voici quelques-uns que jutilise régulièrement:
- Barres de défilement: utilisez les barres de défilement pour enregistrer la propriété de votre classeur. Par exemple, si vous avez 100 lignes de données, vous pouvez utiliser une barre de défilement avec seulement 10 lignes dans le tableau de bord. Un utilisateur peut utiliser la barre de défilement sil souhaite consulter lensemble des données.
- Cases à cocher: les cases à cocher vous permettent de faire des sélections et de mettre à jour le tableau de bord. Par exemple, supposons que je dispose dun tableau de bord de formation et que je suis le PDG de lentreprise, je voudrais regarder le tableau de bord global de lentreprise. Mais si je suis le directeur des ventes, je voudrais seulement regarder la performance de mon département. Dans ce cas, vous pouvez créer un tableau de bord avec des cases à cocher pour les différentes divisions de lentreprise.
- Liste déroulante: une liste déroulante permet à un utilisateur de faire une sélection exclusive. Vous pouvez utiliser ces sélections déroulantes pour mettre à jour le tableau de bord. Par exemple, si vous affichez des données par service, vous pouvez avoir les noms de service dans la liste déroulante.
Utilisation de graphiques Excel pour visualiser les données dans un tableau de bord Excel
Les graphiques rendent non seulement votre tableau de bord Excel attrayant visuellement, mais le rendent également facile à utiliser et à interpréter.
Voici quelques conseils lors de lutilisation de graphiques dans un tableau de bord Excel:
- Sélectionnez le bon graphique: Excel vous offre de nombreuses options de création de graphiques et vous devez utiliser le bon graphique. Par exemple, si vous devez afficher une tendance, vous devez utiliser un graphique linéaire, mais si vous souhaitez mettre en évidence les valeurs réelles, un graphique à barres / colonnes peut être le bon choix. Bien que de nombreux experts déconseillent dutiliser un graphique à secteurs, je vous suggère dutiliser votre discrétion. Si votre public a lhabitude de voir des graphiques à secteurs, vous pouvez également les utiliser.
- Utilisez des graphiques combinés: je recommande vivement dutiliser des graphiques combinés car ils permettent à lutilisateur de comparer des valeurs et den tirer des informations significatives. Par exemple, vous pouvez afficher le chiffre des ventes sous forme de histogramme et la croissance sous forme de graphique linéaire.
- Utiliser des graphiques dynamiques: si vous souhaitez autoriser lutilisateur à effectuer des sélections et que le graphique se met à jour avec lui, utilisez des graphiques dynamiques. Désormais, un graphique dynamique nest rien dautre quun graphique régulier dont les données sont mises à jour dans le back-end lorsque vous effectuez des sélections.
- Utilisez Sparklines pour rendre vos données plus significatives: si vous avez beaucoup de données dans votre tableau de bord / rapport, vous pouvez envisager dutiliser Sparklines pour le rendre visuel. Un graphique sparkline est un petit graphique qui réside dans une cellule et peut être créé à laide dun ensemble de données. Ceux-ci sont utiles lorsque vous souhaitez afficher une tendance au fil du temps et en même temps économiser de lespace sur votre tableau de bord. Pour en savoir plus sur les graphiques Sparklines Excel, cliquez ici.
- Utilisez des couleurs contrastées pour mettre en évidence les données: il sagit dun conseil de création de graphiques générique dans lequel vous devez mettre en évidence les données dans un graphique afin quil soit facile à comprendre. Par exemple, si vous disposez de données sur les ventes, vous pouvez mettre en surbrillance lannée avec la valeur de vente la plus faible en rouge.
Vous pouvez parcourir certains de mes didacticiels graphiques ici.
De plus, si vous souhaitez devenir plus avancé dans les graphiques Excel, je vous recommande de visiter le blog de Jon Peltier, expert en graphiques Excel.
Tableaux de bord Excel à faire et à ne pas faire
Commençons par les choses à ne pas faire!
Voici certaines des choses que je vous recommande déviter lors de la création dun tableau de bord Excel. Encore une fois, ceux-ci varient en fonction de votre projet et de la partie prenante, mais sont valables dans la plupart des cas.
- Nencombrez pas vos tableaux de bord: ce nest pas parce que vous avez des données et des graphiques que cela devrait disparaître dans votre tableau de bord. Noubliez pas que lobjectif du tableau de bord est daider à identifier un problème ou daider à prendre des décisions. Alors gardez-le pertinent et supprimez tout ce qui n’y appartient pas. Je me demande souvent si quelque chose est juste bon davoir absolument indispensable. Ensuite, je supprime tous les bons à avoir.
- Nutilisez pas de formules volatiles: cela ralentira les calculs.
- Ne gardez pas de données supplémentaires dans votre classeur: si vous avez besoin de ces données, créez une copie du tableau de bord et conservez-la comme sauvegarde.
Voyons maintenant quelques choses à faire (ou bonnes pratiques)
- Numérotation de vos graphiques / sections: votre tableau de bord nest pas simplement un ensemble aléatoire de graphiques et de points de données. Au lieu de cela, cest une histoire où une chose mène à une autre.Vous devez vous assurer que votre public suit les étapes dans le bon ordre. Il est donc préférable de les numéroter. Bien que vous puissiez être en mesure de les guider lorsque vous présentez en direct, cest dune grande aide lorsque quelquun utilise votre tableau de bord à un stade ultérieur ou en prend une impression.
- Restreindre le mouvement dans la zone du tableau de bord: Masquez toutes les lignes / colonnes pour vous assurer que lutilisateur ne défile pas accidentellement.
- Figer les lignes / colonnes importantes: utilisez les volets figés lorsque vous voulez que certaines lignes / colonnes soient toujours visibles sur le tableau de bord.
- Faire coller les formes / graphiques: assurez-vous que vos formes / graphiques ou contrôles interactifs ne se masquent pas ou ne se redimensionnent pas lorsque quelquun masque / redimensionne les cellules. Vous pouvez également utiliser loutil Appareil photo Excel pour prendre un instantané des graphiques / tableaux et le placer dans le tableau de bord Excel (ces images sont dynamiques et se mettent à jour lorsque le graphique / tableau principal est mis à jour).
- Fournissez un Guide de lutilisateur: si vous disposez dun tableau de bord complexe, il est judicieux de créer une feuille de calcul distincte et de mettre en évidence les étapes. Cela aidera les gens à utiliser votre tableau de bord même lorsque vous nêtes pas là.
- Économisez de lespace avec des graphiques combinés: utilisez des graphiques combinés (tels que des graphiques à puces, des graphiques thermométriques et des graphiques réels et cibles) pour enregistrer votre feuille de calcul espace.
- Utilisez des symboles & Mise en forme conditionnelle: utilisez des symboles (tels que des flèches haut / bas ou une coche / croix) et une mise en forme conditionnelle pour ajouter un couche danalyse à votre tableau de bord (mais nen faites pas trop non plus).
Vous voulez créer des tableaux de bord professionnels dans Excel? Consultez le cours de tableau de bord Excel en ligne où je vous montre tout sur la création dun tableau de bord Excel de classe mondiale.
Exemples de tableau de bord
Voici quelques exemples de tableau de bord Excel que vous pouvez télécharger et lire avec.
Tableau de bord des KPI Excel
Vous pouvez utiliser ce tableau de bord pour suivre les KPI de diverses entreprises et puis utilisez des graphiques à puces pour approfondir les performances de chaque entreprise.
Cliquez ici pour télécharger ce tableau de bord KPI.
Si vous souhaitez apprendre à créer ce tableau de bord KPI, cliquez sur ici.
Tableau de bord des performances du centre dappels
Vous pouvez utiliser ce tableau de bord pour suivre les KPI clés dun centre dappel. À partir de ce tableau de bord, vous pouvez apprendre à créer des graphiques combinés, à mettre en évidence des points de données spécifiques dans les graphiques, à trier à laide de boutons radio, etc.
Cliquez ici pour télécharger le tableau de bord.
Vous pouvez en savoir plus sur ce tableau de bord ici.
Saison EPL visualisée dans un tableau de bord Excel
Dans ce tableau de bord, vous apprendrez à utiliser VBA dans les tableaux de bord Excel. Dans ce tableau de bord, les détails des jeux sont mis à jour lorsque vous double-cliquez sur les cellules à gauche. Il utilise également VBA pour afficher un menu daide pour guider lutilisateur dans lutilisation de ce tableau de bord.
Cliquez ici pour télécharger le tableau de bord EPL.
Vous pouvez en savoir plus sur ce tableau de bord ici.
Formation recommandée sur les tableaux de bord
Si vous souhaitez apprendre à créer des tableaux de bord professionnels de classe mondiale dans Excel, consultez mon cours GRATUIT en ligne sur les tableaux de bord Excel.