Heading

La mise en forme conditionnelle est un moyen fantastique de visualiser rapidement les données dans une feuille de calcul. Avec le formatage conditionnel, vous pouvez faire des choses comme mettre en évidence les dates des 30 prochains jours, signaler les problèmes de saisie de données, mettre en évidence les lignes qui contiennent les meilleurs clients, afficher les doublons, et plus encore.

Excel est livré avec un grand nombre de « préréglages » qui facilitent la création de nouvelles règles sans formules. Cependant, vous pouvez également créer des règles avec vos propres formules personnalisées. En utilisant votre propre formule, vous reprenez la condition qui déclenche une règle, et pouvez appliquer exactement la logique dont vous avez besoin. Les formules vous donnent un maximum de pouvoir et de flexibilité.

Par exemple, en utilisant la présélection  » Égal à « , il est facile de mettre en évidence les cellules égales à  » pomme « .

Mais que faire si vous voulez mettre en évidence les cellules égales à  » pomme  » ou  » kiwi  » ou  » citron vert  » ? Bien sûr, vous pouvez créer une règle pour chaque valeur, mais c’est beaucoup de problèmes. Au lieu de cela, vous pouvez simplement utiliser une règle basée sur une formule avec la fonction OR :

Voici le résultat de la règle appliquée à la plage B4:F8 dans cette feuille de calcul :

Voici la formule exacte utilisée :

=OR(B4="apple",B4="kiwi",B4="lime")

Début rapide
Vous pouvez créer une règle de mise en forme conditionnelle basée sur une formule en quatre étapes faciles :
1. Sélectionnez les cellules que vous souhaitez mettre en forme.

2. Créez une règle de mise en forme conditionnelle, puis sélectionnez l’option Formule

3. Saisissez une formule qui renvoie VRAI ou FAUX.

4. Définissez les options de formatage et enregistrez la règle.

La fonction ISODD ne renvoie VRAI que pour les nombres impairs, ce qui déclenche la règle:

Vidéo : Comment appliquer un formatage conditionnel avec une formule
Nous proposons également une formation vidéo sur ce sujet.
Logique des formules
Les formules qui appliquent un formatage conditionnel doivent retourner VRAI ou FAUX, ou des équivalents numériques. Voici quelques exemples :
=ISODD(A1)=ISNUMBER(A1)=A1>100=AND(A1>100,B1<50)=OR(F1="MN",F1="WI")

Les formules ci-dessus renvoient toutes VRAI ou FAUX, elles fonctionnent donc parfaitement comme déclencheur de mise en forme conditionnelle.
Lorsque la mise en forme conditionnelle est appliquée à une plage de cellules, saisissez les références de cellule par rapport à la première ligne et à la première colonne de la sélection (c’est-à-dire la cellule supérieure gauche). L’astuce pour comprendre le fonctionnement des formules de formatage conditionnel consiste à visualiser la même formule appliquée à chaque cellule de la sélection, les références de cellule étant mises à jour comme d’habitude. Imaginez que vous ayez saisi la formule dans la cellule supérieure gauche de la sélection, puis que vous l’ayez copiée dans toute la sélection. Si vous avez du mal à le faire, consultez la section sur les formules fictives ci-dessous.

Exemples de formules
Vous trouverez ci-dessous des exemples de formules personnalisées que vous pouvez utiliser pour appliquer un formatage conditionnel. Certains de ces exemples peuvent être créés à l’aide des préréglages intégrés d’Excel pour la mise en évidence des cellules, mais les formules personnalisées peuvent aller bien au-delà des préréglages, comme vous pouvez le voir ci-dessous.
Voir aussi : Plus de 30 formules de mise en forme conditionnelle 
Mettre en évidence les commandes du Texas
Pour mettre en évidence les lignes qui représentent les commandes du Texas (en abrégé TX), utilisez une formule qui verrouille la référence à la colonne F :
=$F5="TX"


Pour plus de détails, consultez cet article : Mettre en évidence les lignes avec une mise en forme conditionnelle.
Vidéo : Comment mettre en surbrillance des lignes avec un formatage conditionnel
Mettre en surbrillance des dates dans les 30 prochains jours
Pour mettre en surbrillance des dates survenant dans les 30 prochains jours, nous avons besoin d’une formule qui (1) s’assure que les dates sont dans le futur et (2) s’assure que les dates sont à 30 jours ou moins d’aujourd’hui. Une façon de le faire est d’utiliser la fonction AND conjointement avec la fonction NOW comme ceci :
=AND(B4>NOW(),B4<=(NOW()+30))

Avec une date actuelle du 18 août 2016, la mise en forme conditionnelle met en évidence les dates comme suit :

La fonction NOW renvoie la date et l’heure actuelles. Pour plus de détails sur le fonctionnement de cette formule, consultez cet article : Mettre en évidence les dates dans les N prochains jours.
Mettre en évidence les différences entre les colonnes
Du fait de deux colonnes qui contiennent des informations similaires, vous pouvez utiliser le formatage conditionnel pour repérer les différences subtiles. La formule utilisée pour déclencher la mise en forme ci-dessous est :
=$B4<>$C4


Voir aussi : une version de cette formule qui utilise la fonction EXACT pour effectuer une comparaison sensible à la casse.
Mettre en évidence les valeurs manquantes
Pour mettre en évidence les valeurs d’une liste qui manquent dans une autre, vous pouvez utiliser une formule basée sur la fonction COUNTIF :
=COUNTIF(list,B5)=0


Cette formule vérifie simplement chaque valeur de la liste A par rapport aux valeurs de la plage nommée « liste » (D5:D10). Lorsque le compte est égal à zéro, la formule renvoie VRAI et déclenche la règle, qui met en évidence les valeurs de la liste A qui manquent dans la liste B.
Vidéo : Comment trouver les valeurs manquantes avec COUNTIF

Mettre en évidence les propriétés avec 3+ chambres à coucher sous 350k$
Pour trouver les propriétés de cette liste qui ont au moins 3 chambres à coucher mais qui sont inférieures à 300 000 $, vous pouvez utiliser une formule basée sur la fonction AND :
=AND($C5<350000,$D5>=3)

Les signes dollar ($) verrouillent la référence aux colonnes C et D, et la fonction AND est utilisée pour s’assurer que les deux conditions sont VRAIES. Dans les lignes où la fonction AND renvoie VRAI, la mise en forme conditionnelle est appliquée :

Mise en évidence des valeurs supérieures (exemple dynamique)
Bien qu’Excel dispose de préréglages pour les  » valeurs supérieures « , cet exemple montre comment faire la même chose avec une formule, et comment les formules peuvent être plus flexibles. En utilisant une formule, nous pouvons rendre la feuille de calcul interactive – lorsque la valeur dans F2 est mise à jour, la règle répond instantanément et met en évidence les nouvelles valeurs.

La formule utilisée pour cette règle est:
=B4>=LARGE(data,input)

Où « données » est la plage nommée B4:G11, et « entrée » est la plage nommée F2. Cette page contient des détails et une explication complète.
Gantt charts
Croyez-le ou non, vous pouvez même utiliser des formules pour créer des diagrammes de Gantt simples avec un formatage conditionnel comme celui-ci :

Cette feuille de calcul utilise deux règles, une pour les barres, et une pour l’ombrage du week-end :
=AND(D$4>=$B5,D$4<=$C5) // bars=WEEKDAY(D$4,2)>5 // weekends

Cet article explique la formule pour les barres, et cet article explique la formule pour l’ombrage du week-end.
La boîte de recherche simple
Une astuce sympa que vous pouvez faire avec le formatage conditionnel est de construire une boîte de recherche simple. Dans cet exemple, une règle met en évidence les cellules de la colonne B qui contiennent du texte tapé dans la cellule F2:

La formule utilisée est :
=ISNUMBER(SEARCH($F$2,B2))

Pour plus de détails et une explication complète, consultez :

Article : Comment mettre en évidence les cellules qui contiennent du texte spécifique 
Article : Comment mettre en évidence les lignes qui contiennent du texte spécifique 
Vidéo : Comment construire une boîte de recherche pour mettre des données en surbrillance 

Dépannage
Si vous ne parvenez pas à déclencher correctement vos règles de formatage conditionnel, il y a très probablement un problème avec votre formule. Tout d’abord, assurez-vous que vous avez commencé la formule avec un signe égal (=). Si vous oubliez cette étape, Excel convertira silencieusement l’ensemble de votre formule en texte, ce qui la rendra inutile. Pour corriger, il suffit de supprimer les guillemets doubles qu’Excel a ajoutés de part et d’autre et de s’assurer que la formule commence par des égaux (=).
Si votre formule est saisie correctement, mais qu’elle ne déclenche pas la règle, vous devrez peut-être creuser un peu plus. Normalement, vous pouvez utiliser la touche F9 pour vérifier les résultats d’une formule, ou utiliser la fonction Évaluer pour parcourir une formule. Malheureusement, vous ne pouvez pas utiliser ces outils avec les formules de mise en forme conditionnelle, mais vous pouvez utiliser une technique appelée  » formules factices « .
Formules factices
Les formules factices sont un moyen de tester vos formules de mise en forme conditionnelle directement sur la feuille de calcul, afin que vous puissiez voir ce qu’elles font réellement. Cela peut vous faire gagner beaucoup de temps lorsque vous avez du mal à faire fonctionner correctement les références de cellules.
En bref, vous entrez la même formule dans une plage de cellules qui correspond à la forme de vos données. Cela vous permet de voir les valeurs renvoyées par chaque formule, et c’est un excellent moyen de visualiser et de comprendre le fonctionnement du formatage conditionnel basé sur des formules. Pour une explication détaillée, consultez cet article.

Vidéo : Testez le formatage conditionnel avec des formules factices
Limitations
Il y a quelques limitations qui viennent avec le formatage conditionnel basé sur des formules :

Vous ne pouvez pas appliquer des icônes, des échelles de couleur ou des barres de données avec une formule personnalisée. Vous êtes limité au formatage standard des cellules, y compris les formats de nombre, la police, la couleur de remplissage et les options de bordure.
Vous ne pouvez pas utiliser certaines constructions de formule comme les unions, les intersections ou les constantes de tableau pour les critères de formatage conditionnel.
Vous ne pouvez pas faire référence à d’autres classeurs dans une formule de formatage conditionnel.

Vous pouvez parfois contourner les points 2 et 3. Vous pouvez être en mesure de déplacer la logique de la formule dans une cellule de la feuille de calcul, puis de faire référence à cette cellule dans la formule à la place. Si vous essayez d’utiliser une constante de tableau, essayez de créer une plage nommée à la place.
Il est possible de contourner ces problèmes.