Excel 2026 - Intermediate

The Excel 365 Intermediate course builds on your foundational skills and takes you into the practical tools used for real-world data analysis. You’ll learn how to manage structured data, apply advanced sorting and filtering, validate and control inputs, import and clean external data, and create meaningful charts. The course also introduces PivotTables, lookups, and techniques for working with larger datasets, giving you the confidence to analyse information faster and more accurately.

Course Highlights

• Working confidently with structured data and Excel Tables
• Sorting, filtering, and applying advanced data criteria
• Using Data Validation to control input and improve accuracy
• Importing and cleaning data with modern Excel tools
• Creating clear, professional charts for data storytelling
• Building and customizing PivotTables and PivotCharts
• Managing large datasets with grouping and consolidation
• Using lookups (VLOOKUP, HLOOKUP, INDEX + MATCH) for smarter analysis alleen de module titels bold

Module 1 – Getting Started

Microsoft Excel 365 Overview

A quick refresher to bridge Beginner and Intermediate skills and introduce Excel 365’s modern interface, cloud features, and productivity tools.

Essential Keyboard Shortcuts

Expanded shortcut set to improve speed when navigating, formatting, and managing worksheets in Excel 365.

Module 2 – Working With Structured Data

What are Lists

Learn how Excel recognizes data blocks and how to work efficiently with structured ranges for sorting, filtering, and analysis.

Sorting a List

Use Excel 365’s enhanced Sort tools to reorder data by one or multiple columns and apply custom sort lists.

Advanced Multi-Level Sorting

Build multi-criteria sorts for more complex datasets, such as sorting by department, category, and date.

Custom Sort Options

Leverage advanced sorting rules including font color, cell color, and conditional icon sorting.

Filtering Data

Use AutoFilter and the improved Filter menus to instantly display only the information you need.

Converting Lists to Tables

Turn data ranges into Excel Tables for automatic formatting, structured references, and dynamic data updates.

Module 3 – Database-Style Functions

Introduction to Database Functions

Explore Excel’s D-functions for performing calculations based on criteria—ideal for database-like analysis.

DSUM

Sum values that match specific criteria within a database-style table.

DSUM with Single Criteria

Apply simple conditions to calculate totals for a targeted subset of records.

DSUM with Multiple Criteria

Combine several criteria to perform complex filtered totals.

DAVERAGE

Calculate the average of records that match selected criteria.

DCOUNT

Count numeric entries that meet defined conditions in a structured dataset.

SUBTOTAL

Perform flexible calculations—such as sum, average, or count—while automatically excluding hidden or filtered rows.

Module 4 – Data Validation & Entry Control

Dropdown Lists

Create dropdown selections to control data entry and ensure consistent, accurate input.

Decimal & Numeric Rules

Set boundaries for numerical inputs, including minimum/maximum values and decimal limits.

Input & Error Alerts

Provide helpful instructions via input messages and enforce correct data entry with customized error alerts.

Module 5 – Importing & Exporting Data

Importing Text & CSV Files

Use the modern “Get Data” tools to import text, CSV, and delimited files with automatic data detection.

Importing Data from Access and Other Sources

Connect to Access or other databases using Power Query for clean, repeatable extraction.

Exporting Data to Text Files

Export worksheet data into structured text formats for use in other applications.

Module 6 – Creating Charts

Building Basic Charts

Create column, line, pie, and bar charts using Excel 365’s streamlined charting interface.

Chart Tools & Ribbon

Access design and formatting controls to customise chart elements quickly.

Editing Chart Data

Add, remove, or modify data ranges to keep charts relevant and up to date.

Chart Formatting

Apply modern styles, adjust layouts, and enhance readability with refined formatting options.

Module 7 – PivotTables & PivotCharts

Understanding PivotTables

Learn how PivotTables summarize large datasets and provide instant insights.

Creating PivotTables

Build dynamic PivotTables using drag-and-drop fields for fast analysis.

Pivot Calculations

Use calculated fields and items to create custom formulas inside PivotTables.

Grouping Data

Group dates, numbers, and categories to simplify reporting.

Formatting PivotTables

Apply consistent styles, number formats, and layout adjustments.

Drill-Down Analysis

Explore underlying data by drilling through values to view source records.

PivotCharts

Visualize PivotTable results with automatically linked PivotCharts.

Filtering PivotTable Data

Learn how to filter PivotTable data to focus on relevant values, isolate trends, and create clearer, more meaningful reports easily fast.

PivotTable and Slicers

Use slicers for interactive, user-friendly filtering across multiple PivotTables.

Module 8 – Managing Large Datasets

Grouping Rows and Columns

Organize large worksheets by collapsing or expanding sections for easier navigation.

Data Consolidation

Merge data from multiple sheets or workbooks into a single summary using Consolidate tools.

Module 9 – Lookup & Reference Functions

VLOOKUP

Look up values vertically in structured tables—still widely used and essential.

HLOOKUP

Retrieve values based on horizontal lookup ranges.

INDEX & MATCH

Combine INDEX and MATCH for flexible, powerful lookups beyond VLOOKUP limitations.

XLOOKUP

A modern lookup function that finds a value in a range and returns the matching result. It’s more flexible than VLOOKUP and works in any direction with exact or approximate matches.

Module 10 – Additional Functions & Tools

FV (Future Value)

Calculate the future value of investments based on rate, periods, and payment patterns.

PMT

Determine loan or repayment amounts using interest rate and duration.

Hyperlinks

Create links to files, folders, web pages, and email addresses.

Text to Columns

Split data into separate columns based on delimiters or fixed widths.

Module 11 – Wrapping Up

Conclusion & Next Steps