← Back to training
Training plan · Office Productivity · Advanced
Excel Advanced
Intensive 3-day training to become an Excel expert: Power Query, M language, advanced VBA, automation and development of a complete reporting system.
Duration
3 jours (21h)
Format
On-site
Prerequisites
Good command of pivot tables and advanced Excel functions
Audience
Advanced analysts, management controllers, BI experts
1
Day 1 — Power Query: Import & Transformation
1j- Power Query interface: editor, applied steps, preview
- Connections: Excel files, CSV, TXT, PDF, databases, web, folders
- Transformation: split columns, merge, pivot/unpivot, replace
- Cleaning: remove rows, sort, filter, conditional columns
- Merge and append: combine tables, append queries, merge queries
- Parameters and M functions: create reusable parameters, custom functions
- Intensive lab 1: automate import and cleaning of a monthly 100,000-row file
2
Day 1 — Advanced Power Query
- M language: syntax, types, records, lists, tables
- Custom M functions: creation, calling, parameterization
- Advanced conditional columns: multiple rules, auto-update
- Grouping and aggregation: Group By with multiple operations
- Query templates: reusable models, auto-refresh
- Performance: optimized queries, caching, load deferral
- Intensive lab 2: create a Power Query model for automated monthly reporting
3
Day 2 — Macros & VBA
1j- Macro recording: absolute/relative, optimization
- VBA editor: project, modules, Sub/Function procedures, execution
- Excel objects: Application, Workbook, Worksheet, Range, Cells
- Variables: declaration, types (Integer, Long, Double, String, Boolean, Variant)
- Structures: For Each, For Next, Do Loop, If, Select Case, With
- Interaction: InputBox, MsgBox, dialog boxes, simple UserForm
- Event handling: Workbook_Open, Worksheet_Change, BeforeClose
- Intensive lab 3: create a complete macro for import, cleaning and report generation
4
Day 2 — Advanced VBA & Automation
- Collections and arrays: dictionaries, dynamic arrays, collections
- Files: Open/Close, Read/Write, FileSystemObject, folders
- Email: create and send emails via Outlook from Excel
- PowerPoint: auto-generate presentations from Excel
- UserForms: custom forms, controls, validation, events
- Debugging: breakpoints, watches, step-by-step, error handling (On Error)
- Security: unlocking, digital signing, distributing macros
- Intensive lab 4: create a complete Excel app with UserForm, import and export
5
Day 3 — Final Project: Automated Reporting System
1j- Needs analysis: identify data sources and required reports
- Design: system architecture, data flow, output templates
- Development: Power Query (import), Power Pivot (model), VBA (automation), Pivot (analysis)
- Testing: validate with real data, handle errors and exceptions
- Documentation: user guide, flow diagram, maintenance procedure
- Presentation: demonstrate the complete end-to-end system
6
Day 3 — Certification & Deployment Plan
- Final project evaluation: functionality, performance, robustness
- Exam: develop an automation module in 3 hours
- Excel Advanced certification with VBA/Power Query validation
- Deployment plan: how to put the created system into production
- Access to expert community and ongoing resources
This plan is customizable
For BI experts and management controllers. Final project adapted to your real data and processes.