Mise en forme conditionnelle avec des formules dans Excel
Par défaut, Excel nous a déjà fourni de nombreux types de mise en forme conditionnelle disponibles pour nos données, mais si un utilisateur souhaite que la mise en forme soit effectuée sur des critères ou une formule spécifiques, nous pouvons le faire dans l'onglet d'accueil de la section de mise en forme conditionnelle lorsque nous cliquez sur la nouvelle règle, nous trouverons une option de mise en forme conditionnelle avec des formules où nous pouvons écrire des formules qui définiront les cellules à formater.
La mise en forme conditionnelle avec Formula n'est rien d'autre que de changer le format des cellules en fonction de la condition ou des critères donnés par l'utilisateur. Vous avez peut-être utilisé la mise en forme conditionnelle pour mettre en évidence la valeur supérieure de la plage, ainsi que les valeurs en double.
Une bonne chose est que nous avons des formules en formatage conditionnel dans Excel. Toutes les formules doivent être logiques; le résultat sera VRAI ou FAUX. Si le test logique réussit, nous obtiendrons un formatage conditionnel, et si le test logique Excel échoue, nous n'obtiendrons rien.
Aperçu
Permettez-moi de vous présenter la mise en forme conditionnelle avec la fenêtre de formule.
Dans la section ci-dessous de l'article, nous verrons une grande variété d'exemples d'application de la mise en forme conditionnelle avec des formules.
Comment utiliser la mise en forme conditionnelle Excel avec des formules?
# 1 - Mettez en surbrillance les cellules dont les valeurs sont inférieures à 500
Supposons ci-dessous les chiffres de vente avec lesquels vous travaillez et ci-dessous le prix de vente par unité.
- Étape 1: Accédez à la mise en forme conditionnelle et cliquez sur Nouvelle règle.
- Étape 2: sélectionnez maintenant " Utiliser une formule pour déterminer les cellules à mettre en forme".
- Étape 3: Maintenant, sous « Mettre en forme les valeurs où une formule est vraie», appliquez la formule comme A2 <500, puis cliquez sur Format pour appliquer le formatage Excel.
- Étape 4: Sélectionnez le format selon votre souhait.
- Étape 5: Vous pouvez maintenant voir l'aperçu du format dans la zone d'aperçu.
- Étape 6: Maintenant, cliquez sur OK pour terminer le formatage. Maintenant, il a mis en évidence toutes les cellules qui ont un nombre <500.
# 2 - Mettez en surbrillance une cellule basée sur une autre cellule
Nous pouvons mettre en évidence une cellule en fonction d'une autre valeur de cellule. Supposons que les données sur le nom du produit et le prix du produit figurent dans les deux premières colonnes.
À partir du tableau ci-dessus, nous devons mettre en évidence les produits si le prix de vente est> 220.
- Étape 1: Sélectionnez la gamme de produits puis, allez à la mise en forme conditionnelle et cliquez sur Nouvelle règle.
- Étape 2: Dans la formule, appliquez la formule B2> 220.
- Étape 3: Cliquez sur la touche Format et appliquez le format selon votre souhait.
- Étape 5: Cliquez sur OK; nous avons le formatage prêt.
# 3 - Mettez en surbrillance toutes les cellules vides de la plage
Supposons ci-dessous les données dont vous disposez.
Dans les données ci-dessus, vous devez mettre en évidence toutes les cellules, qui sont vides ou vides. Nous devons utiliser la formule ISBLANK dans le formatage conditionnel Excel.
Appliquez la formule ci-dessous dans la section formules.
Sélectionnez la mise en forme selon votre souhait.
Cliquez sur ok, cela mettra en évidence toutes les cellules vides de la plage sélectionnée.
# 4 - Utilisez la fonction AND pour mettre en évidence les cellules
Considérez les données ci-dessous pour cette formule.
Si la région est centrale et que la valeur des ventes est> 500, nous devons mettre en évidence la ligne complète.
- Étape 1: Sélectionnez d'abord toutes les données.
- Étape 2: Ici, nous devons utiliser la fonction AND excel car nous devons tester deux conditions ici, et les deux conditions doivent être TRUE. Appliquez la formule ci-dessous.
- Étape 3: Cliquez sur Format.
- Étape 4: Accédez à REMPLIR et sélectionnez la couleur et les effets requis.
- Étape 5: Cliquez sur OK. Nous verrons les lignes en surbrillance si les deux conditions sont VRAIES.
# 5- Utilisez la fonction OR pour mettre en évidence les cellules
Considérez les données ci-dessous pour l'exemple.
Dans la plage de données ci-dessus, je souhaite mettre en évidence les noms de villes Bangalore et Mysore.
- Étape 1: Sélectionnez d'abord les données et accédez à Mise en forme conditionnelle, puis cliquez sur Nouvelle règle.
- Étape 2: Puisque nous devons mettre en évidence l'une des deux cellules de valeur, appliquez la fonction OR excel.
- Étape 3: Cliquez sur le format et sélectionnez le format requis.
- Étape 4: Une fois le formatage appliqué, cliquez sur OK pour terminer la tâche. Une formule mettrait en évidence toutes les cellules qui ont des valeurs Bangalore & Mysore.
# 6 - Utilisez la fonction COUNTIF pour mettre en évidence les cellules
Supposons que vous travaillez avec la base de données clients. Vous devez identifier tous les clients dont les noms apparaissent plus de cinq fois dans la liste. Voici les données.
Sélectionnez les données et appliquez la formule COUNTIF dans la section formule.
Cliquez sur le format et appliquez le formatage requis.
Maintenant, la formule mettra en évidence tous les noms si le nombre est supérieur à 5.
L'ambre est le seul nom qui apparaît plus de cinq fois sur la liste.
# 7 - Mettez en surbrillance chaque ligne alternative
Nous pouvons également avoir la formule pour mettre en évidence chaque ligne alternative dans Excel des données. Supposons ci-dessous les données sur lesquelles vous travaillez.
Sélectionnez les données et appliquez la formule ci-dessous.
= MOD (RANG (), 2)
Appliquez au format selon votre souhait et cliquez sur OK; nous aurions chaque ligne alternative mise en évidence par la formule.
# 8 - Mettez en surbrillance chaque colonne alternative
Comme nous avons mis en évidence chaque ligne alternative de la même manière, nous pouvons mettre en évidence chaque colonne alternative. Considérez les mêmes données pour l'exemple.
Appliquez la formule ci-dessous.
= MOD (COLONNE (), 2)
Copiez la formule ci-dessus et appliquez-la dans la section formule de la mise en forme conditionnelle.
Cliquez sur OK; il mettra en évidence la ligne alternative.
Si vous souhaitez supprimer la première colonne et mettre en surbrillance de la deuxième colonne, vous devez utiliser la formule ci-dessous.
Il mettrait en évidence la colonne alternative à partir de la deuxième colonne.
Choses dont il faut se rappeler
- Le formatage conditionnel n'accepte que les formules logiques dont les résultats ne sont que VRAI ou FAUX.
- Un aperçu de la mise en forme conditionnelle n'est qu'une indication de l'aspect de la mise en forme.
- N'utilisez jamais de référence absolue comme dans la formule. Lorsque vous appliquez la formule, et si vous sélectionnez la cellule directement, cela en ferait une référence absolue, vous devez en faire une référence relative dans Excel.
