Advanced Excel: advanced functions, pivot tables and Power Query

This course is run on demand: in a virtual classroom or at your premises, on dates that suit you. Ask us for the next dates or a quote for your team.

Most users only tap a small part of Excel. This course saves you hours every week: make your files reliable, combine data, build dynamic analyses and automate repetitive imports with Power Query.

• Use advanced functions: XLOOKUP, nested IF, SUMIFS
• Structure data as tables
• Analyse with pivot tables
• Create readable charts and dashboards
• Import and clean data with Power Query

• Regular Excel users
• Assistants, administrators, controllers, sales staff
• Managers who produce tracking spreadsheets

Excel basics: data entry, simple formulas, formatting, sorting and filtering.

1. Module 1

   Advanced formulas

   • –Absolute references and names
   • –XLOOKUP, INDEX, MATCH
   • –Conditional and statistical functions

2. Module 2

   Reliable data

   • –Structured tables
   • –Data validation and drop-down lists
   • –Conditional formatting

3. Module 3

   Analysing

   • –Pivot tables
   • –Slicers and timelines
   • –Charts and a mini dashboard

4. Module 4

   Automating

   • –Power Query: import, clean, combine
   • –One-click refresh
   • –Introduction to macros and Copilot in Excel

Exercises on real cases, on your own files if you wish. Groups of up to 10 participants, in a virtual classroom or at your premises, with a practitioner trainer. For 30 days after the course, you can email your questions to the trainer.

• Corrected exercise files
• Advanced functions cheat sheet
• Dashboard template

Initial assessment, hands-on exercises and a final quiz. Certificate of attendance; Onarcle certificate of achievement from a 70% score on the final quiz.

Public course price per participant, materials included. In-company (up to 10 participants, programme tailored to your needs): €2,690 excl. VAT per group. 10% discount from 3 participants from the same organisation. If your employer or a funding body will pay, contact us and we will find the right arrangement.