90 % des utilisateurs Excel se contentent de SOMME, MOYENNE, RECHERCHEV et de quelques tableaux croisés dynamiques. Pourtant, depuis 2020, Microsoft a profondément modernisé le moteur de calcul d’Excel avec des fonctions matricielles dynamiques, LAMBDA, LET et RECHERCHEX. Selon la Microsoft Tech Community Excel, ces nouvelles fonctions divisent par 3 à 5 le temps passé sur les manipulations classiques. Voici 10 fonctions Excel avancées qui transformeront votre quotidien si vous travaillez régulièrement avec des données.
Excel a discrètement vécu une révolution depuis 2020. Le moteur de calcul a été refondé pour gérer les tableaux dynamiques (dynamic arrays), qui propagent automatiquement un résultat sur plusieurs cellules sans avoir besoin de Ctrl+Maj+Entrée. Cette évolution a rendu obsolètes des dizaines de manipulations fastidieuses.
Pourtant, dans la pratique romande, la majorité des professionnels continuent d’utiliser Excel comme en 2010. RECHERCHEV au lieu de RECHERCHEX, des formules à rallonge au lieu de LAMBDA, des manipulations manuelles de texte au lieu de FRACTIONNER.TEXTE. Résultat : une minorité d’utilisateurs exploite réellement les fonctions matricielles dynamiques, alors qu’elles sont disponibles dans toutes les versions récentes d’Excel.
Conséquence directe : un analyste qui maîtrise les fonctions modernes accomplit en 30 minutes ce qui en demande 2 heures à un utilisateur intermédiaire. Voici donc les 10 fonctions qui font cette différence, classées par ordre d’impact.
RECHERCHEV souffre de trois limites historiques : elle ne cherche que vers la droite, elle se casse si la colonne de référence n’est pas la première, et elle ne gère pas nativement les valeurs introuvables. RECHERCHEX résout les trois.
=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_retour; "Non trouvé")Vous pouvez chercher à gauche, à droite, du dernier au premier élément, et gérer le « non trouvé » sans imbriquer un SIERREUR. En somme, RECHERCHEX devrait remplacer 100 % de vos RECHERCHEV en 2026.
Au lieu de copier-coller manuellement les lignes correspondant à un critère, FILTRE renvoie un tableau dynamique :
=FILTRE(Ventes; Ventes[Région]="Genève"; "Aucune donnée")Cette fonction supprime à elle seule 50 % des manipulations de filtres avancés et de macros simples.
Plus besoin de tableau croisé dynamique juste pour extraire la liste des clients ou des produits :
=UNIQUE(Ventes[Client])Le résultat se met à jour automatiquement lorsque vous ajoutez des données. Couplé à TRIER, c’est imbattable.
LET permet de stocker des résultats intermédiaires dans une formule, comme des variables en programmation :
=LET(
total; SOMME(Ventes[Montant]);
moyenne; total/NBVAL(Ventes[Client]);
SI(moyenne>1000; "Bon"; "Faible")
)Cela rend les formules complexes 10 fois plus lisibles et améliore les performances sur les gros classeurs. C’est probablement la fonction la plus sous-utilisée par rapport à son impact.
LAMBDA permet de définir une formule personnalisée, réutilisable comme une fonction native, sans VBA. Exemple : calculer la TVA suisse de 8,1 % :
TVA_CH = LAMBDA(montant; montant * 0,081)Une fois nommée via le Gestionnaire de noms, vous l’utilisez partout : =TVA_CH(B2). Pour les modèles financiers récurrents, c’est un gain de productivité considérable. D’ailleurs, la documentation Office Scripts de Microsoft positionne LAMBDA comme la voie d’entrée dans la programmation pour les utilisateurs métier.
Combien de fois avez-vous utilisé Données → Convertir → Texte délimité ? FRACTIONNER.TEXTE remplace cette manipulation par une formule dynamique :
=FRACTIONNER.TEXTE(A2; ";")Idéal pour traiter des fichiers CSV bruts ou des champs concaténés. De plus, le résultat se met à jour automatiquement quand la source change.
L’inverse de FRACTIONNER.TEXTE. JOINDRE.TEXTE ignore les cellules vides, ce qui résout l’un des cauchemars classiques de CONCATENER :
=JOINDRE.TEXTE(", "; VRAI; A2:A20)En particulier pour les listes de mailings ou les libellés agrégés, le gain est immédiat.
Plus besoin de re-trier votre tableau à la main quand vous ajoutez des données. TRIER renvoie un tableau trié et reste dynamique :
=TRIER(FILTRE(Ventes; Ventes[Région]="Lausanne"); 4; -1)Cet exemple combine FILTRE et TRIER pour afficher dynamiquement les ventes lausannoises triées par montant décroissant.
SEQUENCE crée une série de nombres :
=SEQUENCE(12; 1; DATE(2026;1;1); 31)Génère les 12 premiers jours de chaque mois de 2026. Particulièrement utile pour construire dynamiquement des tables de paramètres ou des calendriers de simulation.
SOMMEPROD n’est pas nouvelle, mais c’est probablement la fonction la plus polyvalente d’Excel. Elle remplace à elle seule de nombreuses combinaisons SOMME.SI, NB.SI, et permet des calculs conditionnels avancés :
=SOMMEPROD((Ventes[Région]="Genève")*(Ventes[Année]=2026)*Ventes[Montant])Cette formule calcule la somme des ventes 2026 à Genève. Sa magie tient à la conversion automatique des conditions booléennes en 0 ou 1. C’est l’outil préféré des contrôleurs de gestion expérimentés. Néanmoins, depuis l’arrivée de FILTRE et RECHERCHEX, son usage évolue : SOMMEPROD reste imbattable sur les multi-conditions agrégées.
Pour les utilisateurs qui veulent franchir un cap supplémentaire après MOS Excel Expert, Microsoft propose Office Scripts, un système d’automatisation moderne basé sur TypeScript intégré à Excel Online. Concrètement, vous enregistrez vos manipulations comme une macro, puis vous éditez le code TypeScript généré pour le rendre paramétrable, robuste et partageable. Combiné à Power Automate, Office Scripts permet d’orchestrer des automatisations cross-applications (Outlook → Excel → Teams) sans aucune installation locale. Pour les contrôleurs de gestion et les data analysts intermédiaires, c’est l’entrée naturelle vers le Low-Code Microsoft, sans avoir à apprendre Python ou VBA.
En outre, la certification Microsoft Office Specialist Excel Expert (MO-201) reconnaît officiellement la maîtrise des fonctions avancées d’Excel. En Suisse romande, cette certification distingue clairement votre CV dans les fonctions finance, contrôle de gestion, analyse de données et reporting.
Sur le marché de l’emploi en Suisse romande, de nombreuses offres dans la finance, le contrôle de gestion et l’analyse de données valorisent une certification Excel reconnue. Le contenu de l’examen couvre précisément les fonctions présentées ici, ce qui en fait un excellent objectif pédagogique pour structurer votre montée en compétences.
Formation recommandée
Microsoft Office Specialist – MOS Excel Expert
Réf. MOS-EE
Validez officiellement votre maîtrise Excel avancée avec la certification MO-201 : fonctions avancées, tableaux croisés, macros, analyse de données. Préparation intensive sur 3 jours.
Au-delà des fonctions de cellule, Excel intègre Power Query M, un langage de transformation de données souvent ignoré des utilisateurs avancés. Pourtant, trois patterns Power Query peuvent à eux seuls éliminer 80 % des manipulations manuelles répétitives. Voici les trois plus impactants.
Le cas classique : votre direction financière reçoit chaque mois 10 fichiers Excel similaires des filiales. Vous les empilez à la main dans un fichier consolidé. Avec Power Query, une seule manipulation suffit. Dans Excel, Données → Obtenir des données → À partir d’un fichier → À partir d’un dossier. Vous pointez le dossier, Power Query détecte automatiquement la structure commune et empile les 10 fichiers en une seule requête mise à jour à chaque ouverture.
Bénéfice : ce qui prenait 30 minutes manuelles chaque mois devient un simple clic « Actualiser ». Et si un onzième fichier arrive, il est automatiquement intégré sans modification.
Les fichiers Excel reçus de partenaires arrivent souvent au format « tableau croisé » : une colonne par mois, une ligne par produit. Pour analyser ces données dans Power BI ou Power Query, il faut les remettre au format « plat » (une ligne par combinaison mois × produit). C’est le rôle de la fonction Unpivot dans Power Query.
Sélectionnez vos colonnes mensuelles, faites Transformer → Dépivoter les colonnes. Power Query produit instantanément un tableau plat exploitable. À l’inverse, la fonction Pivot reconstitue une matrice à partir d’un tableau plat. Maîtriser ces deux opérations vous fait gagner des heures à chaque traitement de données externes.
Vous voulez qu’une requête Power Query change automatiquement de fichier source en fonction d’une cellule Excel ? C’est le rôle des paramètres Power Query. Créez un paramètre nommé (par exemple « Mois ») puis utilisez la fonction Excel.CurrentWorkbook() pour lire une cellule Excel. Power Query lit alors dynamiquement le mois sélectionné par l’utilisateur et adapte la source ou les filtres.
Ce pattern transforme un classeur Excel en application interactive sans VBA. C’est particulièrement puissant pour les modèles de simulation utilisés par les contrôleurs de gestion ou les chargés d’affaires.
Ces trois patterns Power Query, combinés aux 10 fonctions modernes vues plus haut, constituent le socle technique d’un utilisateur Excel niveau Expert en 2026. La certification MOS Excel Expert valide d’ailleurs précisément cette combinaison de compétences.
Au-delà des trois patterns précédents, Power Query peut piloter une automatisation complète sans aucun script externe. Imaginez : chaque matin, ouvrir votre classeur déclenche automatiquement la récupération des données fraîches d’une API ou d’un dossier partagé, sans aucune action manuelle. C’est ce que permettent les options de rafraîchissement automatique de Power Query, accessibles via Données → Requêtes et connexions → Propriétés.
Trois réglages clés à connaître. D’abord, le rafraîchissement à l’ouverture du fichier garantit que les données sont toujours fraîches. Ensuite, le rafraîchissement périodique (toutes les 30, 60 ou 240 minutes) maintient les données à jour pendant la journée pour les utilisateurs en saisie continue. Enfin, le rafraîchissement en arrière-plan permet de continuer à travailler sur le fichier pendant que la requête se met à jour.
Combiné aux paramètres dynamiques du Pattern 3, vous obtenez un véritable mini-tableau de bord Excel qui se rafraîchit sans intervention. Pour un contrôleur de gestion ou un chargé de portefeuille, c’est l’équivalent d’un Power BI Light intégré directement dans son outil quotidien, sans nouvelle licence à demander.
La vraie puissance d’Excel moderne apparaît quand vous chaînez ces fonctions. Imaginons un tableau de bord commercial qui doit afficher, en temps réel, le top 5 des clients d’une région donnée. En une seule formule, vous filtrez les ventes de la région avec FILTRE, vous les triez par montant décroissant avec TRIER, puis vous ne conservez que les cinq premières lignes. Le tout se recalcule automatiquement dès qu’une nouvelle vente est saisie, sans macro ni manipulation manuelle.
Vous pouvez ensuite encapsuler cette logique dans une fonction LAMBDA nommée, par exemple TopClients, pour la réutiliser sur chaque région sans réécrire la formule. C’est exactement ce type de raisonnement (penser en briques réutilisables plutôt qu’en cellules isolées) qui distingue un utilisateur intermédiaire d’un véritable expert Excel. Cette approche modulaire réduit aussi le risque d’erreur, puisqu’une correction faite dans la LAMBDA se propage partout où elle est utilisée.
Les fonctions Excel modernes (RECHERCHEX, FILTRE, UNIQUE, LET, LAMBDA, FRACTIONNER.TEXTE, TRIER, SEQUENCE, SOMMEPROD) ne sont pas réservées aux profils techniques. Elles transforment radicalement la productivité de tout utilisateur qui manipule régulièrement des données. Le retour sur investissement de leur apprentissage se mesure en heures gagnées chaque semaine, et en qualité des analyses produites.
Concrètement, commencez par adopter RECHERCHEX à la place de RECHERCHEV pendant deux semaines, puis intégrez progressivement FILTRE, LET et UNIQUE. Vous serez surpris de ne plus jamais vouloir revenir en arrière.
Ces fonctions sont-elles disponibles dans toutes les versions d’Excel ?
Les fonctions matricielles dynamiques (RECHERCHEX, FILTRE, UNIQUE, TRIER, SEQUENCE) sont disponibles dans Microsoft 365 et Excel 2021+. LAMBDA et FRACTIONNER.TEXTE sont arrivés plus récemment et nécessitent une version M365 à jour.
Faut-il abandonner RECHERCHEV ?
Oui, dans les nouveaux fichiers. RECHERCHEX est plus simple, plus robuste et plus rapide. Conservez RECHERCHEV uniquement pour la compatibilité avec d’anciennes versions Excel.
LAMBDA peut-il remplacer VBA ?
Partiellement. Pour des fonctions de calcul réutilisables, LAMBDA suffit. En revanche, pour de l’automatisation complète (boucles, interactions utilisateur, API), VBA ou Office Scripts restent nécessaires.
Quelle est la difficulté réelle de la certification MO-201 ?
Le passage d’un niveau intermédiaire vers Expert demande environ 3 jours de formation intensive plus de la pratique. L’examen porte sur des cas concrets, pas sur des questions théoriques, ce qui en fait une certification très opérationnelle.
Comment garder ses compétences Excel à jour ?
Suivez la Microsoft Tech Community Excel, qui publie chaque mois les nouvelles fonctions. Pratiquez sur de vrais cas d’usage métier, pas sur des exercices déconnectés.
Nos dernières publications
S’abonner à la Newsletter
Consultez nos formations et sessions confirmées
ITTA
Route des jeunes 35
1227 Carouge, Suisse
ITTA
Route des jeunes 35
1227 Carouge, Suisse