Microsoft Excel — Advanced

Course Overview

This advanced course takes your Excel skills to a professional level. You will move beyond basic formulas into powerful data analysis tools: PivotTables and PivotCharts for instant data summarisation, advanced lookup formulas (INDEX/MATCH, XLOOKUP), logical and text functions, Power Query for data transformation, data validation and error handling, conditional formatting for visual analysis, and introduction to macros for repetitive task automation. You will build a complete interactive business dashboard from scratch, consolidating all the skills covered. Aligned to the MO-201 Microsoft Excel Expert exam.

What You Will Learn

• Build PivotTables and PivotCharts to summarise and analyse large datasets
• Write advanced lookup formulas: INDEX/MATCH, XLOOKUP, IFERROR
• Use logical functions: IFERROR, IFS, SWITCH, nested IF
• Use Power Query to import, transform, and clean data from multiple sources
• Apply data validation to control what users can enter in cells
• Create dynamic named ranges and structured Excel Tables
• Use conditional formatting to build visual heat maps and highlight KPIs
• Record and run basic macros to automate repetitive tasks
• Build a complete interactive management dashboard in Excel

Course Modules

• Advanced formulas review: IF, SUMIF, COUNTIF, AVERAGEIF — with multiple criteria
• Lookup functions: VLOOKUP vs INDEX/MATCH — when and why to use each
• XLOOKUP: the modern replacement — syntax, wildcards, match modes
• Error handling: IFERROR, IFNA, ISERROR — clean professional spreadsheets
• Array functions: SUMPRODUCT, and introduction to dynamic arrays (FILTER, SORT, UNIQUE)
• PivotTables: creating, grouping, slicers, timelines, calculated fields
• PivotCharts: combo charts, formatting, linking to PivotTable
• Data validation: dropdown lists, input messages, error alerts, custom formulas
• Excel Tables: structured references, auto-expand, table styles, naming
• Named ranges: creating, managing, and using in formulas
• Conditional formatting: data bars, colour scales, icon sets, custom formula rules
• Power Query (Get & Transform): importing CSV/Excel/web, transformation steps, M code basics
• Power Query: merging queries (joins), appending tables, data type correction
• Introduction to macros: recording, running, editing VBA code in the editor
• Building a dashboard: layout, KPI cards, dynamic charts, slicers, print-ready formatting
• MO-201 exam preparation and practice questions

Who Is This Course For?

This course is designed for finance managers, business analysts, operations staff, accountants, HR professionals, and anyone who regularly works with data in Excel and wants to move from 'good at Excel' to genuinely advanced. Ideal for those seeking MO-201 Microsoft Excel Expert certification.

Prerequisites

Completion of BITA Excel Foundational (or equivalent knowledge): you should be comfortable writing basic formulas, formatting cells, and creating simple charts before starting this course.

Delivery & Access

• Blended, online, onsite or on-demand delivery — your choice of format
• Hands-on labs with real environments
• Instructor Q&A support for 30 days
• Verified certificate of completion by BITA
• Study guides, practice questions and reference materials included

Related Courses