Microsoft Excel Intermediate

Microsoft Excel Intermediate builds on basic spreadsheet skills and moves learners into structured workplace analysis. Across eight teaching Modules, learners work with robust Tables, logical functions, XLOOKUP, reusable formulas, Data Validation, Conditional Formatting, PivotTables, PivotCharts and introductory Power Query.
The course is designed for learners who already understand basic Excel navigation, data entry, formatting and simple formulas. It keeps the focus on practical analysis rather than macros, VBA or advanced data modelling.

👥 What you need

Learners should already be comfortable with basic Excel navigation, data entry, formatting and simple formulas. A suitable current Excel environment is recommended for practice.

⏱ Learning time

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

📘 Module 1 — Building Robust Tables and Structured Data

Use one clear header row, consistent fields and one record per row. Avoid merged cells inside analysis ranges and remove unnecessary blank rows. Convert the clean range to a Table so formulas, filters and downstream analysis can work from a structured source.

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

✓ Prepare analysis-ready tabular data and convert it into Excel Tables.
✓ Clean a VAT transaction list and convert it to a Table with Date, Transaction ID, Net, VAT and Total fields.

Workplace application: Clean a VAT transaction list and convert it to a Table with Date, Transaction ID, Net, VAT and Total fields.

📘 Module 2 — Logical Functions and Error Handling

Logical functions test conditions and return results based on TRUE/FALSE logic. IF handles one condition, AND/OR combine tests, IFS can evaluate multiple conditions, and IFERROR can return an alternative result when a formula evaluates to an error. Error handling should not hide data-quality problems that need investigation.

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

✓ Use IF, AND, OR, IFS and IFERROR appropriately.
✓ Create a bonus-eligibility formula using IF with AND and use IFERROR only to handle a genuine calculation error.

Workplace application: Create a bonus-eligibility formula using IF with AND and use IFERROR only to handle a genuine calculation error.

📘 Module 3 — Lookup and Reference Functions with XLOOKUP

XLOOKUP searches a lookup array and returns a corresponding value from a return array. Exact match is the normal default when match_mode is omitted. It can return values from either side of the lookup field because lookup and return arrays are defined separately.

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

✓ Retrieve values using XLOOKUP and understand its version boundary.
✓ Return a Product ID from a Product Name using XLOOKUP, then test the result with an unknown product.

Workplace application: Return a Product ID from a Product Name using XLOOKUP, then test the result with an unknown product.

📘 Module 4 — Working with Text, Dates and Reusable Formulas

Relative references such as A1 adjust when copied; absolute references such as $A$1 remain fixed; mixed references lock one dimension. Reusable formulas can also use text and date functions where appropriate to clean or present data. 3-D references can summarise the same cell across consistently structured worksheets, but should be used carefully because sheet order and structure matter.

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

✓ Build reusable formulas using appropriate references plus useful text/date techniques.
✓ Create a monthly summary formula using a fixed tax rate, a date calculation and one simple text-cleaning step.

Workplace application: Create a monthly summary formula using a fixed tax rate, a date calculation and one simple text-cleaning step.

📘 Module 5 — Data Validation and Conditional Formatting

Data Validation can restrict or guide entries, including list-based choices. Conditional Formatting applies visual rules such as colours, data bars or icons based on values or formulas. Rules should be designed so that users understand what the visual cue means.

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

✓ Reduce entry errors and add visual cues to important values.
✓ Create an inventory status drop-down and apply a simple rule to highlight low stock.

Workplace application: Create an inventory status drop-down and apply a simple rule to highlight low stock.

📘 Module 6 — Analysing Data with PivotTables

PivotTables summarise source data without changing the original records. Learners add fields to the required areas, choose a suitable aggregation and refresh the PivotTable when source data changes. If numeric data is stored as text, a PivotTable may count rather than sum it.

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

✓ Summarise structured data using Rows, Columns, Values and Filters.
✓ Summarise travel expenses by category and department and show one value as a percentage of the grand total.

Workplace application: Summarise travel expenses by category and department and show one value as a percentage of the grand total.

📘 Module 7 — PivotCharts and More Effective Visual Analysis

A PivotChart visualises a PivotTable and responds to its filtering. Slicers provide a visible, interactive way to filter supported PivotTable fields. Choose chart types that communicate the question clearly and avoid unnecessary visual clutter.

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

✓ Connect PivotCharts and slicers to PivotTable analysis.
✓ Create a regional sales PivotChart and add a slicer for Region.

Workplace application: Create a regional sales PivotChart and add a slicer for Region.

📘 Module 8 — Introduction to Power Query for Cleaning and Combining Data

Use one clear header row, consistent fields and one record per row. Avoid merged cells inside analysis ranges and remove unnecessary blank rows. Convert the clean range to a Table so formulas, filters and downstream analysis can work from a structured source.

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

✓ Understand the basic Power Query workflow for repeatable data preparation.
✓ Connect to a CSV, remove an unused field, set the Amount column to a suitable data type and load the result to a worksheet.

Workplace application: Clean a VAT transaction list and convert it to a Table with Date, Transaction ID, Net, VAT and Total fields.