Excel : nos 5 meilleures astuces

Excel est un outil indispensable du monde du travail : il permet de construire un business plan, un modèle d’évaluation immobilière ou un outil de chiffrage sur mesure. Il est devenu nécessaire de savoir s’en servir et le maîtriser permet de décupler sa productivité. Mais nombre d’utilisateurs se limitent à des fonctionnalités de base. Nous vous partageons donc dans cet article 5 astuces Excel peu connues mais particulièrement utiles.

• Gérer la saisie des données avec l’option data validation
• Faire une recherche approximative à un ou plusieurs caractères près 
• Insérer une liste déroulante dynamique
• Créer un formatage de cellule sur mesure
• Utiliser la fonction imbriquée INDEX(MATCH) au lieu de VLOOKUP 

1ère astuce Excel : Gérer la saisie des données avec l’option data validation

Il arrive parfois que vous souhaitiez vous assurer qu’un seul type de données soit rentré sur une plage particulière. Notre première astuce Excel est la validation des données qui limite la saisie de l’utilisateur dans une feuille de calcul. 

Cas d’usage

Vous pouvez faire en sorte qu’Excel renvoie un message d’erreur si la valeur rentrée par l’utilisateur ne répond pas aux critères que vous avez précisés. Ces critères peuvent n’accepter que :

• des valeurs décimales comprises entre deux bornes
• des dates postérieures à une autre
• un texte d’une longueur maximale donnée, etc. 

Etapes à suivre 

Voici les étapes à suivre pour mettre en place une validation de données : 

• Sélectionnez la plage de données ou la cellule dont vous souhaitez restreindre les valeurs rentrées par les utilisateurs 
• Dans le ruban, sélectionnez Data/Données, puis dans la rubrique Data Tools/Outils de données cliquez sur Data Validation/Validation des données puis encore une fois sur Data Validation dans le ruban qui vient d’apparaître
• Une boîte de dialogue apparaît alors, faites votre choix dans la liste déroulante qui s’affiche lorsque vous cliquez sur Any Value/Tout et laissez-vous guider
• Une fois votre choix fait, cliquez sur Ok

Pour aller plus loin

Vous remarquez que s’affiche dans la boîte de dialogue un onglet « Input Message » / « Message de saisie ». Il s’agit d’un message qui apparaîtra lorsque l’utilisateur cliquera sur l’une des cellules de votre plage. Il est utile pour expliquer à l’utilisateur quelles données sont autorisées dans cette plage. L’ajout d’un message est facultatif.

De même, dans l’onglet « Error Alert » / « Alerte d’erreur » de la boîte de dialogue, vous pouvez personnaliser le message d’erreur qui apparaît dans une fenêtre pop-up lorsque l’utilisateur tente de saisir des données non valides.

2ème astuce Excel : Faire une recherche approximative à un ou plusieurs caractères près 

Vous connaissez sûrement le raccourci Ctrl+F pour ouvrir la boîte de dialogue « Rechercher et remplacer ». Mais il est moins connu que vous pouvez effectuer une recherche approximative avec cette même boîte de dialogue. 

Cas d’usage

Que vous ne soyez pas sûr de l’orthographe du mot que vous cherchez, ou que vous souhaitiez trouver les occurrences de celui-ci à un ou plusieurs caractères près, vous pouvez effectuer une recherche approximative. 

Si vous souhaitez trouver les occurrences d’une chaîne de caractères à un caractère près, l’astuce Excel est de remplacer ce caractère par un point d’interrogation.

Dans la liste de noms de dossiers suivante, l’utilisation du point d’interrogation permet de faire ressortir les noms FR01/01/2023 et FR31/01/2023. Excel renvoie les cellules qui contiennent la chaîne de caractères FR?1/01/2023, peu importe le caractère en position 3 (à la place du point d’interrogation).

Et si vous souhaitez trouver les occurrences d’une chaîne de caractères à plusieurs caractères près, l’astuce Excel est de remplacer ce caractère par un astérisque.

Dans la liste de noms de dossiers suivante, l’utilisation de l’astérisque permet de faire ressortir les noms FR01/01/2023, FR31/01/2023, FR01/02/2023 et FR30/02/2023. Excel nous a donné les cellules qui contiennent les chaînes de caractères FR au début et 2023 à la fin, peu importe le nombre et la nature des caractères situés entre FR et 2023 (à la place de l’astérisque).

Pour rappel, la casse – c’est-à-dire l’utilisation des majuscules et des minuscules – est uniquement prise en compte par la boite de dialogue si l’option « Match case » est sélectionnée.

3ème astuce Excel : Insérer une liste déroulante dynamique

Vous connaissez certainement le principe des listes déroulantes qui vous permettent d’afficher une liste dans une cellule. Cette liste prend ses valeurs à partir de cellules que vous aurez spécifiées au préalable. Mais qu’en est-il lorsque vous voulez ajouter une valeur à votre liste ? Il est fastidieux de recréer une liste déroulante et de préciser à chaque fois la nouvelle plage de données source. L’astuce Excel est de créer une liste déroulante dynamique.

Qu’est-ce qu’une liste déroulante dynamique ? 

Elle se présente comme la liste déroulante classique, mais elle prend comme source toutes les valeurs non nulles saisies à la suite dans une plage de données. Ainsi, chaque fois que vous voudrez rajouter une valeur à la liste déroulante, il suffira d’ajouter cette valeur à la suite de celles déjà renseignées. Et sans avoir à recréer une liste, la liste déroulante dynamique prendra en compte la nouvelle valeur tout juste renseignée.

Étapes à suivre 

Voici les étapes à suivre pour mettre en place une liste déroulante dynamique : 

• Sélectionnez la cellule dans laquelle vous souhaitez créer la liste déroulante dynamique.
• Dans le ruban sélectionnez « Data ». Puis dans la rubrique « Data Tools » cliquez sur « Data Validation » puis encore une fois sur « Data Validation » dans le ruban qui vient d’apparaître

Si votre Excel est en français, sélectionnez « Données », puis dans la rubrique « Outils de données » cliquez sur « Validation des données » puis encore une fois sur « Validation des données » dans le ruban qui vient d’apparaître

• Une boîte de dialogue apparaît alors, sélectionnez « List » dans la liste qui s’affiche lorsque vous cliquez sur « Any Value » (« Tout » en français)
• Dans le champ « Source », entrez la formule suivante : =OFFSET($B$1;0;0;COUNTIF($B$1:$B$100 ; »<> »))

=DECALER($B$1;0;0;NB.SI($B$1:$B$100; »<> »)) en français

Dans notre exemple, la liste source se trouve dans la plage B1:B100. La fonction COUNTIF permet d’abord de compter le nombre de cellules non vides dans la plage B1:B100, appelons ce nombre x. Puis la fonction OFFSET créé une liste qui commence en B1, de longueur x. La liste déroulante dynamique prend enfin cette liste comme source. 

Chaque fois que vous ajouterez un élément à la suite après la première valeur en B1, la liste source s’allongera donc automatiquement. Il est donc primordial que les valeurs de la plage se suivent sans vide et qu’il n’y ait dans la plage que des valeurs que l’on souhaite voir apparaître dans la liste. A vous de choisir une plage pertinente (cela peut-être la colonne entière si vous ne voulez pas avoir un nombre maximum d’éléments de votre liste). 

• Assurez-vous que l’option « In-cell dropdown » (« Liste déroulante dans la cellule » en français) est cochée
• Cliquez sur « Ok »

4ème astuce Excel : Créer un formatage de cellule sur mesure

Vous savez sûrement que vous pouvez préciser à Excel le type de données d’une plage pour en formatter les cellules (comme des dates ou des pourcentages par exemple). Mais une astuce Excel moins connue est que vous pouvez personnaliser bien plus ce formatage. 

En effet, pour une plage de nombres, vous pouvez par exemple préciser la couleur de ceux-ci en fonction de leur signe, mettre un tiret pour les zéros, faire en sorte d’avoir un espace entre les milliers etc. On utilise pour cela le format personnalisé. 

Etapes à suivre 

• Sélectionnez la plage de cellules que vous souhaitez formatter
• Faites un clic droit puis cliquez sur « Format Cells » (« Format de cellule » en français)
• Une boîte de dialogue s’affiche alors, cliquez sur « Custom » (« Personnalisée ») et entrez votre chaîne de caractères dans le champ « Type » en lieu et place du mot « Standard »
• Cliquez sur « Ok » 

Le format personnalisé utilise une chaîne de caractères pour décrire la règle qu’il va suivre. 

Dans notre exemple nous utilisons la chaîne « ### ### ### ; [Red](#,00) ; – ». 

Cette chaîne est construite de la manière suivante : 

• Format des nombres strictement positifs
• Format des nombres strictement négatifs
• Format du zéro

Voici les symboles les plus courants à utiliser :

# : Montrer le caractère s’il existe, et ne rien mettre sinon

0 : Montrer le caractère s’il existe, et mettre un zéro sinon

[Red] : Préciser la couleur du texte

() – … : Les parenthèses, les tirets et les espaces sont pris en compte comme vous les avez écrits dans le code

Ainsi, si l’on applique notre format personnalisé « ### ### ###;[Red](#,00);-»   pour les 3 cellules suivantes, on obtient ce résultat :

Nous avons gardé l’essentiel pour rendre la personnalisation plus intelligible, mais les options sont encore plus nombreuses. N’hésitez pas à regarder la documentation Microsoft pour découvrir toutes les possibilités offertes. Vous y découvrirez quelques codes très spécifiques tels que « ;;; » qui rend invisible la valeur de la cellule sans la supprimer.

5ème astuce Excel : Utiliser la fonction imbriquée INDEX(MATCH) au lieu de VLOOKUP

Les limites de la fonction VLOOKUP 

Vous connaissez peut-être la fonction VLOOKUP (RECHERCHEV en français), très utilisée pour rechercher des éléments dans une plage par ligne. Vous pouvez par exemple rechercher la moyenne d’un élève via son nom. Mais cette fonction a plusieurs défauts :

• la colonne qui comporte la cellule que l’on recherche est nécessairement la première de la plage sélectionnée
• il faut rentrer « en dur » le nombre de colonnes qui séparent la première colonne de celle qui comporte l’élément que nous recherchons. Insérer une colonne entre ces deux colonnes créé alors un décalage et la formule VLOOKUP ne fonctionne plus
• dans le cas des recherches par intervalle, les données doivent être triées par ordre croissant

La fonction imbriquée INDEX(MATCH)

Une autre des astuces Excel est d’imbriquer les fonction MATCH (EQUIV en français) et INDEX (la même fonction en français) entre elles. La fonction MATCH indique la position d’un élément recherché dans une liste. La fonction INDEX renvoie quant à elle l’élément situé à une position x d’une liste. 

Imbriquer ces 2 fonctions revient à faire un VLOOKUP mais sans avoir à se soucier de l’ordre des colonnes.

Et dans le cas des recherches par intervalle, la fonction MATCH fonctionne que lorsque les données sont triées par ordre croissant ou décroissant. Il suffit alors de préciser en 3^ème argument si l’on souhaite une recherche exacte (matchtype = 0), une recherche par intervalle croissant (matchtype = 1), ou décroissant (matchtype = -1).

Un exemple concret

Supposons que vous souhaitiez appliquer 

• un taux de 10% pour les deals dont la valeur est comprise entre 0 et 500
• un taux de 5% pour ceux compris entre 500 et 1000
• un taux de 2% entre 1000 et 2000 
• et un taux de 1% pour ceux supérieurs à 2000

Alors structurez votre tableau de référence comme ci-dessous, et pour chaque deal entrez la formule suivante.

De cette manière, la fonction imbriquée INDEX(MATCH) est plus robuste qu’un VLOOKUP car on a l’avantage de pouvoir insérer des colonnes n’importe où sans que notre fonction ne cesse de fonctionner.

Merci pour votre lecture, nous espérons que ce TOP 5 de nos astuces Excel vous aura été utile. L’idée était de vous faire découvrir des fonctionnalités moins connues de ce logiciel mais tout autant intéressantes. Vous aurez d’ailleurs remarqué que nous avons sélectionné des options avec lesquelles vous avez déjà sûrement travaillé, mais en poussant leur personnalisation un cran plus loin. Nous espérons vous avoir ainsi donné une meilleure idée de tout ce que l’on peut faire sur Excel.    

Pour savoir comment alléger votre fichier Excel, découvrez nos 8 astuces !