Excel: From Advanced Data Analysis to Full Automation

This course is intended for proficient Excel users — including professionals, entrepreneurs, and analysts — who wish to acquire advanced-level skills.

1. Working knowledge of the Excel interface: navigating the user interface, manipulating rows, columns, cells, and sheets.
2. Working knowledge of different types of data and cell references.
3. Working knowledge of creating formulas (SUM, AVERAGE, VLOOKUP, ...).
4. Working knowledge of creating charts/graphs (column, line, pie, ...).
5. A Windows or Mac laptop with a pre-installed version of Office of at least version 2021 or Microsoft 365.

On completion of this course, participants will be able to :

• Transform and clean raw data from various sources using Get Data (Power Query).
• Implement complex calculations and lookups using advanced functions like XLOOKUP and LAMBDA.
• Analyse complex datasets by building dynamic Pivot Table reports with Slicers, Calculated Fields, and Pivot Charts
• Visualize data insights using advanced charts, trendlines, and formula-based conditional formatting.
• Automate repetitive Excel tasks by recording macros and developing simple VBA procedures using loops and conditions.

Day 1: Data Foundations & Formulas

• Refresher on data types and working with Excel Tables
• Importing data (CSV, TXT) and using Power Query for transformation
• Key text, date/time, logical, and error functions (e.g., IF, IFERROR)
• Modern lookup functions (XLOOKUP)
• Creating custom functions using LAMBDA
• Aggregation and conditional functions (e.g., SUMIFS, AVERAGEIFS)
• Using the Analysis ToolPak

Day 2: Pivoting, Charting & Introduction to Automation

• Building and customizing Pivot Tables
• Using Slicers, Timelines, Calculated Fields, and Pivot Charts
• Advanced charting: combo, waterfall charts, Sparklines
• Conditional formatting using formulas
• Macros: security settings, recording, running, and basic editing

Day 3: VBA, Capstone Challenge & Wrap-up

• VBA basics: syntax, dialog boxes, variables, and data types
• Writing conditional and loop structures (If...Then...Else, For...Next)
• Practical automation project
• Capstone challenge integrating Power Query, Pivot Tables, and Macros
• Review of solutions and course wrap-up

• Go beyond the basic formulas.
• Master advanced data analysis with Power Query, pivot tables, and modern functions like XLOOKUP and LAMBDA.
• Learn to automate repetitive tasks using Macros and VBA.

The training method is instructor-led, following a "demonstrate, practice, apply" model. This course uses a progressive approach, logically transitioning from data ingestion to analysis and automation.

Learners will assess their level of acquisition of new knowledge by completing a Capstone challenge during the second half of the afternoon of the 3rd day.

Certificate of Participation emitted by Digital Learning Hub

Participants will leave with a PDF version of the slides and a complete online Excel manual.

In-person training at the DLH premises, Terres Rouges building in Belval.