Formation Excel Expert Gratuite
Power Query, VBA, formules matricielles et dashboards professionnels. Le niveau qui vous permet de créer des outils Excel que personne d'autre dans votre équipe ne sait faire.
Ce niveau suppose que vous maîtrisez le niveau intermédiaire (RECHERCHEX, TCD, MFC avec formules). Si ce n'est pas le cas, commencez par là. Ce niveau expert vous donne les compétences d'un développeur Excel freelance.
Objectif : Être capable de construire des outils Excel complets de l'import de données brutes jusqu'au rapport final automatisé. Ces compétences sont rares et très recherchées en entreprise, et constituent la base de l'activité d'un développeur Excel freelance.
Ce que vous maîtriserez
Power Query : transformer des données sans formule
Power Query est l'outil d'import et de transformation de données intégré à Excel depuis 2016. Il permet de connecter, nettoyer et restructurer n'importe quelle source de données sans écrire une seule formule et de rafraîchir le résultat en un clic.
Accéder à Power Query
- Onglet Données → groupe "Obtenir et transformer des données"
- Choisissez votre source : À partir d'un fichier (CSV, Excel, JSON), Du web, D'une base de données…
- L'éditeur Power Query s'ouvre chaque transformation que vous faites est enregistrée comme une "étape"
- Cliquez Fermer & Charger pour importer le résultat dans Excel
Transformations les plus utilisées
| Transformation | Comment | Utilité |
|---|---|---|
| Supprimer des lignes | Accueil → Supprimer les lignes | Enlever les lignes vides ou d'entête parasites |
| Filtrer les données | Flèche dans l'en-tête de colonne | Exclure des valeurs indésirables |
| Fractionner une colonne | Transformer → Fractionner | Séparer "Nom Prénom" en 2 colonnes |
| Fusionner des requêtes | Accueil → Fusionner des requêtes | Équivalent d'un RECHERCHEX entre 2 fichiers |
| Dépivoter les colonnes | Transformer → Dépivoter | Transformer une matrice en liste de lignes |
| Grouper par | Transformer → Grouper par | Calculer des totaux par catégorie |
Le vrai avantage : Toutes vos transformations sont enregistrées. La prochaine fois que vous recevez un nouveau fichier CSV de votre ERP, il vous suffit de cliquer Actualiser tout Power Query réapplique toutes les transformations automatiquement en quelques secondes.
- Importez-le dans Power Query (Données → À partir d'un fichier CSV)
- Supprimez les colonnes inutiles, renommez les colonnes en noms clairs
- Filtrez pour ne garder que les données de l'année en cours
- Transformez la colonne Montant en type Décimal et la colonne Date en type Date
- Chargez le résultat dans Excel vous avez un pipeline de nettoyage réutilisable !
🎯 À vous de jouer
Remettez dans l'ordre les étapes pour empiler les 12 fichiers mensuels d'un dossier.
Dans l'éditeur, dans quel ordre appliquer ces étapes à un export brut ?
Formules matricielles : FILTRE, TRIER, UNIQUE
Depuis Excel 365/2021, les formules matricielles dynamiques permettent de renvoyer plusieurs résultats dans plusieurs cellules à partir d'une seule formule. Elles remplacent avantageusement de nombreuses formules complexes.
=FILTRE extraire selon un critère
=TRIER trier dynamiquement
=UNIQUE liste sans doublons
- Sur votre base de ventes, créez une liste déroulante des régions avec
=UNIQUE(B2:B100) - Créez un rapport filtré avec
=FILTRE(A2:E100 ; B2:B100=H2 ; "Aucune vente")(H2 = votre liste déroulante) - Triez ce rapport par CA décroissant en combinant FILTRE + TRIER
- Ajoutez sous le rapport :
=SOMME(FILTRE(E2:E100 ; B2:B100=H2))pour le total de la région sélectionnée
🎯 À vous de jouer
En F2, additionnez le CA de la région Nord en combinant SOMME et FILTRE.
En F2, comptez le nombre de régions différentes.
=LET et =LAMBDA : des formules intelligentes
LET permet de nommer des calculs intermédiaires dans une formule pour la rendre plus lisible et plus rapide. LAMBDA permet de créer vos propres fonctions Excel personnalisées.
=LET nommer des variables dans une formule
=LAMBDA créer votre propre fonction
- Créez une fonction LAMBDA nommée "MARGE" qui calcule :
=LAMBDA(ca ; cout ; (ca-cout)/ca*100) - Utilisez =MARGE(C2 ; D2) dans votre tableau de ventes pour calculer la marge en %
- Avec LET, créez une formule qui calcule le bonus d'un commercial : si taux d'atteinte ≥ 120% → bonus de 15%, si ≥ 100% → 8%, sinon → 0%
🎯 À vous de jouer
En D2, calculez le prix TTC avec LET : nommez ht le produit B2*C2, puis renvoyez ht*1,2.
En D2, avec LET, nommez total la somme de B2:B6, puis renvoyez la part de B2 dans ce total, arrondie à 2 décimales.
Introduction au VBA : comprendre la programmation Excel
VBA (Visual Basic for Applications) est le langage de programmation intégré à Excel. Il permet d'automatiser n'importe quelle action que vous feriez à la main. Ce module vous donne les bases pour écrire vos premières lignes de code.
Ouvrir l'éditeur VBA
- Appuyez sur Alt + F11 l'éditeur VBE s'ouvre
- Dans le menu, faites Insertion → Module pour créer un nouveau module
- C'est ici que vous écrirez votre code
Structure de base Sub et End Sub
Manipuler des cellules en VBA
- Ouvrez l'éditeur VBA (Alt + F11) et créez un nouveau module
- Écrivez une macro qui écrit votre prénom en A1, votre nom en B1, et la date du jour en C1
- Pour la date :
Range("C1").Value = Now() - Exécutez la macro (F5 ou Outils → Macros) et vérifiez le résultat dans Excel
- Assignez la macro à un bouton : Insertion → Formes → dessinez un rectangle → clic droit → Affecter une macro
💡 Voir la macro complète
Sub RemplirInfo()
Range("A1").Value = "Votre Prénom"
Range("B1").Value = "Votre Nom"
Range("C1").Value = Now()
Range("C1").NumberFormat = "dd/mm/yyyy"
End Sub
🎯 À vous de jouer
Remettez dans l'ordre les étapes pour faire apparaître l'onglet Développeur.
Remettez les lignes de code dans l'ordre pour écrire « Bonjour » en A1 de la feuille active.
Macros VBA : automatiser des tâches répétitives
Maintenant que vous connaissez les bases du VBA, voici les structures de programmation qui permettent d'automatiser des tâches complexes : les conditions et les boucles.
If / Else les conditions en VBA
For / Next les boucles en VBA
- Créez une macro qui parcourt votre base de ventes et colorie en vert les lignes dont le CA > 5 000 €
- Ajoutez une condition : si le CA est entre 2 000 et 5 000 → orange ; en dessous → rouge
- À la fin de la macro, affichez un message "Mise à jour terminée X lignes traitées" (comptez avec une variable)
- Assignez cette macro à un bouton "Actualiser les couleurs" sur votre feuille
🎯 À vous de jouer
Remettez dans l'ordre la macro qui écrit « Relancer » en colonne D quand le montant en C dépasse 1 000.
Remettez dans l'ordre les étapes pour enregistrer une macro sans écrire de code.
Dashboard professionnel Excel : architecture complète
Un dashboard Excel professionnel suit une architecture claire : données brutes → traitement → visualisation. On ne mélange jamais les données et les visuels dans la même feuille.
Architecture recommandée
| Feuille | Contenu | Visibilité |
|---|---|---|
| _DATA | Données brutes importées via Power Query | Masquée |
| _CALC | Tableaux croisés dynamiques, calculs intermédiaires | Masquée |
| Dashboard | KPIs, graphiques, segments vue pour l'utilisateur | Visible |
| Paramètres | Dates, seuils, objectifs configurables | Visible (protégée) |
KPIs dynamiques lire les données du TCD
Technique pro : Protégez les feuilles _DATA et _CALC avec un mot de passe (Révision → Protéger la feuille). L'utilisateur ne peut qu'interagir avec le Dashboard. Son expérience est celle d'un vrai logiciel, pas d'un fichier Excel.
- Créez les 3 feuilles : _DATA (données brutes), _CALC (calculs), Dashboard (visuel)
- Dans _DATA, importez votre base de ventes via Power Query
- Dans _CALC, créez 3 TCD : CA par région, CA par mois, Top 5 vendeurs
- Dans Dashboard, créez les KPIs : CA Total, Croissance vs N-1, Meilleure région, Meilleur vendeur
- Masquez _DATA et _CALC (clic droit sur onglet → Masquer)
🎯 À vous de jouer
En D2, calculez le taux d'atteinte de l'objectif (CA réalisé / objectif), arrondi à 2 décimales.
En G2, renvoyez le nom du vendeur qui a fait la plus grosse vente, avec INDEX, EQUIV et MAX.
Projet final : outil Excel professionnel complet
Ce module final intègre tout ce que vous avez appris pour créer un outil Excel complet et livrable. C'est le type de projet que réalise un développeur Excel freelance pour ses clients.
- Import Power Query : connectez un fichier CSV de ventes (actualisable en 1 clic)
- Nettoyage automatique : supprimer doublons, corriger les types, filtrer l'année courante
- Formules matricielles : rapport filtré par vendeur avec =FILTRE + =TRIER + liste déroulante =UNIQUE
- TCD + graphiques : CA par région (histogramme), évolution mensuelle (courbe), répartition produits (camembert)
- Segments partagés : Région et Période contrôlent tous les graphiques simultanément
- KPIs : CA Total, Croissance, Meilleur vendeur, Taux de réalisation objectif
- Macro VBA : bouton "Actualiser tout" qui rafraîchit Power Query + TCD + recolorie les KPIs
- Protection : masquez les feuilles de calcul, protégez les cellules de formules
💡 Code VBA pour le bouton "Actualiser tout"
Sub ActualiserTout()
' Actualise Power Query
ThisWorkbook.RefreshAll
' Attend la fin du rafraîchissement
Application.CalculateUntilAsyncQueriesDone
' Message de confirmation
MsgBox "Données actualisées le " & Format(Now(), "dd/mm/yyyy à hh:mm"), vbInformation
End Sub
Félicitations ! Si vous avez complété tous les modules, vous maîtrisez Excel à un niveau professionnel. Ces compétences sont celles utilisées par les développeurs Excel freelances pour créer des outils sur mesure pour leurs clients. La prochaine étape : appliquer ces compétences sur vos propres fichiers professionnels.
🎯 À vous de jouer
Remettez dans l'ordre les étapes de construction d'un outil Excel complet.
Avant de livrer, en F2, vérifiez que le total des ventes par région égale le total général : renvoyez "OK" si SOMME(D2:D8) = la somme des 3 régions, sinon "Écart".
🎓 Terminer le niveau Expert
Réussissez toutes les activités avec au moins 60 % des points pour télécharger votre certificat. Ensuite, entraînez-vous sur un cas réel avec les ateliers pratiques.