← Back to training
Training plan · Office Productivity · Intermediate
Excel Intermediate
Intensive 3-day training to master advanced functions, pivot tables, Power Pivot and professional dashboard creation.
Duration
3 jours (21h)
Format
On-site
Prerequisites
Good command of basic Excel formulas (IF, SUMIF, VLOOKUP)
Audience
Analysts, accountants, managers
1
Day 1 — Advanced Functions
1j- VLOOKUP: exact, approximate, dynamic column, error handling
- XLOOKUP: replacing VLOOKUP/HLOOKUP, multiple search, default values
- INDEX + MATCH: the powerful bidirectional search combination
- OFFSET, INDIRECT: dynamic ranges, indirect references
- Conditional functions: IFS, SWITCH, MAXIFS, MINIFS
- Database functions: DSUM, DCOUNT, DMIN, DMAX
- Intensive lab 1: build a multi-criteria search system with INDEX/MATCH and XLOOKUP
2
Day 1 — Text, Date & Logic Functions
- Text: LEFT, RIGHT, MID, LEN, SUBSTITUTE, TRIM, UPPER, TEXT
- Date: TODAY, NOW, DATEDIF, EDATE, EOMONTH, WEEKNUM, WEEKDAY
- Logic: AND, OR, NOT, XOR, IFS, SWITCH
- Information: ISNUMBER, ISTEXT, ISERROR, TYPE, CELL
- Intensive lab 2: create a data cleaning system with text and date functions
3
Day 2 — Pivot Tables
1j- Pivot table creation: layout, rows/columns/values/filters, recommended
- Customization: calculated fields, calculated items, formatting
- Grouping: dates (month, quarter, year), numbers (ranges), text
- Slicers: connect to multiple pivot tables, timelines, style
- Pivot charts: creation, synchronization, customization
- Power Pivot: data models, relationships, simple DAX measures
- Intensive lab 3: analyze 50,000 rows of sales data with pivot tables, slicers and Power Pivot
4
Day 2 — Advanced Charts & Visualization
- Combination charts: column + line, dual axis
- Advanced charts: waterfall, funnel, map, radar, gauge, histogram
- Advanced conditional formatting: multiple rules, custom formulas
- Dashboards: design, KPIs, indicators, alignment, printing
- Interactive elements: slicers, form controls, dropdowns
- Intensive lab 4: create an interactive executive dashboard with 6 KPIs
5
Day 3 — Intensive Practical Projects
0,5j- Project A: analyze annual sales (pivot tables, slicers, charts, KPIs)
- Project B: create a complete HR dashboard (headcount, turnover, absences, payroll)
- Project C: automate monthly reporting with Power Pivot and DAX measures
6
Day 3 — Assessment & Certification
0,5j- Real scenario: analyze a real dataset and present insights
- Practical exam: create a pivot table + dashboard in 1h30
- Excel Intermediate certificate with detailed skills grid
- Progression plan toward Advanced level (Power Query, VBA)
This plan is customizable
Ideal for analysts and managers handling large data volumes.