Est-il possible d’avoir des étiquettes de données colorées comme le rouge pour les négatifs et le bleu ou le vert pour les valeurs positives dans le graphique Excel? Cette question a été posée par certains lorsque j’ai publié une analyse de la variance dans Excel – Faire un meilleur budget par rapport aux graphiques réels, car cela montre l’utilisation d’étiquettes de données personnalisées qui incluent des flèches ascendantes et descendantes avec des valeurs positives et négatives directement sur le graphique pour une compréhension facile. C’est ce que nous recherchons dans ce tutoriel:
Mais avoir les valeurs positives et négatives dans les étiquettes de données également colorées, c’était assez intéressant. À ce moment-là, j’étais vraiment sans réponse car j’ai essayé peu de choses mais je ne l’ai pas vraiment fait. J’ai donc suggéré la façon à l’ancienne d’avoir chaque étiquette de données « de couleur dure » (ce que je vais expliquer dans cet article) mais ce n’était pas la solution.
Mais je suis revenu dessus et j’ai essayé un nouvel échantillon de données et là, je ne l’ai pas fait fonctionner comme cela aurait dû fonctionner auparavant, mais j’ai également trouvé pourquoi cela ne fonctionnait pas la première fois. Je vais donc partager toute l’expérience que j’ai vécue avec la mauvaise approche paresseuse et la bonne approche géniale!
Changement de couleur des étiquettes de données dans les graphiques Excel – Comment
1 Solution de contournement avant 2013
Avant Office 2013, je ne connais aucun moyen facile d’insérer des symboles dans les étiquettes de données ou de les colorer conditionnellement pour afficher des valeurs négatives et positives de différentes couleurs. Et au-dessus, obtenir les symboles et les couleurs était deux tâches distinctes.
La solution de contournement pour les symboles, bien que longue, est assez bonne car avec cette approche, les étiquettes de données sont également mises à jour si les données sous-jacentes sont mises à jour mais pas complètement dynamiques car elles n’intègrent pas si des données supplémentaires sont ajoutées, ce qui les rend laborieuses. Dans le monde d’Excel, il est connu sous le nom d’étiquettes de données personnalisées et j’ai discuté de cette approche dans certains de mes tutoriels de cartographie, y compris le graphique d’analyse de variance.
La solution de contournement pour les étiquettes de données colorées était cependant un peu boiteuse. Peut-être y a-t-il un moyen via VB wizardry mais je ne le connais toujours pas. La raison pour laquelle il est boiteux est qu’il n’est pas dynamique et ne change pas avec le changement de données. Simple.
Mais comme la majorité utilise toujours Excel 2003, 2007 et 2010, ces approches peuvent toujours aider.
1.1 Étiquettes de données personnalisées avec des symboles
L’idée de base derrière l’étiquette personnalisée est de connecter chaque étiquette de données à une cellule de la feuille de calcul Excel et tout ce qui se trouve dans cette cellule apparaîtra sur le graphique en tant qu’étiquette de données. Ainsi, une fois qu’une étiquette de données est connectée à une cellule, nous appliquons une mise en forme de numéro personnalisée sur la cellule et les résultats apparaîtront également sur le graphique. Les étapes suivantes vous aident à comprendre ce qui est requis :
Étape 1: Configurez le graphique et activez les étiquettes de données sur votre graphique. J’ai les données dans les colonnes A et B avec respectivement les années et les montants. J’ai une troisième colonne avec une étiquette comme en-tête et j’obtiens les mêmes valeurs que dans la colonne Montant. Vous pouvez également utiliser la colonne Montant, mais pour faire, mais pour comprendre, je vais avec une colonne supplémentaire.
Étape 2: Faites un clic gauche sur n’importe quelle étiquette de données et elle les sélectionnera toutes ou au moins toutes les étiquettes de données de cette série. Cliquez à nouveau avec le bouton gauche de la souris et cette fois, seule l’étiquette de données sur laquelle vous avez cliqué sera sélectionnée.
Étape 3: Cliquez à l’intérieur de la barre de formule, appuyez sur le bouton « = » du clavier, puis cliquez sur la cellule que vous souhaitez lier ou tapez l’adresse de cette cellule. Dans mon cas, c’est la cellule C2.Appuyez sur la touche Entrée. Maintenant, la cellule est connectée à cette étiquette de données. Répétez ce processus jusqu’à ce que toutes les cellules soient connectées à chaque étiquette de données.
Étape 4: Sélectionnez les données dans la colonne C et appuyez sur Ctrl + 1 pour appeler la boîte de dialogue formater la cellule. À partir du bouton gauche, cliquez sur personnalisé et placez votre curseur dans le champ type et procédez comme suit:
- Appuyez et maintenez la touche ALT du clavier et sur le pavé numérique appuyez sur les touches 3 et 0. Lâchez la touche ALT et vous verrez que la flèche vers le haut est insérée. Appuyez sur la touche 0. Puis deux points; touche. Ceci complète l’argument de valeur positive.
- Maintenez enfoncée la touche ALT du clavier et appuyez sur les touches 3 et 1 du pavé numérique. Relâchez la touche ALT et vous verrez que la flèche vers le bas est insérée. Appuyez sur la touche 0, puis sur la touche;. Ceci termine l’argument de la valeur négative.
- Cliquez sur le bouton OK pour fermer la boîte de format de cellule.
Le graphique affichera instantanément les flèches vers le haut et vers le bas.
1.1.1 Problèmes
Le problème comme je l’ai dit au début est que bien que les étiquettes de données que j’ai connectées se mettent à jour si les données changent, mais si je lance des lignes supplémentaires au graphique, les nouvelles étiquettes de données doivent également être connectées et cela le rend assez lourd.
Dans mon cas, j’utilise une colonne d’aide pour les étiquettes de données. Je dois donc non seulement mettre à jour ma colonne d’assistance pour inclure plus de lignes, mais aussi connecter chaque étiquette de données supplémentaire aux cellules nouvellement ajoutées.
Jetez un coup d’œil aux 2 dernières étiquettes de données une fois les nouvelles données ajoutées et le graphique mis à jour:
1.2 Étiquettes de données colorées
Maintenant, nous arrivons à la deuxième partie, c’est-à-dire à colorier les étiquettes de données. Ce que nous recherchons, c’est de colorer les négatifs en rouge essentiellement et les positifs dans n’importe quelle couleur comme le bleu ou le vert ou simplement de les laisser en noir. Alors, quel est le chemin? Pour être honnête, il n’y a pas de bonne façon de le faire. Seule solution de contournement à ma connaissance. Je répète mes connaissances car il pourrait y avoir quelque chose de mieux que je ne sais pas encore. Si quelqu’un le sait, veuillez avancer et partager car cela aidera des milliers, sinon des millions!
La façon dont je sais est de simplement cliquer une fois sur l’étiquette de données et de cliquer à nouveau dessus pour sélectionner l’étiquette de données particulière que vous pouvez ensuite formater avec la couleur souhaitée. Et bien sûr, vous devrez le faire pour chaque étiquette de données séparément.
Fatigant non? Et au-dessus de cela, il est « difficile » de colorer les étiquettes. Donc, si les données changent et qu’au lieu de positifs, vous avez des négatifs, la couleur ne changera pas pour vous et ce sera certainement un gâchis!
2 Post 2013 – La génialité commence!
Et si je vous disais que dans Excel 2013, vous pouvez obtenir toutes les solutions d’avoir des symboles et même d’obtenir des couleurs en UNE seule fois et cela sans aucun piège de ne pas être dynamique? Absolument possible! Suivez:
Étape 1: Appuyez sur Ctrl + 1 sur n’importe quelle cellule sélectionnée en dehors de la plage. Cliquez sur personnalisé sur le côté gauche et placez votre curseur dans le champ type et procédez comme suit:
- Tapez, puis maintenez la touche ALT enfoncée et appuyez sur les touches 3 et 0 du pavé numérique. Relâchez la touche ALT et pointez 0, puis deux points.
- Tapez, puis maintenez la touche ALT enfoncée et appuyez sur les touches 3 et 1 du pavé numérique. Relâchez la touche ALT et pointez 0
- Sélectionnez le code entier et appuyez sur copier! Nous allons coller ce code dans un champ spécifique dans un peu. Essayez donc de ne rien copier après cette étape. Si vous le faites. Vous pouvez toujours revenir ici pour le copier à nouveau.
- Cliquez sur le bouton OK pour fermer la boîte de dialogue ET également pour enregistrer le code que vous venez de saisir.
Étape 2: Tableau de configuration sur la base de deux colonnes A et B. Plus besoin de troisième colonne.
Étape 3: Activez les étiquettes de données si elles ne le sont pas déjà en accédant à l’option Éléments de graphique dans l’onglet conception sous outils de graphique.
Étape 4: Cliquez sur les étiquettes de données et il sélectionnera toute la série. Ne cliquez plus car nous devons appliquer des paramètres à toute la série et pas à une seule étiquette de données.
Étape 4: Allez dans Options d’étiquette > Numéro. Dans la liste déroulante catégorie, sélectionnez Personnalisé.
Étape 5: Placez votre curseur dans le champ code de format et appuyez sur Ctrl + V et il collera le code que nous avons copié à l’étape 1.
BANG!!!! BANG!!!! Ouais cogné deux fois car nous avons non seulement les symboles mais aussi les couleurs en une seule fois!
Mais la magie n’est pas encore terminée ! Maintenant, si vous ajoutez des données supplémentaires et mettez à jour le graphique, les étiquettes de données se mettront à jour automatiquement et vous n’avez donc pas à vous soucier de la recoloration ou de la connexion des cellules, etc. alors BANG à nouveau pour la troisième fois 🙂
Jetez un coup d’œil aux deux derniers éléments de données supplémentaires ajoutés au graphique et les étiquettes de données sont mises à jour en conséquence:
2.1 Attention
Quand on m’a posé cette question, j’avais déjà installé la version 2013 d’Excel. Et j’ai essayé les paramètres que j’ai mentionnés ci-dessus dans le scénario post 2013. Mais cela n’a pas fonctionné à ce moment-là et je pensais que ce n’était pas possible et que la couleur et les symboles ne pouvaient pas être insérés. Mais les deux avaient leurs propres problèmes.
Pour appliquer un format personnalisé aux étiquettes de données à l’intérieur des graphiques via un formatage de nombres personnalisé, les étiquettes de données doivent être basées sur des valeurs. Vous avez plusieurs options comme le nom de la série, la valeur des cellules, le nom de la catégorie. Mais il doit s’agir de valeurs sinon les couleurs n’apparaîtront pas.
Le problème des symboles me dépasse. Lorsque j’essaie d’insérer un symbole via les options de graphique, appuyer sur la touche ALT appelle la fonctionnalité de raccourci d’Excel. J’ai essayé de faire la même chose même avec une barre détachée, mais je n’ai toujours pas pu obtenir de travail ALT pour que j’insère des symboles directement dans les options du graphique. C’est-à-dire que j’ai dû copier le code à partir de la fenêtre de la cellule de format comme première étape.