Comment modifier la mise en forme d’un tableau à la volée en 1 clic sur Excel (sans VBA) ?

Dans ce tutoriel, je vais vous montrer comment ajouter des fonctionnalités sur un tableau en utilisant des cases à cocher en 1 seul clic, sans VBA.

Cela nous permettra d’analyser les ventes réalisées par une entreprise, en faisant apparaître les valeurs les plus élevées en vert et au contraire, les valeurs les plus faibles en rouge.

Nous allons souhaiter modifier le nombre d'éléments mis en valeur en utilisant directement un objet de sélection que nous retrouvons juste à côté du tableau.

Par la suite, nous allons également mettre en évidence les valeurs supérieures ou inférieures à la moyenne.

Pour finir, nous allons également voir comment utiliser une case d'option pour faire apparaître des barres d’histogramme ou encore des étoiles qui vont nous permettre de comparer directement les différentes valeurs entre elles.

Juste avant de commencer, je vous rappelle que cette semaine c'est le Black Friday sur excelformation.fr, et à cette occasion, vous pourrez obtenir des packs exclusifs reprenant l'ensemble des formations et des applications que j'ai pu mettre en place au cours de ces dernières années en cliquant ici.

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. Comment appliquer une mise en forme conditionnelle sur les cellules d’un tableau ?

Comme je vous l'expliquais dans l'introduction, nous allons voir comment mettre en place une mise en forme conditionnelle sur un tableau, que nous pourrons ensuite activer ou désactiver à la demande en utilisant de simples cases à cocher.

Avant cela, nous allons commencer par mettre en place les différentes règles de mise en forme conditionnelles sur nos données.

La première de ces mises en forme conditionnelles va consister à faire ressortir les plus grandes et les plus petites valeurs de notre tableau de données.

Ici, ce tableau reprend le montant des ventes trimestrielles qu'une entreprise a réalisées au sein de différents pays.

Pour faire ressortir les trimestres et les pays dans lesquels les ventes ont été les plus importantes, nous sélectionnons les données de notre tableau, puis nous allons nous rendre dans le menu « Accueil » et déployer le menu « Mise en forme conditionnelle » afin de nous rendre dans « Règle des valeurs de plage haute/basse » et nous allons choisir de mettre en place une mise en forme conditionnelle sur les « 10 valeurs les plus élevées… » :

Excel nous affiche alors une boîte de dialogue, qui nous demande combien de valeurs nous allons souhaiter mettre en forme.

Ici, nous pouvons rester sur dix, nous verrons un petit peu plus tard comment ajuster cette valeur de manière dynamique.

Ensuite, étant donné qu'il s'agit des valeurs les plus élevées, nous allons choisir ici un « Remplissage vert avec texte vert foncé » :

Cela étant fait, nous allons recommencer la même opération, afin cette fois-ci de faire ressortir les valeurs les moins élevées :

Que nous faisons apparaître en rouge :

Voyons maintenant comment activer et désactiver ces mises en forme directement en utilisant des cases à cocher.

2. Comment insérer des cases à cocher sur une feuille Excel ?

Pour pouvoir insérer une case à cocher directement sur la feuille de calcul, nous allons devoir utiliser le menu « Développeur » du ruban, lequel n’est pas présent par défaut dans Excel.

Pour l’afficher, suivez simplement ce lien.

Une fois le menu « Développeur » ajouté dans le menu ruban, nous allons pouvoir nous rendre à l'intérieur de celui-ci et dérouler le menu « Insérer » pour choisir un contrôle de formulaire de type « Case à cocher » :

Nous insérons cet élément sur la feuille de calcul, et nous allons ensuite donc supprimer le texte de ce dernier, qui ne va pas nous intéresser :

Pour utiliser le statut de cette case à cocher, nous allons devoir la lier avec une cellule.

Pour cela, nous effectuons un clic droit sur la case et nous choisissons « Format de contrôle ».

Ensuite, dans l'onglet « Contrôle », nous allons pouvoir choisir une cellule liée avec cet élément, ici la cellule J9 :

Maintenant, si nous cliquons sur la case, la valeur « VRAI » va s’affiche sur la cellule J9, juste en dessous de la case à cocher :

Si nous cliquons une seconde fois, Excel va afficher « FAUX » dans la cellule.

Nous allons maintenant pouvoir répéter cette opération pour insérer une case à cocher sur la cellule située juste en dessous, ou alors pour gagner du temps nous pouvons également sélectionner la cellule J9 que nous allons copier en appuyant sur [Ctrl]+[C]. Ensuite, nous nous plaçons sur cellule juste en dessous, aux coordonnées J10 et nous appuyons sur [Ctrl]+[V] pour dupliquer automatiquement la case à cocher.

En revanche, lorsque nous allons désactiver celle-ci, nous pourrons constater que ce n'est pas la cellule J10 qui sera modifiée, mais la cellule J9 :

Pas de soucis, il suffit de lier la nouvelle case à cocher avec la cellule correspondante en effectuant un nouveau clic droit sur la case à cocher afin de choisir « Format de contrôle » et de modifier les coordonnées de la cellule liée.

Maintenant que nous avons nos deux cases à cocher indépendantes l'une de l'autre, nous allons pouvoir modifier les règles de mise en forme conditionnelle que nous avons établies juste avant afin de tenir compte de ces différentes cases.

3. Activer et désactiver la mise en forme en 1 clic

Pour modifier les règles de mise en forme conditionnelle, nous sélectionnons toutes les cellules de notre tableau, puis nous allons à nouveau nous rendre dans le menu « Accueil » afin de déployer le menu « Mise en forme conditionnelle », sauf qu'ici nous allons nous rendre directement sur « Gérer les règles » :

Cela va nous permettre de retrouver automatiquement toutes les règles que nous avions mises en place précédemment :

Nous allons commencer par modifier la première règle, qui concernent les valeurs les moins élevés de notre tableau.

Sauf qu’ici, nous n'allons pas vouloir utiliser la règle automatique définie par Excel, laquelle n’est pas dynamique.

Nous choisissons donc l’option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué » :

Pour obtenir les valeurs les plus basses du tableau, nous allons pouvoir utiliser la fonction RANG(), qui permet de classer la valeur d’une cellule parmi toutes les cellules contenues dans une plage.

Ici, nous allons vouloir récupérer la position de la cellule active (B9), donc nous saisissons les références de celle-ci en tant que premier argument de la fonction RANG(), en utilisant bien une référence relative (c'est-à-dire sans les symboles dollars, afin que celle-ci puisse s'adapter automatiquement pour modifier la mise en forme conditionnelle sur toutes les cellules sélectionnées) :

Étant donné que nous souhaitons connaître le rang de cette cellule à l'intérieur de l'ensemble de la plage sélectionnée (c'est-à-dire les cellules B9 jusqu'à E16), nous allons utiliser ses coordonnées en tant que deuxième argument de la fonction RANG(), mais cette fois-ci nous allons vouloir que la plage de l'ensemble des cellules reste fixe, peu importe la cellule sur laquelle nous allons étudier la mise en forme conditionnelle.

Nous allons donc transformer cette plage de cellules en une référence absolue en appuyant sur la touche [F4] (si un simple appui sur la touche [F4] n’ajoute pas directement les dollars, c'est que vous êtes probablement sur un PC portable et donc dans ce cas-là il faudra utiliser en même temps la touche [Fn], qui se trouve généralement en bas à gauche du clavier) :

Pour le troisième argument de la fonction RANG(), nous allons pouvoir spécifier dans quel sens nous allons vouloir effectuer le classement, afin d'obtenir uniquement les valeurs les plus faibles.

Ici, nous allons donner la valeur « 1 » à cet argument :

Cela étant fait, il nous restera à vérifier si cette valeur de classement de la cellule active est bien inférieure ou égale à dix :

 =RANG(B9;$B$9:$E$16;1)<=10 

Ensuite, nous pouvons valider en appuyant sur [Ok].

En apparence, rien n’a changé, ce sont exactement les mêmes cellules qui sont mises en évidence, mais cela va maintenant nous permettre de mettre en place un deuxième test à effectuer, afin de voir s'il faut ou pas mettre en place la mise en forme conditionnelle.

Pour cela, nous allons utiliser la fonction ET(), qui va nous permettre d'effectuer deux analyses :

• La première analyse, nous venons de la détailler, elle consiste à regarder si la valeur de la cellule active fait partie des dix valeurs les plus faibles parmi toutes les cellules de notre table,
• La deuxième analyse va tout simplement vérifier si la valeur de la cellule J10 est égale à « VRAI »

 =ET(RANG(B9;$B$9:$E$16;1)<=10;$J$10=VRAI) 

Lorsque nous validons, rien n'a été modifié, en revanche si désactivons la case à cocher « FLOP », nous allons pouvoir constater qu’automatiquement Excel va désactiver la mise en forme conditionnelle :

Ensuite, nous pouvons répéter l’opération pour la case à cocher des « TOP », en sachant que la formule à utiliser est alors la suivante :

 =ET(RANG(B9;$B$9:$E$16;0)<=10;$J$9=VRAI) 

4. Modifier le nombre de cellules avec une mise en forme conditionnelle à la souris

En plus de cela, nous voyons comment définir de manière dynamique le nombre de cellules que nous voulons faire apparaître avec la mise en forme conditionnelle.

Pour cela, nous allons devoir insérer un deuxième élément de formulaire, qui sera cette fois-ci donc un compteur, nous sélectionnons donc l'objet « Toupie » :

Toupie que nous allons lier avec la cellule K9 :

Ensuite, nous allons revenir modifier les formules utilisées dans les règles de mises en forme conditionnelles pour utiliser cette cellule K9 à la place de la valeur 10 :

 =ET(RANG(B9;$B$9:$E$16;1)<=$K$9;$J$10=VRAI) 

 =ET(RANG(B9;$B$9:$E$16;0)<=$K$9;$J$9=VRAI) 

p>

Maintenant, nous pouvons réactiver les mises en forme conditionnelles, et utiliser l’objet toupie pour sélectionner le nombre de cellules à mettre en évidence :

5. Identifier les valeurs supérieures ou inférieures à la moyenne

Maintenant, nous allons ajouter un jeu de nouvelles règles qui va mettre en évidence les cellules supérieures ou inférieures à la moyenne.

Pour commencer, nous pouvons dupliquer les cellules J9 et J10 pour insérer de nouvelles cases à cocher.

Comme nous en avons maintenant l’habitude, nous n’oublions pas de modifier la référence de la cellule liée à chacune de ces cases :

Puis, nous allons pouvoir ajouter les nouvelles mises en forme conditionnelles.

Pour cela, nous sélectionnons les cellules du tableau, puis nous allons nous rendre dans le menu « Mise en forme conditionnelle » de l’onglet « Accueil » du ruban, puis nous allons nous rendre à nouveau sur « Gérer les règles » afin de pouvoir en créer une nouvelle :

Puis, nous allons directement nous rendre sur la ligne « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué », afin de saisir la formule suivante :

 =ET(B9>=MOYENNE($B$9:$E$16);$J$11=VRAI) 

>

Celle-ci permet de contrôler :

• Que la cellule B9 est supérieure ou égale à la moyenne des cellules du tableau
• Et que la cellule J11 est égale à « VRAI » (et donc que la case liée est bien cochée)

Ensuite, nous pouvons cliquer sur le bouton « Format » afin de spécifier la mise en forme à appliquer aux cellules concernées, nous allons simplement choisir de les afficher en gras :

Nous pouvons maintenant répéter l’opération pour appliquer une mise en forme conditionnelle sur les cellules inférieures à la moyenne :

• Nous cliquons sur « Nouvelle règle »,
• Nous choisissons « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué »,
• Nous saisissons la formule : « =ET(B9<MOYENNE($B$9:$E$16);$J$12=VRAI) »,
• Nous cliquons sur « Format »,
• Et enfin, nous choisissons le format « Italique »

Ensuite, nous revenons sur la feuille de calcul pour retrouver les formats conditionnels :

Bien entendu, tous les formats peuvent être appliqués en même temps.

6. Insérer des barres d’histogramme et des étoiles en 1 clic

En bonus, vous retrouverez dans la vidéo de ce tutoriel deux options supplémentaires à appliquer en 1 clic.

Tout d’abord, je vous y explique comment insérer des barres d’histogramme directement dans les cellules de la feuille de calcul, que nous pourrons également activer ou désactiver à la demande d’un simple clic :

Ensuite, nous verrons également comment ajouter des jeux d’icônes afin de permettre de comparer rapidement les valeurs entre elles de manière visuelle :

Pour insérer ces deux objets, nous aurons besoin d’utiliser une petite astuce que je vous détaille dans la vidéo.