La mise en forme conditionnelle fait partie de ces fonctionnalités d’Excel que l’on utilise tous les jours une fois qu’on les a découvertes. Elle permet aux cellules de changer de couleur, de police ou d’afficher des icônes automatiquement selon leur contenu. Elle ne sert pas seulement à embellir un rapport : bien utilisée, c’est un système d’alerte qui vous signale les erreurs, les doublons, les valeurs suspectes et les changements entre versions avant qu’ils ne deviennent un problème. Dans cet article, vous découvrirez ses applications les plus utiles pour contrôler la qualité de vos données.
Où la trouver et comment elle fonctionne
Sélectionnez une plage et allez dans Accueil > Mise en forme conditionnelle. Vous y trouverez des règles prédéfinies (mise en surbrillance des cellules, règles des valeurs plus/moins élevées, barres de données, nuances de couleurs, jeux d’icônes) et l’option Nouvelle règle, qui permet d’utiliser des formules pour les cas personnalisés.
Pour vérifier ou supprimer des règles, utilisez Mise en forme conditionnelle > Gérer les règles.
1. Mettre en évidence les doublons
Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double.
C’est idéal pour les colonnes qui devraient contenir des valeurs uniques : codes produit, numéros de facture, adresses e-mail des clients. Si une couleur apparaît, vérifiez ces enregistrements avant qu’ils ne posent problème. Pour supprimer ensuite les doublons en masse, utilisez Supprimer les doublons Excel.
2. Détecter les cellules obligatoires vides
Si une colonne ne devrait jamais être vide (par exemple le prix ou le numéro de TVA), créez une règle avec une formule :
- Sélectionnez la plage, par exemple C2:C500.
- Nouvelle règle > Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Saisissez
=ESTVIDE(C2)et choisissez un remplissage rouge clair.
Le moindre trou sautera aux yeux.
3. Valeurs hors plage
Pour détecter les erreurs de saisie, signalez les valeurs impossibles ou peu probables :
- Prix négatifs : Règles de mise en surbrillance des cellules > Inférieur à 0.
- Remises supérieures à 50 % : Supérieur à 0,5.
- Dates futures dans une colonne de date de facture : formule
=C2>AUJOURDHUI().
Adaptez les seuils à votre activité. Une remise de 60 % peut être correcte lors d’une liquidation, mais il vaut mieux que quelqu’un la vérifie.
4. Comparer deux colonnes
Pour voir sur quelles lignes deux colonnes diffèrent (par exemple, ancien prix et nouveau prix) :
- Sélectionnez la plage de la nouvelle colonne, D2:D500.
- Nouvelle règle avec formule :
=D2<>C2. - Choisissez une couleur.
Les cellules qui ont changé seront mises en évidence.
5. Trouver les valeurs présentes dans une liste mais pas dans l’autre
Si vous avez la liste des clients d’août dans la feuille « Août » et celle de septembre dans « Septembre », vous pouvez mettre en évidence les nouveaux clients de septembre :
=NB.SI(Août!$A:$A;A2)=0
Appliquez cette règle à la plage des codes de la feuille de septembre. Les cellules colorées correspondent aux clients qui n’étaient pas présents en août. En inversant les feuilles, vous verrez aussi ceux qui ont disparu.
Cette méthode fonctionne bien pour une seule colonne. Si vous devez comparer des tableaux complets avec plusieurs colonnes et feuilles, Comparer des fichiers Excel vous fournira un rapport complet sans créer de règles.
6. Espaces superflus
Les espaces invisibles sont une source constante d’erreurs. Cette règle les trahit :
=NBCAR(A2)<>NBCAR(SUPPRESPACE(A2))
Les cellules mises en évidence contiennent des espaces en trop au début, à la fin ou entre les mots.
7. Nombres stockés sous forme de texte
Pour trouver les montants qu’Excel traite comme du texte :
=ESTTEXTE(C2)
Appliquée à une colonne de montants, elle signale précisément les cellules qui poseront problème dans les sommes et les tableaux croisés dynamiques.
8. Nuances de couleurs pour voir les tendances
Les nuances de couleurs (du vert au rouge) appliquées à une colonne de variations de prix montrent d’un coup d’œil quels produits ont le plus augmenté. Les barres de données sont utiles pour comparer des ordres de grandeur, comme les ventes par commercial.
Bonnes pratiques
- N’abusez pas des couleurs. Si tout est mis en évidence, plus rien ne ressort. Utilisez une ou deux couleurs à la signification claire.
- Documentez les règles. Ajoutez une petite légende qui explique ce que signifie chaque couleur.
- Appliquez les règles à la plage exacte. Les appliquer à des colonnes entières ralentit le fichier et augmente sa taille.
- Vérifiez les règles en double. Lorsque vous copiez et collez des cellules, les règles se multiplient. Nettoyez-les depuis Gérer les règles.
- Utilisez les bonnes références. Dans les formules, la référence à la première cellule de la plage doit être relative (sans
$) pour s’ajuster à chaque ligne.
Questions fréquentes
La mise en forme conditionnelle fonctionne-t-elle dans LibreOffice et Google Sheets ?
Oui. Les deux programmes proposent la mise en forme conditionnelle avec des règles prédéfinies et des règles par formule. Certaines fonctions peuvent porter des noms différents.
La mise en forme conditionnelle est-elle conservée lors de la conversion en PDF ?
Oui. En convertissant avec Excel en PDF, les couleurs appliquées par les règles apparaissent dans le document, ce qui est utile pour partager des rapports avec des alertes visibles.
Combien de règles de mise en forme conditionnelle puis-je avoir ?
Excel n’impose pas de limite basse en pratique, mais des centaines de règles ralentissent le fichier. Si vous constatez une lenteur, ouvrez Gérer les règles et supprimez les règles en double ou celles appliquées à des plages inutilement grandes.
Puis-je utiliser la mise en forme conditionnelle pour comparer deux feuilles complètes ?
Oui, avec une règle comme =A1<>Feuil2!A1 appliquée à toute la plage. Cela fonctionne si les deux feuilles ont exactement la même structure et le même ordre. Si des lignes ont été insérées ou supprimées, un outil de comparaison donnera un résultat beaucoup plus fiable.
Comment supprimer toute la mise en forme conditionnelle d’une feuille ?
Allez dans Accueil > Mise en forme conditionnelle > Effacer les règles > Effacer les règles de la feuille entière. Les données ne sont pas modifiées, seules les couleurs automatiques disparaissent.
Conclusion
La mise en forme conditionnelle transforme vos feuilles de calcul en un système de contrôle qui signale les doublons, les trous, les valeurs impossibles, les espaces cachés et les changements entre listes. Configurez quelques règles dans vos fichiers les plus importants et vous repérerez les erreurs avant qu’elles n’atteignent un rapport. Pour des comparaisons complètes entre fichiers, complétez ces règles avec les outils gratuits de notre site.
Essayez nos outils gratuits
Comparez, convertissez, fusionnez et nettoyez vos feuilles de calcul depuis votre navigateur, sans rien installer.
