colors

Formats conditionnels : 6 bonnes pratiques

04/11/2016

Les formats conditionnels sont une des plus belles fonctionnalités d’Excel : ils donnent vie et couleur à vos analyses de manière dynamique. Mais quand ils sont mal utilisés ils peuvent se transformer en casse-tête… Vous avez tous fait l’expérience de recevoir un fichier Excel truffé de formats conditionnels : pour tenter d’y voir plus clair vous ouvrez le gestionnaire et découvrez des dizaines de règles quasi indéchiffrables…

Pour éviter ce genre de situations, nous avons souhaité partager avec vous les bonnes pratiques que nous utilisons chez Finance 3.1.

 

1. Pas de valeurs en dur

Le grand avantage des formats conditionnels est d’être dynamique. Si on met des valeurs en dur dans les règles de format conditionnel, on perd cet avantage.

Dans le tableau ci-dessous, imaginons qu’on souhaite surligner les lignes qui ont une croissance supérieure à 5%. On stocke donc la valeur 5% dans une cellule qu’on nomme CroissanceCible, et on applique la règle =$K4 > CroissanceCible à la plage $B$4:$K$13.

capture4qar

2. Attention aux dollars !

Des signes $ mal placés sont une source classique d’erreur dans les formats conditionnels. Il faut donc être prudent au moment de rédiger la formule. Et pour ceux qui se demandent régulièrement s’il faut placer le dollar avant ou après la lettre, voici un moyen mnémotechnique : un dollar placé aVant signifie que la formule sera tirée à la Verticale !

 

3. Privilégiez les booléens

Pour rappel, on nomme « booléens » les variables qui prennent la valeur 1 si une condition est respectée, 0 sinon. Les formules booléennes sont appréciées dans les règles de formats conditionnels complexes car elles permettent d’éviter de multiples imbrications de fonctions ET et OU, parfois peu lisibles.

Dans notre tableau, si nous souhaitons colorer les lignes qui ont une croissance supérieure à CroissanceCible, avec des ventes en 2015 inférieures à ValeurCible, nous pouvons utiliser la formule =et($K4>CroissanceCible ;$F4<ValeurCible) ou bien son équivalent booléen =($K4>CroissanceCible)*($F4<ValeurCible)

 

4. Anticipez le copier-coller

Lorsqu’on copie-colle une plage de cellules, Excel duplique également les formats conditionnels, ce qui peut conduire à une accumulation de formats conditionnels parfois erronés. Il faut donc être prudent au moment de copier-coller des données : regarder si des formats conditionnels ont été dupliqués, et s’ils ne sont pas souhaitables les supprimer.

Dans certains cas (par exemple lorsqu’on travaille sur une base de données), on peut prévenir ce problème en appliquant les formats conditionnels non pas à une plage de cellules mais à des colonnes entières. Cela n’alourdit pas le fichier et évite que les formats conditionnels soient dupliqués. Dans notre exemple la règle devient :

capture2qar

 

5. Triez vos formats conditionnels

En appliquant les conseils ci-dessus, vous pourrez dans la plupart des cas simplifier et réduire le nombre de vos formats conditionnels. Néanmoins il est parfois inévitable d’avoir un nombre important de formats conditionnels.

Dans ce cas pensez à utiliser les flèches du gestionnaire pour trier vos formats conditionnels selon un ordre logique. Et n’oubliez pas que si plusieurs règles entrent en conflit, Excel appliquera ces règles successivement selon leur ordre d’apparition dans la liste.

 

6. Osez la colonne intermédiaire ..!

Pour la création de formats conditionnels particulièrement complexes, il peut être judicieux de passer par une colonne intermédiaire ou colonne d’aide (« helper column » en anglais). L’idée est de transférer la complexité des formules depuis le gestionnaire de formats conditionnels vers Excel, en partant du principe qu’une formule est plus facile à inspecter dans Excel que dans le gestionnaire.

Par exemple dans notre outil de pronostics Euro Predictor 2016, nous devions colorer de manière dynamique les groupes de qualification en tenant compte de nombreux paramètres, d’où risque de confusion. Nous avons donc stocké dans des colonnes masquées des formules qui prenaient comme valeur la couleur souhaitée :

capture3qar

Les formats conditionnels étaient ensuite basés sur ces colonnes, ce qui permettait de s’y retrouver facilement :

capture1qar