Microsoft Excel Advanced

Microsoft Excel Advanced is designed for experienced Excel users who want to build more powerful, reliable analytical workbooks. The course develops advanced formula design, dynamic arrays, resilient lookups, LET formulas, advanced PivotTable analysis, repeatable Power Query workflows, Data Models and relationships, What-If Analysis, and reliable dashboard/workbook design.

👥 What you need

Learners should already be confident with core Excel formulas, Tables, filtering, basic PivotTables and normal workbook navigation. A supported Excel environment is recommended for practical work because advanced feature availability can vary by version, edition and platform.

⏱ Learning time

Allow approximately 1 hour 47 minutes, including teaching videos, self-study activities and the final assessment.

📘 Module 1 — Advanced Formula Design and Dynamic Arrays

Dynamic-array formulas can return multiple results from one formula and spill into neighbouring cells when space is available. FILTER returns records that meet defined criteria. Multi-condition logic can use Boolean expressions. If the spill area is blocked or linked workbooks are unsupported in the current configuration, errors may occur.

By the end of this module, learners will be able to:

✓ Use dynamic-array functions such as FILTER and understand spill behaviour.
✓ Build a FILTER formula that returns records for a selected region and category and handle a no-results case.

Workplace application: Build a FILTER formula that returns records for a selected region and category and handle a no-results case.

Controlled boundaries
Dynamic arrays do not eliminate the need for absolute or mixed references in all formulas. Availability depends on supported Excel versions.

📘 Module 2 — Advanced Lookup, Matching and Formula Architecture

XLOOKUP separates the lookup array from the return array, making left/right retrieval straightforward and reducing dependence on fixed column index numbers. Advanced formula architecture should also consider missing matches, readable logic and maintainability.

By the end of this module, learners will be able to:

✓ Build resilient lookup logic with XLOOKUP and deliberate error handling.
✓ Create an Employee ID lookup that returns Department and provides a controlled not-found message.

Workplace application: Create an Employee ID lookup that returns Department and provides a controlled not-found message.

Controlled boundaries
Do not present XLOOKUP as available in every Excel version. Older environments may require alternatives.

📘 Module 3 — LET and Maintainable Complex Formulas

LET assigns names to values or expressions inside a formula so repeated logic can be calculated and referenced more clearly. It can improve maintainability and may improve performance when repeated expressions are reused, but performance gains depend on the actual formula and dataset.

By the end of this module, learners will be able to:

✓ Use LET to name intermediate values and improve readability.
✓ Rewrite a repeated calculation using LET names for the source range, filtered result and final calculation.

Workplace application: Rewrite a repeated calculation using LET names for the source range, filtered result and final calculation.

Controlled boundaries
Do not promise a fixed speed improvement. Follow current Microsoft naming rules and version support.

📘 Module 4 — Advanced PivotTable Analysis

PivotTables summarise source data using functions such as Sum, Count and Average. Show Values As can present percentages or comparisons without changing the source. A structured source with unique headers improves reliability. Refresh is required when source data changes.

By the end of this module, learners will be able to:

✓ Use aggregation, Show Values As and structured sources for advanced summaries.
✓ Create a departmental sales PivotTable showing both total sales and percentage of grand total.

Workplace application: Create a departmental sales PivotTable showing both total sales and percentage of grand total.

Controlled boundaries
Avoid unsupported platform claims. Feature availability and interface placement vary across Excel versions and devices.

📘 Module 5 — Power Query for Repeatable Data Transformation

Power Query connects to sources, applies recorded transformations and loads the prepared result. Append combines rows from compatible queries; Merge combines columns based on matching keys. Applied Steps makes the transformation sequence reviewable and repeatable.

By the end of this module, learners will be able to:

✓ Create repeatable transformation workflows with Power Query.
✓ Connect to two monthly CSV files, standardise fields, append them and load the combined result.

Workplace application: Connect to two monthly CSV files, standardise fields, append them and load the combined result.

Controlled boundaries
Do not make unnecessary claims about operating-system components unless a specific documented connector requires them in the learner's environment.

📘 Module 6 — Data Models, Relationships and Power Pivot Concepts

The Data Model allows analysis across related tables. Relationships normally connect a unique key on one side to repeated matching values on the many side. Fields should use compatible data types. Power Pivot availability depends on Excel edition and platform.

By the end of this module, learners will be able to:

✓ Relate multiple tables using keys and understand the Data Model.
✓ Relate a Customers table to an Orders table using Customer ID and explain the one-to-many relationship.

Workplace application: Relate a Customers table to an Orders table using Customer ID and explain the one-to-many relationship.

Controlled boundaries
Do not assume Power Pivot features are available in every Excel environment. Keep DAX beyond the level needed to explain relationships.

📘 Module 7 — What-If Analysis, Scenarios and Decision Support

Goal Seek finds an input value needed to reach a known formula result and changes one input cell. Scenario Manager stores sets of input values, while Data Tables show the effect of one or two changing inputs on a formula. Solver is a separate add-in for more complex optimisation and should be treated as an extension rather than the core What-If set.

By the end of this module, learners will be able to:

✓ Use Scenarios, Goal Seek and Data Tables for structured decision support.
✓ Use Goal Seek to find the sales volume required to reach a target profit, then compare two saved scenarios.

Workplace application: Use Goal Seek to find the sales volume required to reach a target profit, then compare two saved scenarios.

Controlled boundaries
Keep Solver optional and clearly distinguish it from built-in What-If Analysis. Feature availability can vary.

📘 Module 8 — Designing Reliable Analytical Workbooks and Dashboards

Dynamic-array formulas can return multiple results from one formula and spill into neighbouring cells when space is available. FILTER returns records that meet defined criteria. Multi-condition logic can use Boolean expressions. If the spill area is blocked or linked workbooks are unsupported in the current configuration, errors may occur.

By the end of this module, learners will be able to:

✓ Combine robust data structure, transformations and clear presentation.
✓ Review an analytical workbook and identify structural risks such as merged data ranges, hard-coded totals and undocumented transformations.

Workplace application: Build a FILTER formula that returns records for a selected region and category and handle a no-results case.

Controlled boundaries
Dynamic arrays do not eliminate the need for absolute or mixed references in all formulas. Availability depends on supported Excel versions.