« Excel avancé » apparaît dans de nombreuses offres d’emploi en comptabilité, gestion, achats ou administration des ventes, sans que l’employeur précise toujours ce qu’il met derrière. Dans les faits, il attend trois choses : des formules qui croisent plusieurs tables sans erreur, des synthèses produites en quelques minutes et des fichiers que d’autres peuvent utiliser sans les casser. Ce guide détaille les sept compétences qui font cette différence, avec des formules prêtes à reprendre sur un journal de commandes, puis la façon de les acquérir et de les faire reconnaître.
Repérer ce qu’un employeur attend d’un profil Excel avancé
Le tableau ci-dessous traduit les situations de travail les plus fréquentes en compétences et en outils. Il sert de fil conducteur au reste de l’article : chaque ligne fait l’objet d’une section.
| Situation en entreprise | Compétence attendue | Outils Excel |
|---|---|---|
| Rapprocher un export de commandes et un fichier clients | Croiser des tables, y compris sur plusieurs critères | RECHERCHEX, INDEX/EQUIV |
| Sortir la liste des commandes d’une région au-dessus d’un seuil | Extraire et trier sans copier-coller | FILTRE, UNIQUE, TRIER |
| Reprendre le fichier d’un collègue parti en congé | Écrire des formules lisibles et maintenables | Tableaux structurés, LET |
| Préparer le chiffre d’affaires par mois et par commercial | Synthétiser des milliers de lignes | Tableau croisé dynamique, segments |
| Faire saisir un suivi par toute une équipe | Fiabiliser la saisie | Validation des données, protection |
| Répondre à « quel prix pour atteindre 30 % de marge ? » | Simuler des hypothèses | Valeur cible, Gestionnaire de scénarios |
| Mettre à jour chaque mois le même rapport | Automatiser les tâches répétitives | Power Query, macros |
Si vous cherchez d’abord à situer votre niveau actuel ou à vous préparer à un test, notre guide niveau Excel : l’évaluer et l’indiquer sur votre CV propose une grille en quatre paliers et des exercices chronométrés. Les exemples qui suivent utilisent un tableau nommé Commandes avec les colonnes N°, Date, Client, Région, Produit, Quantité et Montant HT.
Croiser des tables sur plusieurs critères avec RECHERCHEX
La recherche simple d’un prix à partir d’une référence relève déjà du niveau intermédiaire ; la comparaison complète entre RECHERCHEV et RECHERCHEX est détaillée dans notre guide RECHERCHEX ou RECHERCHEV. Le niveau avancé commence quand la clé de recherche n’existe pas telle quelle : retrouver le montant d’une commande à partir du client et du produit, par exemple.
Avec le client en H2 et le produit en I2, la formule multiplie deux tests logiques. Le produit vaut 1 uniquement sur la ligne où les deux conditions sont vraies, et RECHERCHEX cherche ce 1 :
=RECHERCHEX(1;(Commandes[Client]=H2)*(Commandes[Produit]=I2);Commandes[Montant HT];"Introuvable")Le quatrième argument remplace l’erreur #N/A par un texte explicite, ce qui évite qu’une commande absente fausse tous les totaux en aval. Sur un poste équipé d’Excel 2019 ou plus ancien, la même logique s’écrit avec INDEX et EQUIV, validée avec Ctrl+Maj+Entrée.
Extraire et trier automatiquement avec FILTRE, UNIQUE et TRIER
Les fonctions de tableau dynamique renvoient plusieurs résultats à partir d’une seule formule, qui se propagent dans les cellules voisines. Elles remplacent les filtres refaits à la main chaque semaine et les listes copiées-collées qui ne se mettent jamais à jour. D’après la page Microsoft de la fonction FILTRE, elles sont disponibles dans Excel pour Microsoft 365, Excel 2021 et Excel 2024.
- Liste des clients sans doublon, triée :
=TRIER(UNIQUE(Commandes[Client]))produit une liste qui s’allonge seule quand un nouveau client apparaît. - Commandes du Nord au-delà de 1 000 € :
=FILTRE(Commandes;(Commandes[Région]="Nord")*(Commandes[Montant HT]>1000);"Aucune commande")renvoie toutes les colonnes des lignes concernées. - Les mêmes commandes, de la plus grosse à la plus petite : encadrez la formule précédente par TRIER en indiquant la 7e colonne et l’ordre décroissant,
=TRIER(FILTRE(…);7;-1).
Laissez toujours assez de cellules vides sous et à droite de la formule : si le résultat ne peut pas s’étendre, Excel affiche l’erreur #PROPAGATION!. Une liste produite par UNIQUE sert aussi de source à une liste déroulante, comme on le verra plus bas.
Écrire des formules lisibles avec les tableaux structurés et LET
Un fichier avancé est d’abord un fichier qu’un collègue peut reprendre. Deux habitudes changent tout : travailler sur des tableaux structurés plutôt que sur des plages, et nommer les étapes d’un calcul long.
- Cliquez dans vos données, puis Accueil › Mettre sous forme de tableau (ou Ctrl+L) et confirmez que la première ligne contient les en-têtes.
- Dans l’onglet Création de tableau, remplacez le nom proposé (Tableau1) par Commandes dans la zone Nom du tableau.
- Écrivez vos formules en sélectionnant les colonnes à la souris : Excel insère
Commandes[Montant HT]au lieu deG2:G850, et la plage suit automatiquement les nouvelles lignes.
LET attribue un nom à un résultat intermédiaire, réutilisable dans la suite de la formule. Pour calculer le taux d’atteinte de l’objectif de la région saisie en H2, avec un second tableau Objectifs :
=LET(ca;SOMME.SI.ENS(Commandes[Montant HT];Commandes[Région];H2);objectif;RECHERCHEX(H2;Objectifs[Région];Objectifs[Objectif]);SI(objectif=0;"";ca/objectif))Chaque morceau ne se calcule qu’une fois et la dernière partie se lit presque comme une phrase. LET est proposée dans les mêmes versions que FILTRE, selon la page Microsoft de la fonction LET.
Synthétiser des milliers de lignes avec un tableau croisé dynamique interactif
Construire un tableau croisé dynamique est la base ; la méthode complète, avec un fichier d’exercice, figure dans notre guide du tableau croisé dynamique. Ce qui distingue un usage avancé, c’est un tableau que le destinataire manipule lui-même sans risque :
- Segments et chronologie : depuis l’onglet Analyse du tableau croisé dynamique, Insérer un segment (Région, Commercial) et Insérer une chronologie (Date) donnent des boutons de filtre cliquables.
- Afficher les valeurs en pourcentage : clic droit sur une valeur › Afficher les valeurs › % du total général, pour lire la part de chaque région sans formule.
- Grouper les dates : clic droit sur une date › Grouper › Mois et Années, pour comparer un mois à la même période de l’année précédente.
- Source sur un tableau structuré : avec Commandes comme source, il suffit d’actualiser pour intégrer les nouvelles lignes.
Fiabiliser la saisie avec la validation des données et la protection
Un fichier rempli par plusieurs personnes se dégrade vite : « Nord », « nord » et « Nord » avec une espace finale deviennent trois régions dans le tableau croisé dynamique. Deux réglages évitent ces dérives.
- Sélectionnez la colonne Région du tableau, puis Données › Validation des données. Dans Autoriser, choisissez Liste et indiquez comme source la plage d’une feuille Listes, par exemple
=Listes!$A$2:$A$6. - Dans l’onglet Alerte d’erreur de la même fenêtre, choisissez le style Arrêt et rédigez un message utile (« Choisissez une région dans la liste »).
- Sélectionnez les cellules de saisie, ouvrez Format de cellule › Protection et décochez Verrouillée.
- Activez Révision › Protéger la feuille : les formules restent visibles mais ne peuvent plus être écrasées, tandis que les zones de saisie restent modifiables.
La protection de feuille empêche les erreurs de manipulation, pas un accès malveillant : ce n’est pas un dispositif de sécurité des données. Pour signaler visuellement les saisies incohérentes qui passent malgré tout, combinez ces réglages avec la mise en forme conditionnelle.
Simuler des hypothèses avec la Valeur cible et le Gestionnaire de scénarios
Les outils de simulation répondent aux questions que pose un responsable en réunion. Ils se trouvent dans l’onglet Données, groupe Prévision, sous Analyse de scénarios.
| Outil | Question à laquelle il répond | Exemple |
|---|---|---|
| Valeur cible | Quelle valeur d’entrée donne le résultat voulu ? | Prix de vente unitaire nécessaire pour atteindre 30 % de marge |
| Gestionnaire de scénarios | Que devient le résultat selon plusieurs jeux d’hypothèses ? | Budget en version prudente, médiane et optimiste |
| Table de données | Comment varie le résultat quand une ou deux entrées changent ? | Marge selon le prix et la quantité vendue |
Pour la Valeur cible, la cellule à définir doit contenir une formule (le taux de marge) et la cellule à modifier une valeur saisie (le prix). Excel ajuste le prix jusqu’à obtenir 30 %, puis propose de conserver ou d’annuler le résultat. Sur un compte de résultat prévisionnel, cette technique remplace des dizaines d’essais manuels ; un exemple de structure figure dans notre article sur le compte de résultat sur Excel.
Préparer le passage au niveau expert avec Power Query et les macros
Quand le même nettoyage d’export revient chaque mois, les formules ne suffisent plus. Power Query enregistre les étapes d’import et de nettoyage et les rejoue en un clic sur Actualiser tout. Les macros et le VBA automatisent les actions qui ne relèvent pas du traitement de données : mise en page, envoi, création de fichiers par client. Pour les demandes ponctuelles, l’assistant d’IA intégré complète ces outils, comme l’explique notre guide Copilot dans Excel.
Ces outils ne sont pas indispensables pour être reconnu comme avancé, mais ils marquent la frontière avec le niveau expert. Les maîtriser suppose de connaître déjà les tableaux structurés et la logique des tableaux croisés dynamiques : c’est pourquoi ils viennent en dernier dans une progression.
Acquérir ces compétences avec une progression en trois étapes
- Consolider les fondamentaux : références absolues, SI, SOMME.SI.ENS, mise sous forme de tableau. Sans eux, les fonctions avancées produisent des erreurs difficiles à comprendre.
- Travailler chaque compétence sur un fichier métier : un journal de commandes, un suivi de factures ou un fichier RH réel (anonymisé) apprend plus qu’un exercice abstrait.
- Vous mettre en situation chronométrée : c’est la condition des tests de recrutement et des certifications.
La formation Excel CPF de CNFN suit cette logique sur environ 50 heures de vidéo. Son niveau Avancé (chapitres 8 à 16) couvre les fonctions avancées, RECHERCHEV, RECHERCHEX et INDEX/EQUIV, les tableaux croisés dynamiques, les simulations, la validation, les macros et la protection, et se termine par un examen blanc. Les chapitres métiers 24 à 26 appliquent ensuite ces outils à six cas pratiques guidés puis chronométrés, dont le suivi des commandes ADV et le rapprochement bancaire. Les exercices sont corrigés en visioconférence et les questions traitées par tickets.
Pour faire reconnaître ce niveau, la formation prépare la certification ICDL tableur (RS7528), enregistrée à France Compétences jusqu’au 27/02/2029, que vous pouvez passer au niveau Avancé : 36 questions en 35 minutes, réussite à 75 %. Le détail de l’épreuve est expliqué dans notre article sur la certification ICDL Excel. Elle est éligible au CPF ; le reste à charge éventuel peut être couvert par votre employeur, votre OPCO ou l’AIF de France Travail. Pour savoir si vous pouvez entrer directement au niveau Avancé, remplissez le formulaire de vérification des prérequis Excel.
Questions fréquentes sur Excel avancé
Qu’est-ce que le niveau Excel avancé ?
Le niveau avancé correspond à la capacité de croiser plusieurs tables, d’analyser des milliers de lignes et de fiabiliser un fichier partagé. Il recouvre en général RECHERCHEX ou INDEX/EQUIV, les fonctions de tableau dynamique comme FILTRE et UNIQUE, les tableaux croisés dynamiques, la validation des données, la protection et les outils de simulation. Power Query, Power Pivot et VBA relèvent plutôt du niveau expert.
Combien de temps faut-il pour atteindre un niveau Excel avancé ?
Cela dépend de votre point de départ et de votre pratique au quotidien. Avec des fondamentaux solides (références absolues, SI, SOMME.SI), comptez plusieurs semaines d’entraînement régulier sur des fichiers proches de votre métier. Une formation structurée évite surtout de passer à côté de notions comme la validation des données ou les scénarios, rarement découvertes seul.
Une formation Excel avancée est-elle finançable par le CPF ?
Oui, lorsqu’elle est référencée sur Mon Compte Formation et prépare une certification enregistrée à France Compétences. C’est le cas de la formation Excel CPF de CNFN, qui prépare la certification ICDL tableur RS7528 (Répertoire spécifique), passée au niveau Standard ou Avancé. Le CPF (Compte personnel de formation) peut être complété par un abondement de l’employeur, un financement OPCO (opérateur de compétences) ou l’AIF (aide individuelle à la formation) de France Travail.
Quelle version d’Excel faut-il pour les fonctions avancées ?
RECHERCHEX, FILTRE, UNIQUE, TRIER et LET sont disponibles dans Excel pour Microsoft 365, Excel 2021 et Excel 2024. Sur Excel 2019 ou une version plus ancienne, elles ne sont pas proposées : INDEX et EQUIV, les filtres avancés et les tableaux croisés dynamiques restent alors les outils de référence.
Comment prouver un niveau Excel avancé à un recruteur ?
Décrivez sur votre CV deux ou trois fichiers que vous avez construits et les fonctions utilisées, puis préparez-vous à un cas pratique en temps limité. Une certification apporte une preuve extérieure : l’ICDL tableur au niveau Avancé ou un score TOSA élevé situent votre niveau de façon reconnue.
Un profil Excel avancé se reconnaît moins au nombre de fonctions connues qu’à la fiabilité de ses fichiers : des recherches qui ne cassent pas, des synthèses actualisables et une saisie contrôlée. Si vous souhaitez travailler ces sept compétences sur des cas métiers et les valider par une certification, consultez le programme de la formation Excel CPF.
