Comment modifier une règle de mise en forme conditionnelle

Mode opératoire

1) Cliquer dans n’importe quelle cellule de la feuille sur laquelle vous souhaitez modifier une mise en forme conditionnelle existante.

2) Aller dans l’onglet « Accueil » et ouvrir le menu déroulant sous le bouton « Mise en forme conditionnelle ». Choisir « Gérer les règles ».

3) Dans la fenêtre qui s’ouvre, sélectionner la mise en forme conditionnelle à modifier et aller dans l’onglet « Modifier la règle... ». Faite les modifications voulue (changement de mise en forme, changement de formule, changement de périmètre...) et cliquer sur OK.

Cliquer sur les images pour les agrandir

À partir d’une condition propre à une cellule, comment changer le format de toute une ligne

Cas concret

Dans mon tableau, je souhaite mettre automatiquement en italique le format de toute une ligne lorsque l’action est clôturée.

Mode opératoire

1) Sélectionner les cellules qui seront concernées par la mise en forme conditionnelle.

2) Aller dans l’onglet « Accueil » et ouvrir le menu déroulant sous le bouton « Mise en forme conditionnelle ». Choisir « Nouvelle règle ».

Cliquer sur les images pour les agrandir

3) Dans la fenêtre qui s’ouvre, sélectionner le dernier type de règle « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ».

4) Renseigner la formule qui permettra d’activer la mise en forme conditionnelle. Ici on souhaite activer une mise en forme particulière uniquement lorsqu’il y a noté « OK » dans la colonne H « Relance ». La formule à rentrer sera donc =$H1= « OK » Précision 1 : on met un $ devant le H pour bien borner la formule à la colonne H. En revanche, il ne faut pas mettre de $ devant le 1 car on souhaite que la formule s’applique également aux cellules H2, H3, H4, etc. Précision 2 : on met OK entre guillemet pour qu’Excel reconnaisse qu’il s’agit du mot « OK ».
5) Une fois la formule renseignée, cliquer sur le bouton « Format... » et sélectionner la mise en forme désirée (italique, remplissage en rouge, etc.) et cliquer sur OK.

Comment faire une mise en forme conditionnelle

Cas concret

Vous souhaitez mettre en rouge les montants négatifs dans votre tableau, identifier les doublons ou colorer les cellules en fonction d’une zone géographique ou d’un nom, etc. les mises en forme conditionnelles sont faites pour vous.

Mode opératoire

1) Sélectionner les cellules qui seront concernées par la mise en forme conditionnelle.

2) Aller dans l’onglet « Accueil » et ouvrir le menu déroulant sous le bouton « Mise en forme conditionnelle ». Choisir la mise en forme conditionnelle que vous souhaitez appliquer.

Cliquer sur les images pour les agrandir

Voici les principales mises en forme conditionnelles possibles :

- Mettre en surbrillance les cellules supérieures à xxx
- Mettre en surbrillance les cellules inférieures à xxx
- Mettre en surbrillance les cellules dont le montant est compris entre xxx et xxx
- Mettre en surbrillance les cellules égales à xxx
- Mettre en surbrillance les cellules qui contiennent le mot xxx
- Mettre en surbrillance les cellules dont la date est comprise entre xxx et xxx
- Mettre en surbrillance les cellules qui ont des valeurs en double-clic
- Mettre en surbrillance les 10 cellules correspondant aux plus grands montants
- Mettre en surbrillance les 10 cellules correspondant aux plus petits montants
- Mettre en surbrillance les cellules dont le montant est supérieur à la moyenne
- Colorer une cellule proportionnellement au montant qu’elle représente (cf. image ci-dessous)
- Colorer les cellules par rapport au montant qu’elles représentent (cf. image ci-dessous)

En plus des mises en forme pré-programmées, vous pouvez créer votre propre mise en forme en sélectionnant « Nouvelle règle » dans le menu déroulant sous le bouton « Mise en forme conditionnelle ».

Trucs et astuces

Les mises en formes conditionnelles peuvent permettre de colorer une cellule. Vous pouvez ensuite faire des tris ou des filtres dans votre tableau en fonction des couleurs de cellules. Cela peut être très utile par exemple pour colorer les valeurs en double puis filtrer le tableau uniquement sur ces valeurs.

Pour effacer une mise en forme conditionnelle, il suffit de sélectionner « Effacer les règles » dans le menu déroulant sous le bouton « Mise en forme conditionnelle ».

Comment faire des filtres

Cas concret

Le filtre permet d’afficher à l’écran uniquement les lignes qui répondent à un critère bien précis. Par exemple, si on a un tableau avec une colonne qui contient l’information du nom des fournisseurs, il est possible de faire un filtre pour n’afficher à l’écran que les lignes propres à un fournisseur en particulier.

Mode opératoire

1) Sélectionner la ligne correspondant aux en-têtes de colonne.

2) Aller dans l’onglet « Données » et appuyer sur le bouton « Filtrer ».

Cliquer sur l'image pour l'agrandir

Une fois qu’il est possible de faire un filtre sur une colonne, un petit triangle apparaît dans l’en-tête de cette colonne. En appuyant sur ce triangle (3), vous pouvez faire différents filtres. Voici les principaux filtres utiles :

A) Faire un filtre en cochant une ou plusieurs valeurs présentes dans la colonne.

B) Faire un filtre à partir de la couleur des cellules si certaines cellules ont des couleurs correspondant à une légende particulière (cela peut-être très utiles lorsque l’on utilise des mises en forme conditionnelles pour identifier les doublons par exemple).

C) Faire un filtre à partir de conditions précises : filtrer les cellules dont le montant est supérieur à xxx ; filtrer les cellules qui contiennent le mot xxx ; filtrer les cellules commençant par xxx.

Il est également possible de combiner plusieurs conditions en sélectionnant « filtre personnalisé » (par ex. filtrer les cellules commençant par le mot xxx ET ne contenant pas le mot xxx).

Cliquer sur l'image pour l'agrandir

Trucs et astuces

Pour aller plus vite lorsque l’on a beaucoup de valeurs à cocher pour les inclure dans le filtre, il est possible de cocher « (sélectionner tout) » puis décocher les valeurs que l’on ne souhaite pas afficher à l’écran.

Comment conserver la mise en forme d’un tableau lors d’un copier/coller

Cas concret

Vous venez de faire un tableau avec une mise en forme particulière (couleur, taille des colonnes, police...) et vous souhaitez le copier/coller dans un autre fichier. Vous vous rendez-compte que certains éléments de la mise en forme, notamment la taille des colonnes n’a pas été conservée. Voici comment procéder.

Mode opératoire

1) Sélectionner votre tableau et copier le.

2) Dans l’onglet « Accueil », ouvrir le menu déroulant sous le mot « Coller » et sélectionner la 6ème icône « conserver les largeurs de colonnes sources ».

Cliquer sur l'image pour l'agrandir

Comment figer des volets

Cas concret

Lorsqu’un tableau est très grand, il peut être utile de figer la ligne d’en-tête de ses colonnes. De cette manière, lorsque vous ferez défiler les lignes de votre tableau, vous aurez toujours à l’écran les en-têtes de colonnes.

Mode opératoire

1) Sélectionner la ligne située juste en dessous de celle que l’on souhaite figer.

2) Aller dans l’onglet « Affichage », appuyer sur le bouton « Figer les volets » et sélectionner « Figer les volets ».
Il est également possible de figer une colonne en suivant le même procédé. Pour ne plus figer les volets, aller dans l’onglet « Affichage », appuyer sur le bouton « Figer les volets » et sélectionner « Libérer les volets ».

Cliquer sur l'image pour l'agrandir

Trucs et astuces

Il est également possible de figer en même temps une colonne + une ligne ? Pour cela il suffit de se mettre dans la cellule située au croisement entre la colonne et la ligne que l’on souhaite figer et aller dans l’onglet « Affichage », appuyer sur le bouton « Figer les volets » et sélectionner « Figer les volets ».

Dans l’exemple ci-dessous on souhaite figer en même temps la colonne A et la ligne 2 (en-tête des colonnes).

Cliquer sur l'image pour l'agrandir

Comment modifier plusieurs onglets simultanément

Mode opératoire

1) Sélectionner tous les onglets que vous souhaitez modifier simultanément (pour faire une sélection multiple, maintenir la touche Ctrl enfoncée lorsque vous sélectionnez les onglets avec la souris). A partir du moment où plusieurs onglets sont sélectionnés, toutes les modifications que vous allez faire sur l’onglet visible à l’écran se répercuteront sur tous les autres onglets sélectionnés (ex. ajout de colonnes ou de lignes, ajout de texte, coloration de certaines cellules, etc.).

Cliquer sur l'image pour l'agrandir

Trucs et astuces

Cette astuce est très utile si vous souhaitez mettre en forme des tableaux identiques d’un onglet à un autre. Il suffit de mettre en forme uniquement l’onglet qui est visible à votre écran et tous les autres dupliqueront automatiquement la mise en forme tant qu’ils seront sélectionnés.

Comment modifier la couleur d’un onglet

Mode opératoire

1) Faire un clic droit avec la souris sur l’onglet en question.

2) Dans le menu qui s’affiche, sélectionner le bouton « couleur d’onglet » et choisir une couleur.

Cliquer sur l'image pour l'agrandir

Comment enlever le quadrillage automatique d’une feuille Excel

Mode opératoire

1) Aller dans l’onglet « Mise en page ».

2) Dans la rubrique « Option de la feuille de calcul » et découcher la case « afficher » en dessous de « Quadrillage ».

Cliquer sur l'image pour l'agrandir

Identifier les cellules fusionnées dans un tableau

Cas concret

Lorsque l’on a un fichier de plusieurs milliers de lignes, il peut être très fastidieux de retrouver les cellules fusionnées. Voici un mode opératoire qui permet de les identifier automatiquement.

Mode opératoire

1) Sélectionner entièrement le tableau.

2) Aller dans l’onglet « Accueil » et appuyer sur le bouton « Rechercher et sélectionner » ou utiliser le raccourcis clavier Ctrl + R. Dans la fenêtre qui s’ouvre appuyer sur le bouton « option ».

3) 4) Dans les nouveaux menus qui s’affichent, cliquer sur « Format ».

5) Dans la fenêtre qui s’ouvre, aller dans l’onglet « Alignement » et cocher la case « fusionner les cellules ». Appuyer sur OK et ensuite sur « Rechercher tout » La liste de toutes les cellules fusionnées du tableau apparaît dans une nouvelle fenêtre.

Cliquer sur les images pour les agrandir

Comment mettre des cellules au format nombre automatiquement avec la fonction CNUM

Cas concret

Lorsque l’on importe des données dans Excel en effectuant des copier/coller à partir de différentes sources, il arrive que le format des cellules collées ne soit pas le format « nombre » d’Excel. Pour modifier cela en 30 secondes, il est possible d’utiliser la fonction CNUM.

Mode opératoire

1) Créer une nouvelle colonne dans votre tableau.

2) Dans la première cellule de votre nouvelle colonne, noter la formule suivante =CNUM(A1). Au lieu de A1, vous devez renseigner la référence de la cellule avec le nombre que vous voulez transformer en format nombre.

3) Copier/coller la formule dans toute votre colonne.

Trucs et astuces

Au lieu d’utiliser cette formule, il est également possible de modifier le format de la colonne initiale en la sélectionnant, en faisant un clic droit sur la sélection puis en choisissant le menu « format de cellule ». Sélectionner ensuite la catégorie « nombre » dans l’onglet « nombre ».

Cliquer sur les images pour les agrandir

Comment verrouiller certaines cellules d’un tableau

Cas concret

Vous souhaitez faire remplir un tableur par vos collaborateurs mais vous souhaitez que certaines cellules ne puissent pas être modifiées. Pour cela, il vous suffit de verrouiller votre fichier à l’aide d’un mot de passe.

Mode opératoire

1) Sélectionner l’ensemble des cellules du tableau qui ne doivent pas être verrouillées.

2) Faire un clic droit avec la souris dans cette sélection et ouvrir le menu « Format de cellule ».

3) Dans la fenêtre qui s’ouvre, allez dans l’onglet « Protection » et décocher la case « Verrouillée » puis appuyer sur OK.

4) Une fois l’ensemble des cellules souhaitées déverrouillées, aller dans l’onglet « Révision » puis (5) appuyer sur le bouton « Protéger la feuille ».
6) Dans la fenêtre qui s’ouvre, indiquer un mot de passe qui vous permettra ensuite de déverrouiller le tableau (mot de passe facultatif).
7) Toujours dans cette même fenêtre, sélectionner les cases qui vous intéresse (par défaut les deux premières sont toujours cochées). Par exemple, si vous souhaitez que vos collaborateurs puissent faire des filtres ou des tris dans le tableau, vous devez cocher les cases autorisant les filtres et les tris. Une fois les cases cochées, appuyez sur OK et confirmez le mot de passe si vous en avez mis un.

Cliquer sur les images pour les agrandir

Trucs et astuces
Vous pouvez verrouiller un tableau mais laisser accessible certaines cellules avec liste déroulante par exemple. Il suffit de suivre le même mode opératoire que celui indiqué ci-dessus.

Comment ajuster la zone d’impression

Mode opératoire

1) Sélectionner la zone du tableau que vous souhaitez imprimer.

2) Aller dans l’onglet « Mise en page ».

3) Cliquer sur le bouton « Zone d’impression » puis « Définir ».

Cliquer sur l'image pour l'agrandir

Vous venez de définir la zone qui devra être imprimée. Vous pouvez maintenant personnaliser les modalités d’impression grâce à différents boutons dont voici les principaux :

A) « Marges » : plusieurs marges sont proposées par défaut. Si ces choix ne vous conviennent pas, vous pouvez également choisir « Marges personnalisées ».
B) « Orientation » : vous pouvez choisir une impression en paysage ou en portrait.

C) « Taille » : vous pouvez sélectionner la taille de votre page (A3, A4...). Si aucune proposition par défaut vous convient, vous pouvez sélectionner « Autres tailles ».

D) « Largeur » + « Hauteur » : dans ces deux champs, vous pouvez indiquer sur combien de pages l’impression devra se faire. Attention, si votre tableau comprend beaucoup de lignes, il faudra probablement imprimer le tableau sur 2 ou 3 pages différentes. L’échelle située en dessous de ces deux boutons vous permet de voir si le nombre de pages que vous avez choisi rendra l’impression lisible. Par exemple, si vous souhaitez que votre tableau tienne sur 1 seule page (« largeur » = 1 page / « hauteur » = 1 page), vérifier que l’échelle indiquée ne soit pas en dessous de 60-65 %. Dans le cas contraire, prévoyez une loupe pour lire votre document imprimé !

E) « Imprimer les titres » : ce bouton peut-être intéressant si vous imprimez votre tableau en plusieurs pages. Il vous permet de faire apparaître les noms de colonnes en haut de chaque nouvelle page ou les noms de lignes à gauche de chaque nouvelle page si votre document est plus large que haut.

Pour cela, vous n’avez qu’à sélectionner la plage de cellule correspondant aux noms de colonnes ou aux noms de ligne (1).

Cliquer sur l'image pour l'agrandir

Trucs et astuces
Si votre document contient plusieurs onglets, vous pouvez définir des zones d’impressions différentes dans chaque onglet et les personnalisez comme indiqué ci-dessus. Au moment de lancer l’impression, vous avez plusieurs possibilités :

« Imprimer les feuilles actives » : n’imprime que la zone d’impression définie dans l’onglet sur lequel vous travaillez actuellement.

« Imprimer le classeur entier » : imprime tous les onglets de votre document. Si vous n’avez pas défini de zone d’impression sur certains onglets, l’ensemble du tableau sera imprimé par défaut (attention, cela peut faire des centaines de pages si vos tableaux sont très longs !!!).

« Imprimer la sélection » : n’imprime que les cellules qui sont sélectionnées à ce moment-là (par exemple, si vous avez sélectionné une cellule au hasard avec votre souris, c’est elle qui sera imprimée !). Cette fonction est pratique si vous avez un tout petit tableau à imprimer et que vous n’avez pas besoin de définir de zone d’impression et de la personnaliser (cf. mode opératoire ci-dessus).

Cliquer sur l'image pour l'agrandir

Comment mettre des commentaires dans des cellules

Mode opératoire

1) Sélectionner une où plusieurs cellules dans lesquelles vous souhaitez écrire un commentaire.

2) Aller dans l’onglet « Révisions ».

3) Cliquer sur le bouton « Nouveau commentaire ».

Cliquer sur l'image pour l'agrandir

Trucs et astuces

La présence de commentaire dans une cellule se matérialise par un triangle rouge en haut à droite de la cellule. En passant le curseur de la souris sur la cellule, le commentaire apparaît.

Pour modifier, supprimer ou afficher en permanence les commentaires, il suffit de faire un clic-droit dans la cellule qui en contient un et sélectionner le bouton correspondant dans le menu déroulant qui s’affiche.

Cliquer sur l'image pour l'agrandir

Comment mettre des bulles d’explication dans des cellules qui s’affichent au passage de la souris

Cas concret

Vous devez envoyer un tableau à remplir à plusieurs personnes. Pour les aider dans cette tâche, vous souhaitez que lorsque l’on sélectionne une cellule, une « bulle d’information » s’affiche avec un complément d’information ou un mode opératoire comme dans l’exemple ci-dessous :

Cliquer sur l'image pour l'agrandir

Mode opératoire

1) Sélectionner une ou plusieurs cellules dans lesquelles vous souhaitez voir apparaître une bulle d’information.

2) Aller dans l’onglet « Données ».

3) Cliquer sur le bouton « Validation des données ».

4) dans la fenêtre qui s’ouvre, aller dans l’onglet « Message de saisie » et renseigner un titre ainsi qu’un message. Ce sont ces éléments qui apparaîtront dans la bulle lorsque l’on cliquera dans la ou les cellules (le titre sera en gras).

5) Après avoir vérifié que la case « Quand la cellule est sélectionnée » est bien cochée, cliquer sur le bouton OK.

Cliquer sur l'image pour l'agrandir

Comment faire lorsqu’une fonction ne marche pas automatiquement

Cas concret

J’ai entré une fonction dans ma première cellule et lorsque je souhaite l’appliquer à toutes les cellules du dessous (en tirant la fonction ou en faisant un copier/coller), la formule apparaît bien dans les cellules mais le résultat n’est pas celui attendu. Il correspond au résultat de la première cellule (par exemple, dans l’illustration ci-dessous, la cellule C5 indique bien la formule =A5+B5 mais le résultat n’est pas bon et correspond à celui de la cellule C2).

Mode opératoire

Cliquer sur l'image pour l'agrandir

Le problème provient sûrement d’une fausse manipulation où l’option de calcul est passé de « automatique » à « manuel ». Pour revenir en mode automatique, il suffit :
1) D’aller dans l’onglet « Formules »

2) D’appuyer sur la flèche sous « option de calcul » pour en ouvrir le menu déroulant.

3) De sélectionner « automatique ».

Comment grouper des colonnes / lignes

Cas concret

Mon tableau Excel contient de nombreuses lignes dont certaines sont des totaux de cellules situées au-dessus. Pour plus de lisibilité, j’ai joué avec la mise en forme (couleur, gras...) mais je souhaite également renforcer cette « arborescence » en regroupant certaines lignes entre elles.

Mode opératoire

1) Sélectionner l’ensemble des lignes que l’on souhaite grouper.

2) Dans la barre de menu, aller dans l’onglet « Données » et sélectionner « Grouper ».

Cliquer sur l'image pour l'agrandir

3) Il est possible de masquer les lignes groupées en appuyant sur le « - » situé à gauche des lignes ou sur le petit « 1 » placé en haut du tableur. En cliquant sur le petit « 1 », seules les lignes de niveau 1 (ici, les totaux) vont apparaître.

Cliquer sur l'image pour l'agrandir

Selon la même logique, il est possible d’afficher les lignes masquées en appuyant sur le « + » ou le petit « 2 » (lignes de niveau 2).

Cliquer sur l'image pour l'agrandir

Le mode opératoire est exactement le même pour grouper des colonnes.

Trucs et astuces

Il est possible de créer une multitude de « niveaux » (groupe, sous-groupe, sous-sous-groupe...).

Cela peut être utile par exemple si on souhaite présenter des données consolidées et macro selon une arborescence France, Régions, Départements, Communes tout en ayant la possibilité d’afficher simplement et rapidement les détails par Région, Département ou Commune en cliquant sur le « + » en face de la ligne correspondante.

Cliquer sur l'image pour l'agrandir

Comment afficher des colonnes / lignes masquées

Cas concret

Pour plus de visibilité, j’ai masqué les colonnes de mon tableau Excel que je n’utilise pas actuellement. Aujourd’hui, je souhaite les afficher de nouveau.

Mode opératoire

1) Sélectionner les deux colonnes situées juste avant et après la ou les colonnes masquées que l’on souhaite afficher de nouveau (dans l’illustration ci-dessous, la colonne masquée est la colonne C, il faut donc sélectionner les colonnes B et D).

2) Faire un clic droit dans la sélection et appuyer sur « Afficher » Le mode opératoire est exactement le même pour afficher des lignes masquées.

Cliquer sur l'image pour l'agrandir

Comment masquer des colonnes / lignes

Cas concret

Le tableau que j’enrichis et mets à jour régulièrement sur Excel devient trop grand et peu lisible. Je souhaite en masquer certaines, sans pour autant les supprimer définitivement du tableau.

Mode opératoire

1) Sélectionner la ou les colonnes que l’on souhaite masquer (pour sélectionner plusieurs colonnes en même temps, il suffit de maintenir la touche « Ctrl » appuyée pendant que vous sélectionnez l’ensemble des colonnes souhaitées).

2) Faire un clic droit dans la sélection et appuyer sur « Masquer ».
Le mode opératoire est exactement le même pour masquer des lignes.

Cliquer sur l'image pour l'agrandir

Comment changer le format d’un nombre

Cas concret

Je souhaite transformer 12 500 € en 12,5 K€ et appliquer ce changement automatiquement à tous les nombres d’une colonne.

Mode opératoire

1) Sélectionner la colonne dont on veut modifier le format des nombres.

2) Dans l’onglet « Accueil » ouvrir le menu « Nombre » ou alors faire un clic droit sur la sélection et sélectionner « Format de cellule ».

3) Dans la fenêtre qui vient de s’ouvrir, aller dans l’onglet « Nombre » et sélectionner « Personnalisée » dans la liste de catégories.

4) Dans le champ « type », sélectionner la formule qui vous convient parmi celles proposées en dessous (le champs « Exemple » se met à jour automatiquement pour vous permettre de voir le résultat de chaque formule). Si aucune des formules prédéfinies ne vous convient, vous pouvez écrire vous-même une formule. Dans notre exemple, pour obtenir 12,5 K€ au lieu de 12 500 €, la formule à indiquer est la suivante : # ##0,0 " K€"
5) Appuyer sur « OK ».

Cliquer sur l'image pour l'agrandir

Trucs et astuces
Voici quelques illustrations de l’utilisation des différentes formules. Les valeurs entre guillemets correspondent à du texte et peuvent être personnalisées à l’infini…