La mise en évidence de cellules avec la mise en forme conditionnelle Excel peut sembler une tâche triviale. Mais pas si vous pensez aux nombreuses façons dont vous pouvez réellement le faire. Voulez-vous ombrer les nombres les plus élevés ou les plus bas dans une plage, une colonne ou une ligne ? Ou peut-être souhaitez-vous afficher les 3 premières valeurs dans chaque ligne ? Et ce serait vraiment bien si vous pouviez contrôler le nombre de cellules en surbrillance directement sur la feuille sans avoir à accéder à chaque fois au gestionnaire des règles de mise en forme conditionnelle. 

Mettre en surbrillance les N valeurs supérieures ou inférieures dans la plage

Dans Microsoft Excel, il existe plusieurs façons de mettre en évidence les valeurs les plus élevées et les plus basses avec une mise en forme conditionnelle. Ci-dessous, nous vous proposons un aperçu de 3 méthodes différentes, afin que vous puissiez choisir celle qui vous convient le mieux.

Mettez en surbrillance les valeurs supérieures et inférieures avec la règle intégrée

Le moyen le plus rapide de mettre en évidence les 3, 5, 10 premières valeurs (ou les n inférieures) dans Excel consiste à utiliser une règle de mise en forme conditionnelle intégrée. Voici comment:

1. Sélectionnez la plage dans laquelle vous souhaitez mettre en surbrillance les chiffres.
2. Sous l’onglet Accueil, dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
3. Dans le menu déroulant, sélectionnez Règles des valeurs de plage haute / basse, puis cliquez sur 10 valeurs les plus élevées…ou sur 10 valeurs les moins élevées….

4.Dans la boîte de dialogue qui s’ouvre, précisez le nombre d’éléments à mettre en surbrillance et choisissez l’une des options de formatage prédéfinies. Pour appliquer une mise en forme qui ne figure pas dans la liste, cliquez sur Format personnalisé… .Les modifications que vous apportez ici sont immédiatement reflétées dans votre ensemble de données. Donc, si vous êtes satisfait des résultats, cliquez sur OK .

Pour cet exemple, nous choisissons de mettre en évidence les 4 premières valeurs avec le remplissage rouge clair par défaut et obtenons ce résultat :

Plus d’options de formatage pour afficher les valeurs les plus élevées et les plus basses

Si vous souhaitez plus d’options que celles proposées dans les préréglages Haute/Basse, vous pouvez créer une nouvelle règle à partir de zéro :

1. Sélectionnez une plage avec des valeurs numériques.
2. Sous l’onglet Accueil, cliquez sur Mise en forme conditionnelle / Nouvelle règle.
3. Dans la boîte de dialogue Nouvelle règle de mise en forme conditionnelle, choisissez Appliquer une forme uniquement aux rangées parmi les premières ou les dernières valeurs.
4. Dans la liste déroulante, sélectionnez Premiersou Derniers et saisissez le nombre de valeurs à mettre en surbrillance dans la case à côté.
5. Cliquez sur le bouton Formatet choisissez le formatage souhaité pour Nombre, Bordure et Remplissage.
6. Cliquez sur OK.

Par exemple, voici comment mettre en évidence les 5 principales valeurs avec une couleur d’arrière-plan verte.

Résultat

Mettez en surbrillance les valeurs supérieures ou inférieures avec une formule

Les règles intégrées décrites ci-dessus sont pratiques et faciles à appliquer. Cependant, ils présentent un inconvénient majeur : chaque fois que vous souhaitez afficher un nombre différent de valeurs, vous devrez modifier le nombre dans le gestionnaire de règles de formatage conditionnel.

Pour rendre la règle plus résiliente, vous pouvez la baser sur une formule. Dans notre cas, nous utiliserons :

La fonction GRANDE.VALEUR pour ombrer les premiers chiffres :

= cellule_en_haut_à_gauche >= GRANDE.VALEUR (plage ; n)

La fonction PETITE.VALEUR pour ombrer les derniers chiffres :

= cellule_en_haut_à_gauche <= PETITE.VALEUR (plage ; n)

Pour colorer les valeurs les plus élevées ou les plus basses à l’aide d’une règle basée sur une formule, procédez comme suit :

1. Tapez le nombre de valeurs à mettre en évidence dans une cellule de saisie prédéfinie. Pour cet exemple, nous saisissons le nombre de valeurs supérieures dans F2 et le nombre de valeurs inférieures dans F3.
2. Sélectionnez la plage de nombres (A2:C8).
3. Sous l’ onglet Accueil , cliquez sur Mise en forme conditionnelle/ Nouvelle règle .
4. Dans la boîte de dialogue Nouvelle règle de mise en forme conditionnelle, sélectionnezUtiliser une formule pour déterminer les cellules à formater.
5. Dans la zone Format des valeurs où cette formule est vraie, entrez l’une de ces formules :
   Pour mettre en surbrillance les n premières valeurs :

=A2>= GRANDE.VALEUR ($A$2:$C$8 ; $F$2)

Pour mettre en évidence les n dernières valeurs :

=A2<= PETITE.VALEUR ($A$2:$C$8 ; $F$3)

Où $A$2 :$C$8 est la plage appliquée, A2 est la cellule la plus à gauche de la plage ; F2 et F3 sont les n valeurs.

Veuillez noter que nous verrouillons la plage source et les cellules d’entrée avec des références absolues et utilisons une référence relative pour la cellule supérieure gauche.

Cliquez sur le bouton Format et choisissez le formatage souhaité.

6. Cliquez deux fois sur OKpour fermer les deux fenêtres.

Pour des instructions plus détaillées, veuillez consulter Créer une règle de mise en forme conditionnelle avec une formule .

Comment fonctionne cette formule :

La fonction LARGE renvoie la n-ième plus grande valeur dans la plage spécifiée. Dans notre cas, il trouve la 3 ^ème valeur la plus élevée dans A2:C8, soit 92. La formule compare chaque nombre de la plage sélectionnée avec la 3 ^ème valeur la plus élevée. Pour toute cellule supérieure ou égale à 92, elle renvoie VRAI et la règle de mise en forme conditionnelle est appliquée.