Modern Excel in Office 365

Smarter formulas. Connected data. Reports you can maintain with confidence.

Description

Move beyond repetitive spreadsheet work. This two-day programme combines modern formulas, refreshable data preparation and practical reporting.

Explore data modelling, dashboards and AI-assisted analysis, then apply the techniques to a workbook you can explain and maintain

Who is this Training Course for?

Finance professionals, data and business analysts, operations teams, Excel power users and internal trainers. Suitable for delegates who already use formulas, tables and PivotTables confidently.

Course Objectives

  • 01 Build reports that update as data changes using dynamic arrays and modern lookups.
  • 02 Write clearer formulas with LET and create reusable functions with LAMBDA.
  • 03 Clean and combine source data in repeatable, refreshable Power Query workflows.
  • 04 Connect tables in the Data Model and practise introductory DAX measures.
  • 05 Create interactive dashboards and explore Python-assisted analysis in Excel.
  • 06 Improve workbook collaboration, sharing, automation and handover

The Course Content

DAY 1
The modern formula engine


01. What changed in Excel 365

  • Dynamic arrays, spill ranges and structured references.
  • Diagnose #SPILL!, #CALC! and related calculation errors.


02. The dynamic array toolkit

  • FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and RANDARRAY.
  • Connect dynamic lists to charts and validation.


03. Modern lookups

  • XLOOKUP, XMATCH, two-way and multi-criteria lookups.
  • Return rows, columns or blocks; replace legacy patterns.


04. Clear, reusable formulas

  • LET, LAMBDA, MAP, REDUCE, SCAN, BYROW and BYCOL.
  • Create a shared custom-function library.


05. Text, arrays and patterns

  • TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, TAKE and DROP.
  • Clean text with regular expressions and keep calculations efficient.

 


DAY 2
Data, analytics and automation


06. Power Query: get and transform

  • Connect, clean, unpivot, merge, append and group data.
  • Build refreshable pipelines; explore parameters and performance.


07. The Data Model and DAX

  • Relationships, star schemas and introductory measures.
  • SUM, CALCULATE, DIVIDE, filter context and time intelligence.

08. Summaries and dashboards

  • GROUPBY, PIVOTBY, charts, slicers and dynamic titles.
  • Use conditional formatting and worksheet controls in a dashboard.

09. Python and AI-assisted analysis

  • PY, pandas, statistical summaries and visualisation.
  • Explore Copilot and Analyse Data; verify the findings.

10. Collaboration and governance

  • Co-authoring, Sheet View, version history and controlled sharing.
  • Explore Office Scripts and Power Automate workflows.

How will this Training Course be Presented?

Both scheduled cohorts are offered Face-to-Face and Virtual Live Online.