Comment créer un graphique dynamique avec des flèches d'écart sur Excel

Dans ce tutoriel, je vais vous montrer comment créer un graphique dynamique qui affiche clairement les écarts entre deux séries de données avec des flèches colorées.

Cette technique permettra de mettre en avant visuellement les performances mensuelles de votre entreprise par rapport aux objectifs, et d'impressionner vos collègues avec un rendu visuel digne des meilleurs tableaux de bord !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous avons deux séries de données : le « Montant du CA » réellement réalisé chaque mois et le « Budget » prévu :

Notre objectif est de créer un graphique qui permettra de visualiser ces deux valeurs côte à côte tout en mettant en évidence les écarts entre elles à l'aide de flèches vertes pointant vers le haut (lorsque le CA dépasse le budget) ou de flèches rouges pointant vers le bas (lorsque le CA est inférieur au budget).

2. Préparer les données pour notre graphique dynamique

Pour mettre en place le graphique, nous allons ajouter une série de nouvelles colonnes à la suite de ce tableau :

• « Max Valeur »,
• « Écart »,
• « Écart % »,
• « Flèche Haute »,
• « Flèche Basse »,
• « Étiquette + »
• « Étiquette - ».

Maintenant, nous allons calculer les écarts en valeur absolue entre le CA réalisé et le budget prévu en saisissant la formule suivante dans la cellule D7 :

=B7-C7

Cette formule simple soustrait le budget du CA. Si le résultat est positif, cela signifie que le CA a dépassé le budget. S'il est négatif, cela signifie que le CA est inférieur au budget.

Nous étendons cette formule jusqu'à D13 en double cliquant sur la poignée de recopie.

Ensuite, pour calculer l'écart en pourcentage, nous utilisons la formule :

=D7/C7

Cette formule divise l'écart en valeur par le budget, nous donnant ainsi l'écart relatif.

Pour la colonne « Max Valeur » (F), nous avons besoin de déterminer la valeur maximale entre le CA et le Budget pour chaque mois.

Cette valeur servira de référence pour notre graphique.

En F7, nous saisissons la formule :

=MAX(B7;C7)

Excel choisira automatiquement la plus grande des deux valeurs.

3. Préparer les données pour les flèches d'écart

Pour créer les flèches montantes (vertes), nous devons identifier les situations où le CA est supérieur au Budget, et calculer la différence correspondante.

En G7, nous saisissons la formule :

=SI(E7>0;E7;#N/A)

Cette formule vérifie si la valeur dans la cellule E7 est positive. Si c'est le cas, elle affiche cette valeur. Sinon, elle renvoie #N/A pour ne pas afficher de point sur le graphique.

Pour obtenir les flèches descendantes (rouges), nous identifions les situations où le CA est inférieur au Budget.

En H7, nous saisissons la formule :

=SI(E7<0;-E7;#N/A)

Cette formule vérifie si le CA est inférieur au Budget.

Si c'est le cas (E7<0), elle retourne l'inverse de l'écart négatif (qui sera donc toujours positive).

Sinon, elle retourne #N/A, ce qui empêche l'affichage du point sur le graphique.

L'utilisation de ces formules nous permet de séparer clairement les écarts positifs et négatifs dans des colonnes distinctes, en n'affichant que les points pertinents pour chaque série, ce qui facilitera grandement la création des flèches dans notre graphique.

4. Créer et personnaliser le graphique dynamique

   4.1. Insérer le graphique de base

Maintenant que nous avons préparé toutes les données nécessaires, nous pouvons passer à la création du graphique proprement dit.

Pour cela, nous devons sélectionner quatre premières colonnes du tableau (Mois, CA, Budget et Max Valeur), puis nous nous rendons dans l'onglet « Insertion » du ruban, puis dans le groupe « Graphiques », nous cliquons sur « Histogramme » et choisissons « Histogramme groupé » (le premier modèle d'histogramme 2D).

Excel crée alors un graphique de base avec nos trois séries de données (CA, Budget et Max Valeur).

Pour pouvoir créer les flèches dans les deux sens, nous allons maintenant ajouter une deuxième fois la barre « Max valeur ».

Pour cela, nous sélectionnons le graphique, puis nous nous rendons dans le menu « Conception » du ruban, et nous cliquons sur « Sélectionner des données ».

Dans la boîte de dialogue qui apparaît, nous cliquons sur « Ajouter » pour ajouter une nouvelle série et nous sélectionnons les cellules de la colonne « Max valeur ».

Avant de fermer la boîte de dialogue, nous plaçons l’une des barres en première position, et l’autre à la fin en utilisant les flèches de positionnement :

   4.2. Ajouter et configurer les barres d'erreur (flèches)

Pour ajouter les flèches d'écart à notre graphique, nous allons utiliser une fonctionnalité peu connue mais très puissante d'Excel : les barres d'erreur personnalisées.

Ces barres d'erreur, normalement utilisées pour indiquer la marge d'erreur dans les graphiques scientifiques, peuvent être détournées pour créer nos flèches d'écart.

Nous commençons par sélectionner la série « Max Valeur » dans notre graphique en cliquant sur l’une des barres correspondantes.

Une fois la série « Max Valeur » sélectionnée, nous cliquons sur le bouton « + » qui apparaît près du graphique, puis cochons l'option « Barres d'erreur ».

Ensuite, nous cliquons sur la flèche à droite de « Barres d'erreur » et sélectionnons « Autres options ».

Dans le volet « Format des barres d'erreur » qui s'ouvre, nous sélectionnons « Moins » en haut :

Puis dans la section « Options de barres d'erreur », nous choisissons « Personnalisées » et cliquons sur « Spécifier une valeur ».

Dans la boîte de dialogue qui s'ouvre, pour « Valeur d'erreur négative », nous sélectionnons la plage G7:G18 (Flèche Haute).

Nous cliquons sur « OK » pour fermer cette boîte de dialogue.

Toujours dans le volet « Format des barres d'erreur », nous cliquons sur « Contour » et sélectionnons « Automatique » comme type de ligne, puis nous choisissons le vert comme couleur.

Nous augmentons la largeur de la ligne à 2 pt pour la rendre bien visible.

Nous cliquons ensuite sur « Type de flèche initiale », nous choisissons « Flèche » :

Nous répétons ce processus pour configurer les barres d'erreur de la deuxième colonne « Max valeur » pour les flèches descendantes, mais cette fois-ci, nous sélectionnons « Positive » en haut du volet « Format des barres d'erreur », nous utilisons la plage H2 (Flèche Basse) comme valeur d'erreur positive, nous choisissons le rouge comme couleur de ligne, et dans « Effets », nous sélectionnons « Aucune flèche » pour « Début de flèche » et un style de flèche ouvert pour « Fin de flèche ».

   4.3. Personnaliser l'apparence du graphique

Maintenant que nos flèches sont en place, nous allons personnaliser l'apparence générale de notre graphique pour le rendre plus professionnel et plus lisible.

Tout d'abord, nous voulons masquer la série « Max Valeur » qui nous a servi uniquement à positionner correctement nos flèches.

Pour cela, nous sélectionnons cette série dans le graphique, puis nous cliquons avec le bouton droit et choisissons « Format de série de données ».

Dans le volet qui s'ouvre, nous cliquons sur « Remplissage et trait », puis sous « Remplissage », nous sélectionnons « Pas de remplissage ».

Ainsi, les barres de la série « Max Valeur » deviennent invisibles, mais les flèches restent en place.

Pour améliorer la lisibilité de notre graphique, nous ajoutons aussi des étiquettes de données.

Nous sélectionnons la série « Max valeur » de gauche, cliquons sur le bouton « + » afin de sélectionner « Étiquettes de données » :

Puis, nous sélectionnons l’une de ces étiquettes en effectuant un clic-droit, pour sélectionner l’option « mettre en forme les étiquettes de données » :

Dans la liste des options d’étiquettes, nous choisissons « Valeur à partir des cellules » :

Et nous sélectionnons les cellules de la colonne « Écart % » :

Pour finaliser notre graphique, nous allons ajuster quelques détails de mise en page pour le rendre encore plus professionnel :

Nous allons ajuster la manière dont les séries sont affichées, avec une superposition des séries définies à 63% et une largeur d’intervalle réduite à 0%

• Nous allons également donner un titre à notre graphique, par exemple « Comparaison CA vs Budget ».
• Nous supprimer les entrées « Max valeur » de la légende du graphique