Excel and Power Query

MS Excel: advanced level

2 days · 16 hours6–12 people

Build reports you can update without fragile formulas and manual fixes.

When reports combine multiple sources and support business decisions, manual copying creates errors. The workshop covers functions and models that improve quality and make refresh easier.

Outcomes

What you will learn

Modern lookups and dynamic arrays
Advanced PivotTables and PivotCharts
Relationships in a data model
What-if analysis and simulations
Validation, protection and error checks
Recording simple macros
Audience

Who this workshop is for

  • Analysts, financial controllers and accountants
  • People producing recurring reports
  • Managers analyzing sales, costs or operations
  • Confident Excel users
Course agenda

From knowledge to hands-on work

01

Day 1 · Formulas and data

  1. Named ranges and structured references
  2. VLOOKUP, INDEX + MATCH and XLOOKUP
  3. SUMIFS, COUNTIFS, nested conditions and IFERROR
  4. Dynamic arrays: FILTER, UNIQUE and SORT
  5. Text and date functions for data cleanup
  6. LET and LAMBDA for clearer formulas
  7. Data validation, dependent lists and formula-based conditional formatting
02

Day 2 · Analysis and reports

  1. PivotTables: grouping, calculated fields and showing values as
  2. Slicers, timelines and linked PivotTables
  3. Data model, relationships and introduction to Power Pivot
  4. Dashboards with charts and interactive controls
  5. Goal Seek, scenarios, data tables and Solver
  6. Protecting sheets, ranges and workbook structure
  7. Recording and running macros, plus a Power Query introduction
Final exercise

Build a report from several tables, add data checks and present the key findings in a dashboard.

What you take away

Named completion certificate, practice files with solutions and a reusable formula set.

Feature availability

Dynamic arrays, Power Pivot and other tools depend on the Excel version and configuration.

FAQ

Questions about this course

Is Power Query included?

We show when it helps. Full hands-on exercises are in the separate Power Query course.

Do macros require coding?

We teach simple recorded macros; VBA programming is not a prerequisite.

Excel and Power Query

Plan a workshop for your team

Tell us the group size and participants’ everyday tasks. We will agree on skill level, exercises and a suitable date.

Ask about training