10 astuces Excel qui vont vous époustoufler

La plupart des professionnels utilisent Microsoft Excel tous les jours, et pourtant un nombre surprenant d'utilisateurs effleure à peine les capacités de ce puissant logiciel. Vous savez peut-être comment écrire une fonction SOMME basique ou formater un tableau, mais il existe tout un monde de fonctionnalités cachées conçues pour éliminer les tâches manuelles fastidieuses. Si vous effectuez des tâches répétitives, il y a presque certainement un moyen plus rapide.

Dans ce guide, nous allons découvrir 10 fonctionnalités puissantes qui transformeront la façon dont vous gérez vos feuilles de calcul. Des raccourcis de nettoyage de données ultra-rapides aux formules de recherche avancées, voici les fonctionnalités cachées d'Excel que vous devriez utiliser pour économiser des heures de travail chaque semaine.

1. Remplissage instantané : votre baguette magique pour nettoyer les données

Si vous avez déjà passé des heures à séparer manuellement des noms et prénoms, à extraire des noms de domaine à partir d'adresses e-mail ou à reformater des numéros de téléphone, le Remplissage instantané (Flash Fill) va vous époustoufler. Introduit dans Excel 2013, le Remplissage instantané utilise l'apprentissage automatique prédictif pour détecter des modèles dans votre saisie de données et remplit automatiquement le reste de la colonne.

Comment utiliser le Remplissage instantané :

1. Supposons que vous ayez une liste de noms complets dans la colonne A (par ex. « Jean Dupont »).
2. Dans la cellule B2, tapez le prénom que vous souhaitez extraire : « Jean ».
3. Appuyez sur Entrée pour passer à la cellule B3.
4. Utilisez le raccourci clavier CTRL + E.

Excel reconnaîtra instantanément que vous extrayez le premier mot de la colonne A et remplira parfaitement toute la colonne vers le bas. Cela fonctionne pour combiner des données, extraire des chaînes de texte spécifiques et modifier la casse du texte.

2. INDEX EQUIV : la mise à niveau de RECHERCHEV

Bien que RECHERCHEV soit la fonction de recherche la plus célèbre, elle a ses limites : elle ne peut chercher que de gauche à droite, et si vous insérez une nouvelle colonne dans votre jeu de données, votre formule se casse. Voici le duo dynamique INDEX et EQUIV. Ensemble, ils créent une recherche bidirectionnelle plus rapide, plus flexible et totalement immunisée contre les insertions de colonnes.

Voici un aperçu d'un jeu de données simple :

ID Employé (Col A)	Nom (Col B)	Département (Col C)
1001	Sarah Jenkins	Marketing
1002	Michael Chang	Finance
1003	David Smith	Opérations

Si vous souhaitez rechercher le Nom d'un employé en fonction de son ID, utilisez cette syntaxe :

=INDEX(B2:B4, MATCH(1002, A2:A4, 0))

La fonction EQUIV trouve le numéro de ligne de l'ID « 1002 » (qui est la ligne 2), et la fonction INDEX renvoie la valeur de la ligne 2 de la colonne Nom (« Michael Chang »). Pour approfondir les raisons pour lesquelles cette combinaison est privilégiée par les analystes, consultez notre guide INDEX EQUIV : La méthode de recherche supérieure.

3. Power Query pour l'importation de données automatisée

Téléchargez-vous régulièrement des fichiers CSV depuis une base de données pour supprimer les cinq mêmes colonnes, filtrer les lignes vides et changer le format des dates ? Arrêtez de le faire manuellement. Power Query est un outil ETL (Extraire, Transformer, Charger) intégré qui enregistre vos étapes de transformation de données et les automatise pour toujours.

Étapes pour automatiser avec Power Query :

1. Allez dans l'onglet Données sur le ruban Excel.
2. Cliquez sur Obtenir des données et choisissez votre source (ex : À partir d'un fichier > À partir d'un fichier texte/CSV).
3. Cliquez sur Transformer les données pour ouvrir l'éditeur Power Query.
4. Effectuez votre nettoyage de données (supprimer des colonnes, filtrer des lignes, renommer des en-têtes). Excel enregistre chaque étape dans le panneau de droite.
5. Cliquez sur Fermer et charger.

Le mois prochain, lorsque vous recevrez un nouveau CSV, écrasez simplement l'ancien fichier, faites un clic droit sur votre tableau Excel et appuyez sur Actualiser. Vos données sont instantanément nettoyées. Maîtrisez cela, et vous pourrez véritablement utiliser Power Query : Importez et transformez des données comme un pro.

4. Mise en forme conditionnelle avec des formules personnalisées

La plupart des utilisateurs savent comment utiliser la Mise en forme conditionnelle pour mettre en évidence les cellules supérieures à un certain nombre. Mais vous pouvez utiliser des formules personnalisées pour mettre en évidence des lignes entières en fonction de la valeur d'une seule cellule.

Par exemple, si vous souhaitez appliquer des « bandes zébrées » (mettre en surbrillance une ligne sur deux pour faciliter la lecture des tableaux larges) sans compter sur les styles de tableau par défaut d'Excel, vous pouvez utiliser les fonctions MOD et LIGNE.

Sélectionnez toute votre plage de données, allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué. Entrez cette formule :

=MOD(ROW(), 2)=0

Choisissez votre couleur de remplissage et cliquez sur OK. Désormais, chaque ligne paire de votre feuille de calcul sera automatiquement mise en évidence. Ce n'est qu'une des façons d'exploiter la Mise en forme conditionnelle : Visualisez vos données avec des couleurs pour de meilleurs rapports.

5. Segments de tableau croisé dynamique pour des tableaux de bord interactifs

Les tableaux croisés dynamiques sont incroyables pour résumer de grands jeux de données, mais naviguer dans les filtres déroulants standard peut s'avérer fastidieux pour les utilisateurs finaux. Les segments (Slicers) sont des boutons visuels cliquables qui filtrent vos tableaux croisés dynamiques instantanément, transformant un rapport basique en un tableau de bord interactif.

Pour ajouter un segment, cliquez n'importe où dans votre tableau croisé dynamique existant. Allez dans l'onglet Analyse du tableau croisé dynamique et cliquez sur Insérer un segment. Cochez les cases des champs selon lesquels vous souhaitez filtrer (par exemple, « Région » ou « Catégorie de produit »). Un ensemble de boutons élégants apparaîtra sur votre feuille. Vous pouvez même connecter un seul segment à plusieurs tableaux croisés dynamiques en faisant un clic droit sur le segment et en choisissant Connexions de rapport.

6. Affichages personnalisés pour sauvegarder votre configuration

Si vous partagez un classeur avec des collègues, vous connaissez probablement la frustration d'ouvrir un fichier pour découvrir que quelqu'un a masqué des lignes, modifié le niveau de zoom et appliqué des filtres bizarres. Les Affichages personnalisés résolvent ce problème en sauvegardant la disposition exacte de votre écran, vos paramètres de filtre et vos zones d'impression.

Configurez votre feuille de calcul exactement comme vous souhaitez la voir (appliquez vos filtres spécifiques, masquez les colonnes dont vous n'avez pas besoin et réglez votre zoom à 85 %). Allez dans l'onglet Affichage et cliquez sur Affichages personnalisés. Cliquez sur Ajouter et nommez-le « Mon affichage ». Désormais, peu importe dans quel état un collègue laisse la feuille, vous pouvez restaurer instantanément votre disposition personnalisée en deux clics.

7. Graphiques sparkline pour des mini-graphiques dans les cellules

Parfois, un graphique à barres ou une courbe en taille réelle prend trop de place sur un rapport financier dense. Les sparklines sont de minuscules graphiques légers qui tiennent entièrement à l'intérieur d'une seule cellule. Ils sont parfaits pour montrer des tendances au fil du temps, comme une trajectoire de ventes sur 12 mois, juste à côté de la somme totale.

Pour les utiliser, sélectionnez la cellule vide où vous souhaitez que le graphique apparaisse. Allez dans l'onglet Insertion, cherchez le groupe Graphiques sparkline, et choisissez Courbe ou Histogramme. Excel vous demandera la « Plage de données » (sélectionnez les cellules contenant les données historiques). Cliquez sur OK, et vous verrez une mini-courbe de tendance apparaître directement dans la cellule.

8. Listes déroulantes de validation des données pour éviter les erreurs

Des données corrompues en entrée donnent des résultats faussés en sortie. Si votre feuille de calcul repose sur une saisie de données précise, vous devez empêcher les utilisateurs de faire des fautes de frappe. La Validation des données vous permet de restreindre ce qui peut être saisi dans une cellule en créant une liste déroulante stricte.

Comment créer une liste déroulante :

1. Sélectionnez les cellules que vous souhaitez restreindre.
2. Allez dans l'onglet Données et cliquez sur Validation des données.
3. Sous le menu déroulant « Autoriser », sélectionnez Liste.
4. Dans la zone « Source », vous pouvez soit sélectionner une plage de cellules contenant votre liste approuvée, soit taper les éléments séparés par des points-virgules (ex : Pending, Approved, Rejected).
5. Cliquez sur OK.

Désormais, les utilisateurs ne peuvent sélectionner que parmi les options prédéfinies, garantissant une cohérence parfaite pour vos formules SOMME.SI et NB.SI par la suite.

9. Collage spécial : Transposer

Avez-vous déjà reçu une feuille de calcul où les données sont disposées horizontalement (les mois sur les colonnes), alors que vous en avez besoin verticalement (les mois sur les lignes) ? Vous n'avez pas besoin de tout retaper manuellement. Excel intègre une fonctionnalité pour basculer instantanément l'orientation des données.

Mettez simplement en surbrillance les données que vous souhaitez faire pivoter et copiez-les (CTRL + C). Faites un clic droit sur la cellule de destination où vous souhaitez que la nouvelle disposition commence, survolez Collage spécial, et cliquez sur l'icône avec les deux flèches pointant vers la droite et le bas, ou cochez explicitement la case qui dit Transposé. Vos lignes deviendront des colonnes, et vos colonnes deviendront des lignes.

10. La Valeur cible pour une analyse de scénarios instantanée

Si vous connaissez le résultat que vous attendez d'une formule, mais que vous n'êtes pas sûr de la valeur d'entrée nécessaire pour y parvenir, l'outil Valeur cible (Goal Seek) fait le calcul pour vous. Il fonctionne essentiellement à l'envers à travers vos formules.

Imaginez que vous ayez un modèle de profit simple. Votre profit est calculé comme suit : (Price * Units Sold) - Fixed Costs. Vous connaissez votre prix et vos coûts fixes, et vous voulez savoir exactement combien d'unités vous devez vendre pour atteindre un objectif de profit de 50 000 $.

Allez dans l'onglet Données, cliquez sur Analyse d'hypothèses, et choisissez Valeur cible. Une petite boîte de dialogue apparaît avec trois paramètres :

• Cellule à définir : Cliquez sur la cellule contenant votre formule de profit.
• Valeur à atteindre : Tapez 50000.
• Cellule à modifier : Cliquez sur la cellule vide pour les unités vendues.

Cliquez sur OK, et Excel fera rapidement défiler les nombres jusqu'à ce qu'il trouve le nombre exact d'unités requises pour atteindre votre objectif.

Faites passer vos compétences Excel au niveau supérieur

Excel est un programme incroyablement riche. La maîtrise d'astuces comme le Remplissage instantané, INDEX EQUIV et la Valeur cible augmentera immédiatement votre productivité et fera de vous l'expert en tableurs incontournable de votre bureau. Cependant, vous n'avez pas besoin de mémoriser chaque formule complexe pour être hautement efficace.

Si vous vous retrouvez à lutter pour écrire une fonction SI imbriquée ou à comprendre une recherche compliquée, vous n'avez pas à regarder une barre de formule vide. Avec ExcelGPT, vous pouvez simplement taper ce que vous souhaitez accomplir en français courant, et il générera instantanément la formule parfaite et sans erreur pour vous. L'adoption de l'IA est un raccourci massif : apprenez à utiliser ChatGPT pour Excel : Écrivez des formules avec l'IA pour accélérer encore davantage votre flux de travail.

Foire Aux Questions

Quelle est la meilleure astuce Excel pour les vrais débutants ?

Le Remplissage instantané (CTRL + E) est sans doute la meilleure astuce pour les débutants. Il ne nécessite aucune connaissance en formules, mais automatise complètement les tâches fastidieuses de saisie de données, comme séparer des noms, combiner des colonnes ou formater des numéros de téléphone.

INDEX EQUIV est-il vraiment meilleur que RECHERCHEV ?

Oui. Bien que RECHERCHEV soit plus facile à apprendre au début, INDEX EQUIV est largement supérieur car il peut chercher vers la gauche, traite les grands jeux de données plus rapidement et, surtout, la formule ne se cassera pas si vous insérez ou supprimez des colonnes dans votre tableau de référence.

Les Affichages personnalisés modifieront-ils les données elles-mêmes ?

Non. Les Affichages personnalisés n'enregistrent que la présentation de vos données, en particulier les paramètres de filtre, les lignes/colonnes masquées et les paramètres d'impression. Les données brutes et les formules sous-jacentes restent complètement inchangées.

L'IA peut-elle m'aider à apprendre les astuces Excel plus rapidement ?

Absolument. Au lieu de passer 20 minutes à chercher la bonne syntaxe pour une formule avancée sur Google, vous pouvez décrire la disposition exacte de votre feuille de calcul et vos objectifs à un outil d'IA, qui vous fournira la formule précise et expliquera son fonctionnement.