Comment utiliser la mise en forme conditionnelle basée sur une autre cellule dans Google Sheets

Il arrive que l'on doive parcourir des masses de données sur une feuille de calcul, souvent à la recherche de certaines valeurs. Cependant, en l'absence d'éléments de conception ou de repères visuels, l'exploration des données peut prendre du temps et être pénible pour les yeux. Heureusement, avec Google Sheets vous pouvez utiliser la mise en forme conditionnelle pour modifier la couleur des cellules recherchées en fonction de leur valeur. Cette fonctionnalité est appelée mise en forme conditionnelle. Elle peut être effectuée sur la base d'une cellule individuelle ou d'une autre cellule. Je vais vous montrer comment cela fonctionne à l'aide de quelques exemples.

Différents types de mise en forme conditionnelle

Exemple 1 : Identifier les cellules en dessous d'un seuil

Je commencerai par un exemple de base utilisant la mise en forme conditionnelle pour modifier la couleur d'une cellule. Considérons la feuille de calcul suivante. Le responsable de l'entrepôt d'une station-service automobile locale l'utilise pour suivre l'inventaire des pièces détachées.

Il doit passer une nouvelle commande pour un produit si la quantité en stock (colonne B) passe en dessous du seuil de 20. Il y a un grand nombre de produits et les valeurs de la colonne B changent au fil du temps. Il lui est donc difficile d'identifier les produits en rupture de stock. Même s'il colorait les cellules manuellement pour les repérer facilement par la suite, cela doublerait son travail - ce qui n'est pas une solution idéale !

La fonctionnalité de mise en forme conditionnelle vient à la rescousse, grâce à laquelle vous pouvez modifier la couleur des cellules en fonction de leur valeur dans Google Sheets. Pour appliquer cette mise en forme, sélectionnez d'abord toutes les cellules de la colonne B.

Naviguez maintenant vers Format > Mise en forme conditionnelle. Une barre latérale s'ouvre à droite de l'écran. Vous remarquerez que dans la section Appliquer à la plage la valeur est déjà définie sur B2:B27. Cela s'explique par le fait que cette plage est déjà sélectionnée. Quoi qu'il en soit, si vous souhaitez modifier la plage, vous pouvez le faire.

Je choisirai Inférieur ou égal à dans le Formater les cellules si dans le champ déroulant. Saisissez ensuite la valeur 20 dans le champ suivant. En fait, vous configurez Google Sheets de manière à ce que formatage des cellules d'une certaine manière s'il remplit cette condition. Je choisirai un style de formatage rouge pour répondre à la situation. Pour terminer, il suffit de cliquer sur le bouton Terminé bouton

Il s'agit d'une configuration unique qui met automatiquement à jour le formatage, même si les données sont modifiées ultérieurement.

J'ai utilisé une condition directe pour cet exemple particulier. Vous pouvez également modifier la couleur des cellules en utilisant une formule. Pour ce faire, il vous suffit de vous assurer que la valeur du champ Formater les cellules si Le champ déroulant est La formule personnalisée est, et saisissez la formule dans le champ suivant.

Exemple 2 : Mise en forme conditionnelle basée sur une autre cellule

Maintenant que vous savez comment utiliser la mise en forme conditionnelle, voyons comment changer la couleur d'une cellule en fonction de la valeur d'une autre cellule. J'utiliserai le même exemple que précédemment. Cette fois-ci, le responsable de l'entrepôt souhaite que l'ID du produit soit mis en évidence au lieu de la quantité.

Pour ce faire, suivez les mêmes étapes que précédemment, mais sélectionnez La formule personnalisée est de la Formater les cellules si dans le champ déroulant. Saisissez ensuite la formule basée sur la cellule de la colonne B. Dans cet exemple, j'ai saisi =B2<=20.

J'ai maintenant obtenu la même mise en évidence des cellules à faible stock, mais l'ID du produit est mis en évidence au lieu de la quantité !

Exemple 3 : Formatage avec plusieurs règles

Vous ne devez pas vous limiter à une seule règle lorsque vous utilisez la mise en forme conditionnelle. L'ajout de plusieurs règles de mise en forme conditionnelle peut vous aider à disséquer visuellement vos données.

Dans cet exemple, un enseignant de cinquième année souhaite mettre en évidence les élèves dont les notes sont inférieures ou supérieures à un seuil. Plus précisément, l'enseignant souhaite que les élèves dont la moyenne est inférieure à 70 soient en rouge et que les élèves dont la moyenne est supérieure à 90 soient en vert.

Pour ce faire, vous devez ajouter deux règles distinctes. Le processus pour chaque règle est très similaire à l'exemple précédent.

Comme vous souhaitez que les noms soient mis en évidence en fonction des scores moyens, mettez les noms en surbrillance en tant que Appliquer à la plage, et ajoutez les scores en tapant la règle dans le champ La formule personnalisée est de la section Formater les cellules si champ déroulant

Une fois la première règle rédigée, appuyez sur Terminé et ensuite Ajouter une autre règle. Les formules que j'ai utilisées dans les règles sont =F2<70 and ="F2">=90.

Exemple 4 : Formatage basé sur plusieurs cellules

Vous pouvez également utiliser la mise en forme conditionnelle basée sur plusieurs cellules, dans le cadre d'une seule règle de mise en forme. Pour ce faire, vous pouvez utiliser les fonctions OR ou AND.

Dans cet exemple, l'enseignant souhaite prendre en compte chaque matière plutôt que la moyenne. Si un élève a une note inférieure à 60 dans une matière, son nom doit être surligné en rouge pour que l'enseignant puisse voir rapidement qui a des difficultés.

La méthode utilisée ici est similaire à celle des autres exemples - j'espère que vous vous y êtes habitués ! La différence est que, dans le La formule personnalisée est de la section Formater les cellules si champ déroulant, type =OR(B2<60,C2<60,D2<60,E2<60).

Changer la couleur d'une cellule selon la valeur d'une autre cellule

Pour changer la couleur d'une cellule en fonction de la valeur d'une autre cellule, utilisez une formule personnalisée qui pointe vers cette autre cellule. C'est utile lorsque vos données dépendent beaucoup les unes des autres ou que vous avez beaucoup de données dynamiques. Voici un deuxième exemple, avec une liste de commerciaux. Imaginons que je veuille mettre en évidence en rouge les noms des commerciaux qui ont moins de 10 clients. Dans cet exemple, la mise en forme des cellules de l'équipe commerciale dépend du nombre de clients.

1. Sélectionnez la plage de cellules à mettre en forme (ici, les noms de A2:B45), puis allez dans Format > Mise en forme conditionnelle.

2. Sous "Règles de mise en forme", sélectionnez "La formule personnalisée est" dans le menu déroulant. Ajoutez la formule qui crée la bonne condition, sans oublier de faire référence à l'autre cellule. Dans cet exemple, ma formule sera =C2<10, écrite pour la première ligne de la plage. Comme vous pouvez le voir, la formule fait référence à la colonne "Number of clients".

3. Modifiez les préférences de "Style de mise en forme" et cliquez sur "OK".

4. Comme vous pouvez le voir, les noms des commerciaux qui ont moins de 10 clients sont maintenant mis en évidence en rouge.

Mettre en évidence une ligne entière selon une cellule

Pour colorer toute la ligne et pas seulement le nom, sélectionnez toutes les colonnes du tableau (par exemple, A2:D45) dans Appliquer à la plage et ajoutez un signe dollar devant la lettre de la colonne : =$C2<10. Le $ fait que chaque cellule de la ligne regarde la colonne C, si bien que chaque ligne devient rouge quand son propre nombre de clients est inférieur à 10.

Comment utiliser une formule personnalisée dans la mise en forme conditionnelle

Utiliser une formule personnalisée dans la mise en forme conditionnelle est vraiment utile. Elle vous permet de créer des règles plus personnalisées qui donnent exactement le résultat voulu, et elle sert aussi d'autres objectifs importants, comme vous le verrez plus loin dans cet article. Vous pouvez utiliser différentes fonctions, comme la formule IF (SI) pour la mise en forme conditionnelle, qui vous aident à atteindre des objectifs plus complexes. Voyons d'abord comment utiliser la formule personnalisée pour créer des règles de mise en forme.

Dans cet exemple, je veux mettre en évidence en orange toutes les ventes totales comprises entre 4 000 $ et 8 000 $. Une simple formule AND (ET) suffit pour y arriver rapidement.

1. Sélectionnez la plage de données à mettre en forme et allez dans Format > Mise en forme conditionnelle.

2. Dans le menu déroulant "Règles de mise en forme", sélectionnez "La formule personnalisée est". Dans le champ en dessous, saisissez la formule dont vous avez besoin. Ici, pour mettre en évidence les ventes totales entre 4 000 $ et 8 000 $, je dois ajouter la formule AND suivante : =AND(D1>4000,D1<8000).

3. Sous "Style de mise en forme", définissez la mise en forme des cellules qui respectent votre règle. Dans cet exemple, je choisis l'orange. Une fois terminé, cliquez sur "OK".

Comme vous pouvez le voir ci-dessous, toutes les ventes totales comprises entre 4 000 $ et 8 000 $ ont un fond de cellule orange.

Références relatives et absolues dans une formule personnalisée

Google Sheets lit une formule personnalisée comme si elle était écrite pour la première cellule de Appliquer à la plage, puis l'adapte à chacune des autres cellules, comme une formule change quand vous la recopiez vers le bas dans une colonne. C'est pourquoi les exemples de cette page utilisent =B2 ou =C2 et non une colonne entière.

Le signe dollar verrouille la partie d'une référence qui ne doit pas bouger :

• =$C2 fixe la colonne et laisse la ligne changer. Utilisez-la pour colorer des lignes entières.
• =C$2 fixe la ligne et laisse la colonne changer.
• =$C$2 fait pointer toutes les cellules vers la même cellule, par exemple une valeur cible que vous saisissez une seule fois.

Vous n'avez pas non plus besoin d'une fonction IF dans une formule personnalisée. La règle s'applique dès que la formule renvoie TRUE, donc =D2>5000 fait la même chose que =IF(D2>5000,TRUE,FALSE). Notre guide sur les fonctions IF dans Google Sheets présente les cas où IF est utile. Les formules personnalisées peuvent aussi signaler les entrées en double avec COUNTIF, comme le montre notre article sur comment mettre en évidence les doublons dans Google Sheets.

Comment utiliser la mise en forme conditionnelle avec plusieurs conditions

Il arrive que vous vouliez appliquer plusieurs conditions à vos données. La mise en forme conditionnelle de Google Sheets vous permet de créer et d'appliquer plusieurs conditions très facilement. Dans cet exemple, imaginons que je veuille mettre en évidence deux choses : le commercial qui a le plus de ventes totales et celui qui en a le moins. Vous pouvez ainsi comparer deux colonnes de données en même temps. Pour cela, il faut aussi une formule personnalisée pour chaque règle. Nous créons d'abord la condition pour le total des ventes maximal, puis une autre condition pour le total des ventes minimal.

1. Sélectionnez la plage de cellules à mettre en forme et allez dans Format > Mise en forme conditionnelle.

2. Créez votre première règle avec la formule personnalisée. Dans cet exemple, nous définissons la condition du total des ventes maximal avec la formule personnalisée =D:D=max(D:D) et passons la couleur au vert. Cliquez sur "OK".

3. Cliquez maintenant sur "+ Ajouter une autre règle".

4. Créez votre deuxième règle avec la formule personnalisée. Dans cet exemple, nous définissons la condition du total des ventes minimal avec la formule personnalisée =D:D=min(D:D) et passons la couleur au rouge. Cliquez sur "OK".

5. Comme vous pouvez le voir, après avoir appliqué plusieurs conditions, je sais maintenant que Melanie est en tête des ventes totales et qu'Ameline est en dernière position.

Quelle règle l'emporte quand des règles se chevauchent

Google Sheets vérifie les règles d'une plage de haut en bas, et la première règle vraie détermine l'aspect de la cellule. Si une cellule répond à deux règles, elle ne reçoit que la mise en forme de la règle placée le plus haut. Pour changer l'ordre, survolez une règle dans la barre latérale Règles de mise en forme conditionnelle et faites-la glisser vers le haut ou vers le bas à l'aide de la poignée située à sa gauche.

Si vous avez besoin des deux conditions dans une seule règle, combinez-les avec AND ou OR, comme dans l'exemple 4 ci-dessus.

Mise en forme conditionnelle basée sur une autre feuille

Une formule personnalisée ne peut pas pointer directement vers une cellule d'un autre onglet : une formule comme =D2<Targets!B1 ne fonctionnera donc pas. Placez plutôt la référence dans INDIRECT :

=D2<INDIRECT("Targets!B1")

Cette règle colore chaque valeur de ventes totales inférieure à l'objectif enregistré dans la cellule B1 de l'onglet Targets. Pour comparer chaque ligne à la ligne correspondante de l'autre onglet, construisez l'adresse avec ROW : =D2<INDIRECT("Targets!B"&ROW()). Pour en savoir plus sur cette fonction, consultez notre guide de la formule INDIRECT.

Les formules personnalisées ne peuvent pas non plus récupérer des valeurs d'un autre fichier. Si les valeurs de comparaison se trouvent dans une autre feuille de calcul, importez-les d'abord dans un onglet. Sheetgo, une plateforme d'automatisation des données dotée d'IA, peut garder cet onglet synchronisé avec le fichier source selon un planning.

Règles intégrées et échelles de couleurs dans Google Sheets

Il existe de nombreuses façons d'utiliser la mise en forme conditionnelle dans Google Sheets, selon le type de données ou ce que vous voulez faire.

Les exemples de cette section utilisent une liste de commerciaux avec leur nombre de clients et leurs ventes totales. Avec la mise en forme conditionnelle, je peux mettre en évidence des valeurs clés, comme les ventes ou le nombre de clients les plus élevés ou les plus faibles, les ventes totales comprises dans une certaine plage, ou même repérer les doublons. Voyons d'abord, étape par étape, la façon la plus simple d'utiliser la mise en forme conditionnelle dans Google Sheets.

La mise en forme conditionnelle de Google Sheets propose deux options : "Couleur unique" et "Échelle de couleurs". Voyons comment utiliser les deux.

Règles de couleur unique

Dans cet exemple, je veux mettre en évidence en rouge toutes les ventes totales inférieures à 5 000 $.

1. Ouvrez le fichier Google Sheets qui contient vos données. Sélectionnez les cellules auxquelles appliquer la règle de mise en forme conditionnelle (par exemple, 'D1:D45') et allez dans Format > Mise en forme conditionnelle.

2. Le menu s'affiche sur la droite de l'écran. Vérifiez que vous êtes sur l'onglet "Couleur unique". Comme vous pouvez le voir, la plage de cellules est déjà définie.

3. Définissez la règle de mise en forme. Dans le menu déroulant "Formater les cellules si", choisissez la condition qui doit déclencher la règle. Par exemple, pour mettre en évidence toutes les ventes totales inférieures à 5 000 $, je sélectionne l'option "Inférieur à". Dans le champ "Valeur ou formule" en dessous, je saisis '5000'.

4. Sous "Style de mise en forme", définissez la mise en forme des cellules qui respectent votre règle. Dans cet exemple, nous mettons la couleur de fond de la cellule en rouge. Une fois terminé, cliquez sur "OK".

5. Les règles de mise en forme conditionnelle s'appliquent à vos données. Comme vous pouvez le voir, toutes les ventes totales inférieures à 5 000 $ sont maintenant mises en évidence en rouge.

Les mêmes étapes vous permettent de créer des conditions pour les valeurs supérieures. Il suffit de passer le menu déroulant "Formater les cellules si" sur "Supérieur à" et d'ajouter votre valeur.

Le texte contient et autres conditions intégrées

Les règles de couleur unique ne se limitent pas aux nombres. Le menu déroulant Formater les cellules si vérifie aussi le texte et les dates :

• Est vide et N'est pas vide
• Le texte contient, Le texte ne contient pas, Le texte commence par, Le texte se termine par et Le texte est exactement
• La date est, La date est antérieure au et La date est postérieure au
• Supérieur à, Inférieur à, Est égal à, Est compris entre et leurs variantes
• La formule personnalisée est, pour tout ce que la liste ne couvre pas

Par exemple, pour signaler toutes les commandes en retard, sélectionnez la colonne de statut, choisissez Le texte contient, saisissez Late et choisissez une couleur. Toute cellule qui contient ce mot change de couleur, même si elle contient aussi un autre texte.

Échelles de couleurs

Imaginons maintenant que je veuille appliquer une échelle de couleurs à la colonne 'Total sales'. Avec cette option, le fond de chaque cellule prend une couleur ou une nuance différente selon sa valeur. Dans cet exemple, je veux les ventes totales les plus faibles en rouge et les plus élevées en vert.

1. Comme dans la section précédente, sélectionnez la plage de cellules et ouvrez la mise en forme conditionnelle. Cette fois, vérifiez que l'onglet "Échelle de couleurs" est sélectionné.

2. Dans la section "Aperçu", cliquez sur l'échelle de couleurs et choisissez la palette que vous préférez. Pour mon objectif, je choisis l'échelle du rouge au vert.

3. Choisissez ensuite les valeurs et les couleurs du point minimum, du point médian et du point maximum. Dans le menu déroulant de chaque section, choisissez la valeur sur laquelle baser les couleurs. Pour modifier les couleurs, cliquez sur les icônes de pot de peinture et sélectionnez la couleur de votre choix. Une fois terminé, cliquez sur "OK".

4. Les règles de mise en forme conditionnelle s'appliquent à vos données. Comme vous pouvez le voir, les ventes totales les plus faibles sont en rouge et les plus élevées en vert.

Comment modifier, copier ou supprimer des règles de mise en forme conditionnelle

Pour modifier une règle, sélectionnez une cellule de sa plage, allez dans Format > Mise en forme conditionnelle, et cliquez sur la règle dans la barre latérale. Modifiez la plage, la condition ou le style, puis cliquez sur Terminé.

Pour copier des règles vers une autre plage, copiez une cellule mise en forme, sélectionnez la nouvelle plage et utilisez Édition > Collage spécial > Mise en forme conditionnelle uniquement. Les références relatives d'une formule personnalisée s'adaptent au nouvel emplacement, tandis que les références avec un $ restent fixes.

Pour supprimer une règle, survolez-la dans la barre latérale et cliquez sur l'icône de corbeille.

Félicitations ! Vous avez réussi à utiliser la mise en forme conditionnelle pour fournir des repères visuels qui facilitent grandement le processus d'identification des données !

Pour en savoir plus sur les couleurs alternées, consultez l'article de blog suivant : Comment créer une liste de couleurs alternées dans Google Sheets ?

Vous travaillez dans Excel ? Les formules personnalisées y fonctionnent de la même façon. Ce guide sur la mise en forme conditionnelle dans Excel vous explique comment.