Vous suivez un budget dans Excel, vous calculez des écarts entre prévisions et dépenses réelles, et vous aimeriez que les cellules changent de couleur selon le résultat. La bonne nouvelle : la soustraction et la mise en forme conditionnelle se combinent très bien, à condition de comprendre comment Excel traite chaque étape. Voici la marche à suivre, du cas le plus simple au plus utile en contexte professionnel.
Soustraction dans Excel : la syntaxe à maîtriser avant tout
Excel n’a pas de fonction nommée « SOUSTRACTION ». L’opération repose sur l’opérateur moins, tout simplement. Pour obtenir l’écart entre deux cellules, la formule ressemble à ceci :
=A2-B2
Placée en C2, cette formule renvoie la différence entre la valeur de A2 et celle de B2. Si A2 contient 1 500 et B2 contient 1 200, le résultat affiché sera 300.
Vous pouvez aussi soustraire une valeur fixe : =A2-500. Ou combiner la soustraction avec d’autres opérations dans la même cellule : =A2-B2+D2.
L’idée à retenir : la soustraction produit un résultat numérique. C’est ce résultat que la mise en forme conditionnelle va ensuite évaluer pour décider de la couleur, de l’icône ou du style à appliquer.
Mise en forme conditionnelle sur un résultat de soustraction : méthode pas à pas
Imaginons une colonne C remplie de formules de soustraction (=A2-B2, =A3-B3, etc.). Vous voulez colorer en rouge les cellules dont le résultat est négatif, et en vert celles dont le résultat est positif ou nul.
Étapes dans Excel
- Sélectionnez la plage de cellules contenant vos résultats de soustraction (par exemple C2:C50).
- Allez dans l’onglet Accueil, puis cliquez sur « Mise en forme conditionnelle » et choisissez « Nouvelle règle ».
- Sélectionnez « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ».
- Dans le champ de la formule, tapez =C2<0 pour cibler les valeurs négatives. Définissez un remplissage rouge, puis validez.
- Répétez l’opération avec la formule =C2>=0 et un remplissage vert pour les écarts positifs ou nuls.
La règle s’applique à toute la plage sélectionnée. Excel ajuste automatiquement la référence de ligne pour chaque cellule (C2 devient C3, C4, etc.).
Formule de soustraction directement dans la règle conditionnelle
Vous n’avez pas toujours besoin d’une colonne intermédiaire pour stocker le résultat de la soustraction. La formule de soustraction peut être écrite dans la règle de mise en forme conditionnelle elle-même.
Reprenons l’exemple : colonne A pour les prévisions, colonne B pour les dépenses réelles. Vous voulez colorer les cellules de la colonne B en rouge quand la dépense dépasse la prévision.
Sélectionnez B2:B50. Créez une nouvelle règle avec la formule :
=B2-A2>0
Cette formule teste si la dépense réelle (B2) dépasse la prévision (A2). Si oui, la cellule passe en rouge. L’avantage : pas de colonne supplémentaire, le tableau reste compact.
Pourquoi les références relatives comptent
Quand vous écrivez =B2-A2>0 dans la règle, Excel utilise des références relatives. C’est ce qui permet à la règle de s’adapter ligne par ligne. Si vous verrouillez la référence avec un dollar (=$B$2-$A$2>0), la règle comparera toujours la même paire de cellules, ce qui n’a aucun intérêt sur une plage.
Vérifiez toujours que vos références sont bien relatives (sans $) dans le champ de formule de la règle. C’est la source d’erreur la plus fréquente.
Soustraction avec SUMIF et mise en forme conditionnelle : un cas concret
Vous avez déjà remarqué que certains écarts doivent se calculer sous conditions ? Par exemple, vous ne voulez soustraire que les montants d’une catégorie précise.
La formule SUMIF (ou SOMME.SI en français) permet de filtrer avant de soustraire. Dans une cellule, vous pouvez écrire :
=A2-SOMME.SI(B1:D1; »Validé »;B2:D2)
Cette formule soustrait de A2 uniquement la somme des colonnes dont l’en-tête contient « Validé ». Le résultat représente l’écart entre un montant global et les montants approuvés.
Ensuite, appliquez une mise en forme conditionnelle sur cette cellule : si le résultat dépasse un certain seuil, la cellule change de couleur. Ce principe de soustraction conditionnelle puis formatage visuel est très utile pour le suivi budgétaire.
Soustraction matricielle dans Microsoft 365 et mise en forme sur la plage résultat
Dans les versions récentes d’Excel (Microsoft 365 et 2021+), une formule matricielle dynamique permet de soustraire deux blocs de données d’un coup, sans copier la formule ligne par ligne.
Exemple : vous avez des prévisions en A2:C10 et des dépenses réelles en E2:G10. En tapant dans une seule cellule :
=(A2:C10)-(E2:G10)
Excel renvoie un tableau complet de différences qui se déverse automatiquement sur les cellules adjacentes. Pas besoin de Ctrl+Shift+Entrée dans ces versions : la formule se comporte nativement comme une formule matricielle.
Une fois ce bloc de résultats obtenu, sélectionnez la plage et appliquez une règle de mise en forme conditionnelle (par exemple, colorer en orange tout écart supérieur à un seuil défini). Le gain de temps est notable quand vous travaillez avec des tableaux de plusieurs colonnes.
Erreurs fréquentes à éviter avec cette combinaison
Quelques pièges reviennent régulièrement quand on mélange soustraction et mise en forme conditionnelle :
- Utiliser des références absolues ($A$2) dans la formule de la règle alors que la plage couvre plusieurs lignes. La règle ne s’adapte plus et toutes les cellules affichent le même format.
- Oublier le signe « = » au début de la formule dans la boîte de dialogue de la règle. Excel interprète alors le texte comme une chaîne, pas comme un calcul.
- Appliquer la règle sur une plage qui ne correspond pas aux cellules contenant la formule de soustraction. La mise en forme doit cibler exactement les cellules que vous voulez colorer.
- Empiler trop de règles sans vérifier leur ordre de priorité. Excel applique les règles de haut en bas : la première règle vraie l’emporte si l’option « Arrêter si vrai » est cochée.
La combinaison soustraction et mise en forme conditionnelle ne demande pas de formule complexe. L’opérateur moins, une référence relative bien placée et une règle claire suffisent à rendre un tableau lisible d’un coup d’oeil. Si vos données occupent plusieurs colonnes, la soustraction matricielle des versions récentes d’Excel simplifie encore le travail.
