Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?

Dans ce tutoriel, je vais vous montrer comment contrôler les saisies et éviter les erreurs dans un tableau Excel en entourant automatiquement les cellules non conformes.

Pour effectuer cette opération, nous verrons qu’il n’est pas nécessaire d’utiliser la mise en forme conditionnelle ni de devoir mettre en place des formules complexes.

Cela nous permet d’identifier rapidement les erreurs dans nos feuilles Excel.

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, imaginons que nous travaillons dans une entreprise qui gère des pièces informatiques. Nous recevons un bon de commande pour différentes pièces, et nous voulons vérifier si chacune de ces pièces est disponible dans notre stock. Notre objectif est d'identifier les pièces commandées qui ne sont pas en stock.

Pour cela, nous avons donc deux tableaux : le premier sera notre bon de commande, et le second sera l’état des stocks actuel.

L’idée ici est de vérifier automatiquement si chaque article du bon de commande est bien en stock, et de signaler ceux qui ne figurent pas dans la liste du stock.

2. Appliquer la validation des données et entourer les pièces non présentes en stock

Maintenant que nous avons notre bon de commande et notre état des stocks, nous allons utiliser la validation des données pour vérifier la disponibilité des articles commandés.

Cet outil très pratique nous permet de restreindre les valeurs saisies dans une colonne pour qu’elles soient présentes dans une autre colonne (en l’occurrence, notre état des stocks).

Nous commençons par sélectionner la colonne « Référence » du bon de commande, afin de pouvoir vérifier que chaque pièce commandée correspond à une référence présente dans notre état des stocks.

Une fois la plage sélectionnée, nous nous rendons dans l'onglet « Données » du ruban Excel, puis nous cliquons sur « Validation des données ».

Dans la fenêtre qui s'ouvre, sous l'onglet « Options », nous choisissons l'option « Liste » dans le menu déroulant « Autoriser ».

Dans le champ « Source », nous allons maintenant sélectionner la plage de références qui correspond à l'état des stocks.

Ici, nous pouvons soit entrer manuellement =$D$9:$DE$25 (la plage contenant les références dans le tableau des stocks), soit sélectionner ces cellules directement avec notre souris.

Nous pouvons également sélectionner une grande plage de cellules, pour pouvoir tenir compte des éventuelles livraisons de pièces. Ici, nous sélectionnons directement toute la colonne D en cliquant directement sur la lettre de la colonne :

Maintenant que la validation des données est en place, Excel empêchera l'entrée de toute référence qui ne se trouve pas dans la liste des stocks.

Par exemple, si nous saisissons une ligne « Siège Gamer » dans la liste des références du bon de commande, Excel va refuser la saisie en nous présentant un message d’erreur :

Toutefois, ce que nous voulons, c'est identifier les articles du bon de commande qui ne figurent pas dans le stock.

Pour cela, nous allons utiliser la fonctionnalité « Entourer les données non valides ».

Nous nous rendons à nouveau dans l'onglet « Données » du ruban, puis nous cliquons à nouveau sur « Validation des données ». Cette fois, nous choisissons « Entourer les données non valides ».

Dès lors, toutes les pièces du bon de commande qui ne figurent pas dans la liste des stocks seront entourées d’un cercle rouge. Cela nous permet de visualiser rapidement quelles pièces ne sont pas disponibles sans devoir consulter manuellement chaque ligne.

Pour aller plus loin, nous pouvons personnaliser le message d'erreur qui apparaît lorsqu’une référence non présente dans le stock est saisie.

Pour ce faire, nous retournons dans « Validation des données », puis dans l’onglet « Alerte d’erreur ».

Nous choisissons l’option « Arrêt » pour bloquer la saisie des références non conformes, ou bien « Avertissement » pour simplement afficher un message sans bloquer l'entrée.

Nous pouvons personnaliser le message d’avertissement en entrant, par exemple : « Cette référence n'est pas présente dans le stock. Vérifiez la disponibilité avant de passer la commande. »

Maintenant, Excel affichera un message d'avertissement si une pièce non présente dans le stock est saisie, mais l'utilisateur pourra choisir de confirmer l’ajout de cette pièce.

Si nous cliquons sur « Oui », la saisie sera bien insérée, mais la valeur ne sera pas entourée, car la fonctionnalité « Entourer les données non valides » ne s’actualise pas automatiquement lors de l’ajout d'une valeur.

Nous devons à nouveau cliquer sur « Entourer les données non valides » afin qu’Excel rafraîchisse la mise en surbrillance et mette à jour les cercles autour des données non conformes.

Cela réappliquera la validation à toutes les cellules de la feuille, sans qu’il ne soit nécessaire de les sélectionner à nouveau, afin de souligner à nouveau toute entrée qui ne figure pas dans notre stock :

De même, si nous mettons à jour notre stock en ajoutant la référence « Siège Gamer », celle-ci restera entourée, car la fonctionnalité « Entourer les données non valides » ne reconnaît pas immédiatement les modifications apportées à la liste source.

Excel ne vérifie pas automatiquement les nouvelles références ajoutées à l'état des stocks après l'application initiale de la validation. Ici encore, nous allons devoir revenir cliquer sur « Entourer les données non valides » pour que la feuille soit à jour et que les nouvelles données ajoutées au stock soient correctement prises en compte

Ces astuces nous permettront de mieux gérer nos commandes et d’éviter les erreurs courantes dans la gestion des stocks. En utilisant régulièrement la fonctionnalité « Entourer les données non valides » après chaque modification importante (soit dans le bon de commande, soit dans l'état des stocks), nous nous assurons que les informations restent cohérentes et visuellement faciles à suivre.