Google Sheets offre de nombreuses fonctionnalités avancées qui aident à extraire du sens d’un ensemble de données. L’une d’entre elles, simple et puissante à la fois, est la mise en forme conditionnelle. Elle permet de transformer des lignes et des colonnes de texte noir sur fond blanc en un ensemble de données coloré et attrayant visuellement. Cela permet de gagner du temps et rend les données plus lisibles et plus significatives.
Dans cet article, nous vous expliquerons en quoi consiste la mise en forme conditionnelle dans Google Sheets et comment vous pouvez en tirer profit. Si vous préférez regarder plutôt que lire, consultez notre tutoriel vidéo.
Qu’est-ce que la mise en forme conditionnelle dans Google Sheets ?
La mise en forme conditionnelle dans Google Sheets est une fonctionnalité qui permet de changer automatiquement les propriétés de la police d’une cellule spécifique, d’une ligne, d’une colonne, voire même de la couleur de fond de la cellule, en fonction des règles que vous définissez. En d’autres termes, cet outil utilise le pouvoir de la visualisation pour faire ressortir vos données. En colorant les cellules, vous mettez en évidence des valeurs spécifiques, ce qui les rend plus faciles à visualiser et plus faciles à comprendre dans des tableaux complexes. Avec la mise en forme conditionnelle, les données deviennent plus attrayantes visuellement et donc plus lisibles par l’homme.
Quand peut-on appliquer la mise en forme conditionnelle dans Google Sheets ?
La mise en forme conditionnelle peut être utilisée dans pratiquement n’importe quel flux de travail pour visualiser des informations : des motifs de données, des zones problématiques, des bonnes nouvelles, voire même des données erronées ou défectueuses. Il est possible qu’aucun autre outil de Google Sheets ne puisse être utilisé dans autant d’applications.
- Les responsables des ventes ont besoin de tirer rapidement des informations d’un grand nombre de données de vente => ils utilisent la mise en forme conditionnelle.
- Les comptables veulent mettre en évidence les valeurs négatives en rouge dans leurs calculs de profit/perte => pas de problème, ils appliquent la mise en forme conditionnelle.
- Les chefs de projet essaient de comprendre l’utilisation de leurs ressources => encore une fois, la mise en forme conditionnelle peut venir à la rescousse.
Elle sert également de moyen efficace pour suivre les objectifs, en donnant des indications visuelles sur la progression par rapport à des métriques spécifiques. Et cela sans parler des rapports quotidiens et hebdomadaires, qui sont abondants dans les grandes organisations. Ils peuvent devenir une vraie corvée si vous êtes occupé et s’ils ne sont pas mis en forme conditionnellement, donc pas significatifs au premier coup d’œil.
Ainsi, la mise en forme conditionnelle peut être utile chaque fois que vous souhaitez donner vie à vos tableaux Google Sheets.
Comment utiliser la mise en forme conditionnelle dans Google Sheets
Avant d’entrer dans les détails, il est important de comprendre que tout travail avec la mise en forme conditionnelle suit le même schéma, qui comporte trois éléments clés :
- Intervalle. Il s’agit d’une cellule ou de cellules auxquelles la règle doit être appliquée.
- Déclencheur. Il définit la condition pour que la règle soit appliquée. Par exemple, le déclencheur peut être « Inférieur à ».
- Style. Lorsque la règle est appliquée, elle change le style de la cellule selon vos choix.
Les étapes à suivre sont les suivantes :
- Cliquez sur « Format » -> « Mise en forme conditionnelle ».
- Sélectionnez un intervalle.
- Définissez les règles de mise en forme conditionnelle :
- Sélectionnez votre style dans le menu déroulant « Style de mise en forme ».
- Sélectionnez votre déclencheur dans la liste déroulante « Formater les cellules si… ».
- Cliquez sur « Terminé » chaque fois que vous souhaitez appliquer vos règles :).
Voyons chaque étape plus en détail. Pour cela, nous utiliserons un ensemble de données qui a été importé depuis Airtable vers Google Sheets avec l’aide de Coupler.io.
Coupler.io est une solution d’intégration de données permettant d’automatiser les exportations de données depuis différentes applications telles que Pipedrive, Jira, HubSpot et bien d’autres vers Google Sheets. En outre, vous pouvez utiliser l’intégration de Google Sheets par Coupler.io pour combiner des données provenant de plusieurs feuilles en une seule sans utiliser de formules.
Découvrez la liste des intégrations de Google Sheets disponibles.
Étape 1. Cliquez sur « Format » -> « Mise en forme conditionnelle »
La première étape est assez simple et logique.
Étape 2. Sélectionnez un intervalle
En ce qui concerne la deuxième étape, vous avez deux options ici :
- Option 1 : Sélectionnez un intervalle (cellules, colonnes ou lignes) puis cliquez sur « Format » -> « Mise en forme conditionnelle ». Cela fera apparaître une barre d’outils de mise en forme conditionnelle sur le côté droit de votre écran. Si votre plage de données est petite, c’est la meilleure solution.
- Option 2 : Cliquez sur « Format » -> « Mise en forme conditionnelle » puis saisissez votre intervalle dans l’onglet « Appliquer à la plage ». C’est particulièrement pratique lorsque vous travaillez avec de grandes quantités de données et qu’il y a un risque de faire des erreurs lors de la sélection d’une plage.
Si vous ciblez une plage de cellules, vous devez entrer la première et la dernière cellule de la plage et les séparer par un deux-points, comme G2:G21 dans l’exemple ci-dessus. Si vous souhaitez mettre en évidence une seule cellule, saisissez l’étiquette de la cellule au lieu de la plage (par exemple, G2).
Vous devrez peut-être également travailler avec plusieurs plages. La façon la plus simple de le faire est de cliquer sur l’icône à droite du champ de plage et de sélectionner « Ajouter une autre plage ».
Par exemple, j’ai choisi la plage H2:H21 pour être colorée.
De cette façon, vous pouvez mettre en évidence autant de plages que vous le souhaitez.
Étape 3. Définissez les règles de mise en forme conditionnelle dans Google Sheets
Style de mise en forme
La prochaine chose à faire est de sélectionner le style. Il peut sembler contre-intuitif de sauter « Formater les cellules si… » et de commencer par le style en premier, mais le moment où vous définissez la condition, vos cellules changeront de style. C’est pourquoi il semble plus pratique d’inverser l’ordre, même si le résultat final est le même.
Pour sélectionner un style, cliquez sur « Par défaut » dans le menu déroulant « Style de mise en forme », et vous verrez les options de style par défaut.
Si aucune de ces options ne vous convient, créez un style personnalisé. Vous pouvez choisir le style du texte en gras, en italique, souligné ou barré, et vous pouvez sélectionner la couleur de la police et la couleur de fond de la cellule en cliquant sur le bouton de sélection de couleur.
Formater les cellules si… – sélectionnez votre déclencheur dans la liste déroulante
Regardons comment fonctionne la partie conditionnelle de la mise en forme pour chaque cas. Le déclencheur (« Formater les cellules si… ») est défini par défaut sur « N’est pas vide », mais si vous cliquez dessus, vous verrez une liste de nombreuses règles que vous pouvez appliquer à la mise en forme conditionnelle dans Google Sheets.
Pour notre premier exemple, sélectionnons « Supérieur à » dans les options de la liste déroulante, et appliquons la règle « Supérieur à 10 » à la plage G2:G21, la colonne des prix des produits.
Utilisons également des couleurs pour rendre les chiffres encore plus explicites et significatifs. Pour cela, nous appliquerons deux règles distinctes :
- Mettre en vert toutes les valeurs supérieures à 10.
- Mettre en rouge toutes les valeurs inférieures ou égales à 10.
Et oui, n’oubliez pas de cliquer sur « Terminé » chaque fois que vous voulez que vos règles soient appliquées :).
Échelle de couleurs de la mise en forme conditionnelle dans Google Sheets
Une autre bonne chose à propos de la coloration de vos cellules est que vous pouvez utiliser une échelle de couleurs pour rendre vos données encore plus significatives et attrayantes visuellement.
Par défaut, Google Sheets utilisera l’échelle de couleurs entre les valeurs minimale et maximale, mais vous pouvez modifier l’apparence de votre échelle en définissant des nombres maximum et minimum personnalisés. Par exemple, vous voudrez peut-être avoir quelque chose comme cela.
Mise en forme conditionnelle personnalisée dans Google Sheets en utilisant « La formule personnalisée est »
« La formule personnalisée est » est l’une des options les plus utiles offertes par « Formater les cellules si… » Elle vous permet d’être plus flexible avec vos données et d’élargir la portée des manipulations disponibles.
Pour y accéder, vous devez sélectionner « Est » dans le menu déroulant « Règles de mise en forme », puis faire défiler vers le bas et choisir « La formule personnalisée est… »
Sachez que toutes les formules personnalisées commencent par un signe égal (=). De plus, si vous avez l’habitude d’utiliser les fonctions ET et OU dans vos formules Google Sheets habituelles, bonne nouvelle, elles fonctionnent également dans la mise en forme conditionnelle.
Exemples d’autres règles de mise en forme conditionnelle dans Google Sheets
Prenons une longueur d’avance et examinons d’autres cas plus complexes et plus fréquemment utilisés dans la vie réelle.
Règle de mise en forme conditionnelle dans Google Sheets – le texte contient
Vous voudrez souvent trouver ou mettre en évidence un mot ou des mots dans vos ensembles de données. Il est facile à faire avec une règle basée sur le texte – une cellule changera en fonction du texte que vous saisissez. Les options pour une règle basée sur le texte sont :
- Le texte contient
- Le texte ne contient pas
- Le texte commence par
- Le texte se termine par
- Le texte est exactement
Par exemple, supposons que vous souhaitiez voir quels types de sandwiches sont proposés au menu. Dans ce cas, vous pouvez sélectionner l’ensemble du tableau (comme dans mon cas) de votre colonne de produits et choisir « Le texte contient » et saisir « sandwich ». Tous les types de sandwiches seront mis en évidence.
Mise en forme conditionnelle dans Google Sheets basée sur une autre valeur de cellule
Parfois, vous voudrez également appliquer une mise en forme conditionnelle en fonction d’une autre cellule. Supposons que vous souhaitiez savoir quels clients étaient satisfaits des produits. C’est là que l’option de formule personnalisée intervient. La formule à appliquer est :
=L2=TRUEAinsi, chaque fois que la mise en forme conditionnelle vérifie une cellule dans la plage C2:C21, elle vérifie la condition =L2=TRUE, où L est la colonne avec les valeurs qui montrent la satisfaction des clients.
La même logique peut être utilisée si vous avez besoin de savoir qui achète les produits les plus chers, dans notre cas 13 $. La formule pour cela est :
=G2>12Si vous avez besoin de rendre votre sélection encore plus ciblée, vous pouvez utiliser les fonctions ET ou OU. Par exemple, la formule suivante mettra en évidence les clients qui achètent les produits les plus chers et qui sont entièrement satisfaits.
=ET(L2=TRUE, G2>12)Comment mettre en forme conditionnellement une ligne entière dans Google Sheets
Mais que faire si vous voulez mettre en évidence toute une ligne, en fonction d’une valeur de cellule ? Supposons que vous vouliez mettre en évidence la ligne entière en fonction d’une valeur de cellule. Disons que vous voulez vérifier ce que votre client le plus pointilleux, Federico Coria, achète. Encore une fois, choisissez « La formule personnalisée est… » dans le menu déroulant et tapez :
=$C2="Federico Coria"Toutes les lignes qui contiennent Federico Coria dans la colonne seront mises en évidence.
Il suffit de vous rappeler de sélectionner l’ensemble de vos données, dans notre cas, A2:L21.
Presque la même chose doit être faite si vous voulez mettre en évidence les lignes qui n’incluent pas une valeur donnée. Remplacez = par <> . Dans notre cas, la nouvelle formule ressemblera à ceci :
=$C2<>"Federico Coria"Mettre en évidence les lignes alternées à l’aide de la mise en forme conditionnelle dans Google Sheets
Les lignes zebrées sont souvent utilisées lorsque les rapports doivent être imprimés. Cela permet d’améliorer la lisibilité et lui donne un aspect plus professionnel.
Pour mettre en évidence des cellules alternées, suivez l’algorithme déjà connu. N’oubliez pas de sélectionner l’ensemble des données et, dans le menu déroulant « Formater les cellules si… », sélectionnez l’option « La formule personnalisée est ». La formule à entrer est :
=MOD(LIGNE();2)=1Cela mettra en évidence les lignes alternées dans la plage sélectionnée.
Comment cela fonctionne : La fonction LIGNE() renvoie le numéro de ligne d’une cellule donnée. La fonction MOD() le divise ensuite par 2 et retourne le reste. Lorsque le reste est 1 (s’il y a un nombre impair de lignes), la condition est VRAIE et la mise en forme conditionnelle est appliquée.
Si vous voulez mettre en évidence un nombre pair de lignes, utilisez :
=MOD(LIGNE();2)=0Mise en forme conditionnelle pour les doublons dans Google Sheets
Parfois, vous pouvez avoir besoin de voir les doublons dans votre feuille Google – par exemple, pour pouvoir les supprimer. Avec la fonctionnalité de mise en forme conditionnelle, vous ne pouvez pas réellement supprimer les doublons, mais vous pouvez les identifier et les mettre en évidence. Pour cela, vous devez utiliser la formule personnalisée est comme règle de mise en forme et appliquer une formule COUNTIF à une plage de données sélectionnée. Par exemple, pour identifier les doublons dans la colonne G1:G, la formule suivante fera l’affaire :
=COUNTIF($G$1:$G1,G1)>1Mise en forme conditionnelle pour les dates
Voici un exemple de mise en forme conditionnelle basée sur les dates :
=DATEVALUE(K2)<DATEVALUE("2022-01-01")Nous avons mis en évidence les cellules qui contiennent des valeurs antérieures à une date exacte spécifiée.
Mise en forme conditionnelle basée sur l’heure dans Google Sheets
Vous voudrez peut-être également mettre en forme conditionnellement votre feuille en fonction de l’heure. C’est facile à faire si vous avez l’heure dans une colonne distincte, comme dans la colonne J de la feuille de démonstration. Ensuite, vous pouvez choisir n’importe quelle condition qui s’applique aux nombres. Par exemple, si vous souhaitez mettre en évidence les cellules comprises entre 15:00:00 et 20:00:00, vous devez choisir « Est compris entre » dans le menu déroulant et saisir vos valeurs.
Si votre heure n’est pas dans une colonne distincte, mais, disons, combinée avec la date, les choses ne seront pas plus faciles. Dans ce cas, vous devez choisir « La formule personnalisée est » et saisir la formule suivante :
=et(K2-ent(K2)>=heure(15;0;0);K2-ent(K2)<=heure(20;0;0))Mise en forme conditionnelle avec plusieurs règles dans Google Sheets
Comme nous l’avons déjà mentionné, il est possible de définir plusieurs règles de mise en forme conditionnelle dans Google Sheets. Rappelez-vous d’utiliser le bouton « Ajouter une autre règle » en dessous de votre règle de mise en forme conditionnelle. Ci-dessous, vous pouvez voir à quoi cela ressemble en pratique.
Comment mettre en évidence la valeur la plus basse ou la plus élevée à l’aide de la mise en forme conditionnelle
Il peut également être utile de savoir comment mettre en évidence des valeurs spécifiques dans une feuille de calcul. Regardons comment vous pouvez identifier les valeurs les plus basses et les plus élevées. Vous devez suivre le même algorithme, déjà familier, et dans le menu déroulant « Formater les cellules si… », sélectionnez l’option « La formule personnalisée est ». La formule à utiliser pour la valeur la plus élevée est :
=G:G=max(G:G)G représente la colonne dans laquelle vous souhaitez rechercher la valeur la plus élevée.
Pour trouver la valeur la plus basse, la logique est la même. La formule à saisir sera :
=G:G=min(G:G)Mise en forme conditionnelle avec une case à cocher dans Google Sheets
La case à cocher et la mise en forme conditionnelle sont une autre fonctionnalité intéressante de Google Sheets. Si la case à cocher est cochée, toute la ligne change de couleur ou de style de police. Voyons comment l’appliquer en pratique.
Tout d’abord, insérez une case à cocher depuis l’onglet « Insertion ». Pour cela, nous avons créé une colonne distincte, H.
Ensuite, sélectionnez la plage qui doit être mise en forme conditionnellement. Dans notre cas, c’est l’ensemble des données (A2:L21). Et pour une meilleure visualisation, nous choisissons une couleur différente et un style de police barré lorsque la case à cocher est cochée. La dernière chose à faire est de sélectionner « La formule personnalisée est » et de taper :
=$H2=TRUEMaintenant, chaque fois que vous cochez votre case, la règle sera appliquée.
Mise en forme conditionnelle dans Google Sheets entre différentes feuilles
Eh bien, ce serait génial si vous pouviez appliquer la mise en forme conditionnelle sur différentes feuilles d’onglets. Par exemple, si vous mettez en évidence le produit dans un onglet de feuille, cela s’appliquerait automatiquement à tous les onglets de feuille sans avoir besoin de modifications manuelles.
Malheureusement, les règles de format conditionnel ne s’appliquent qu’à l’onglet dans lequel elles sont saisies. Il est donc impossible de les importer pour plusieurs onglets, sans parler de différentes feuilles de calcul.
La mise en forme conditionnelle de Google Sheets ne fonctionne pas
Si vous êtes également un fan de la mise en forme conditionnelle comme nous le sommes, et que vous l’appliquez souvent, ou que vous commencez tout juste à découvrir cet outil utile, vous pouvez parfois rencontrer des situations où la règle de mise en forme conditionnelle ne fonctionne pas. Nous avons identifié les quatre principales raisons pour lesquelles cela peut se produire :
- L’ordre de votre mise en forme conditionnelle est important. La mise en forme conditionnelle n’applique que la première règle (de haut en bas) qui renvoie VRAI.
Tout autre résultat VRAI ne sera pas calculé. Vous pouvez changer l’ordre en inversant les règles. Pour ce faire, vous devez faire glisser la boîte en utilisant les trois points qui apparaissent à gauche.
- La mise en forme conditionnelle se comporte légèrement différemment des autres formules de Google Sheets. Par exemple, si dans la formule personnalisée vous entrez =G2<10 et l’appliquez à la plage G2:I21, dans la cellule G2, elle l’évaluera exactement comme écrit, G2<10. Mais dans les cellules suivantes, elle l’évaluera comme =G3<10, =G4<10, etc., ce qui signifie que la mise en forme conditionnelle ajustera la formule pour vous.
Si vous ne voulez pas d’ajustements, vous devez utiliser le signe $. Si vous utilisez $G$2, il conservera à la fois la ligne et la colonne constantes dans la plage. G$2 conservera uniquement la ligne constante et $G2 conservera uniquement la colonne constante. Pour mettre en évidence une ligne en fonction d’une seule colonne, vous devez utiliser l’option $G2.
-
Contrairement aux formules standard de Google Sheets, la parenthèse fermante ne sera pas insérée automatiquement dans la mise en forme conditionnelle. Vous devez faire attention à cela et les insérer manuellement vous-même. Dans les formules complexes, il est souvent utile de tester la formule dans une cellule vide pour vous assurer que votre syntaxe est correcte.
-
La mise en forme conditionnelle, comme nous l’avons mentionné précédemment, ne peut pas être appliquée à d’autres onglets de votre feuille directement. Vous devez utiliser la fonction INDIRECT pour référencer un onglet différent dans votre feuille.
La formule personnalisée est incorrecte dans la mise en forme conditionnelle de Google Sheets
Lorsque vous utilisez « La formule personnalisée est… » dans le cadre de la mise en forme conditionnelle, vous devez également faire attention lorsque vous saisissez votre formule. Bien sûr, cela ne fonctionnera pas. De plus, cela mettra en évidence la formule en rouge, comme indiqué ci-dessous.
Comment supprimer la mise en forme conditionnelle dans Google Sheets
Dans certains cas, vous voudrez peut-être supprimer entièrement la mise en forme conditionnelle dans Google Sheets. Cela peut être le cas lorsque vous obtenez les données de quelqu’un d’autre ou que vous voulez commencer à travailler avec votre feuille de calcul à nouveau.
Pour supprimer la mise en forme conditionnelle, sélectionnez d’abord la plage de cellules avec la mise en forme conditionnelle, puis allez dans « Format » -> « Mise en forme conditionnelle » et vous verrez toutes les règles que vous avez créées dans la barre latérale. Cliquez sur l’icône de la « poubelle » de la règle que vous souhaitez supprimer. La mise en forme conditionnelle sera supprimée.
Récapitulatif et continuez à apprendre la mise en forme conditionnelle avancée dans Google Sheets
Peu importe le type de projet sur lequel vous travaillez ou les données avec lesquelles vous travaillez, une feuille de calcul peut être l’outil parfait pour jouer avec vos données et en tirer du sens. Avec la mise en forme conditionnelle dans Google Sheets, vous pouvez rendre vos données visuellement attrayantes et leur donner du sens afin de rendre vos rapports intuitifs et conviviaux pour les lecteurs. Alors, continuez à apprendre et à appliquer cette excellente fonction, et vous trouverez probablement des dizaines de cas d’utilisation pour la mise en forme conditionnelle.
