Course preview

Microsoft Excel Advanced

Move beyond basic spreadsheets and learn to calculate, organise, explore, visualise and report business data with confidence.

2 full days9 practical lessons70% hands-on practiceMixed Excel versions

What you will gain

From formulas to interactive dashboards

Every lesson connects an Excel feature to a realistic workplace task. Participants build skills progressively, using sales and operational data throughout the course.

Calculate accuratelyBuild reusable formulas, conditional calculations and advanced lookups.

Organise efficientlyStructure, sort, filter, group and consolidate growing datasets.

Explore possibilitiesTest assumptions with Data Tables, Goal Seek, Scenarios and Solver.

Communicate insightCreate clear charts, PivotTables, PivotCharts and simple dashboards.

Two-day journey

A practical progression

Day 1

Build and organise

Formulas, conditional functions, Excel Tables, charts, grouping and subtotals.

Day 2

Analyse and report

What-If tools, advanced functions, consolidation, PivotTables and PivotCharts.

Curriculum

Preview all nine lessons

Select any lesson to see its key skills and practical activity.

01Performing CalculationsFormulas, references, functions and error checking+

Build dependable worksheet calculations and understand how Excel evaluates them.

Arithmetic operators and parentheses
Relative, absolute and mixed references
Insert and nest functions
Fill, copy and reuse formulas
Named ranges and formula auditing
Common errors such as #REF! and #DIV/0!

Practice preview: Calculate gross sales, discounts, tax and final invoice values while locking the tax rate with an absolute reference.

02Working with FunctionsConditional totals, counts and averages+

Turn business questions into conditional calculations using one or several criteria.

COUNTIF and COUNTIFS
SUMIF and SUMIFS
AVERAGEIF and AVERAGEIFS
Text, number and date criteria
Wildcards and comparison operators
Troubleshooting criteria ranges

Practice preview: Produce a regional performance summary with order counts, total sales, average sales and selected date-range results.

03Organising Data with TablesStructured data, sorting, filtering and duplicates+

Transform an ordinary range into a flexible Excel Table that grows with your data.

Create and name Excel Tables
Styles, Total Row and calculated columns
Structured formula references
Multi-level and custom sorting
Advanced Filter and unique records
Safe duplicate removal

Practice preview: Convert retail transactions into a named Table, calculate Sales, filter high-value orders and extract unique customers.

04Visualising Data with ChartsClear comparisons, trends and relationships+

Choose a chart that fits the question, then format it for clear business communication.

Column, bar, line and area charts
Pie, doughnut and scatter charts
Combo charts and different scales
Titles, labels, legends and axes
Source ranges and data series
Charts that expand with Tables

Practice preview: Create regional, monthly and sales-versus-quantity charts, including one that expands automatically.

05Getting the Most from Your DataOutlines, grouping and subtotals+

Make long worksheets easier to navigate by revealing summary or detailed views on demand.

Automatic worksheet outlines
Manual row and column groups
Nested groups and summary levels
Regional and category subtotals
Multiple subtotal calculations
Copy visible summary results

Practice preview: Build regional and category summaries, then switch between grand totals, group totals and full detail.

06What-If AnalysisData Tables, Goal Seek, Scenarios and Solver+

Explore alternative assumptions and find inputs that achieve a specific business objective.

One- and two-variable Data Tables
Goal Seek for target values
Best, expected and worst scenarios
Scenario summary reports
Solver objectives and decision variables
Constraints and optimisation methods

Practice preview: Find a target sales quantity, compare price-and-volume combinations and maximise profit within operational limits.

07Advanced Excel TasksArrays, lookups, logic, links and consolidation+

Combine advanced calculation and workbook tools to solve multi-step spreadsheet tasks.

Legacy and dynamic array formulas
VLOOKUP and XLOOKUP
INDEX with MATCH
IF, AND, OR, IFS and IFERROR
Cross-sheet links and consolidation
Hyperlinks and workbook navigation

Practice preview: Build product lookups, classify transactions, consolidate monthly sheets and create a navigation page.

Version note: Microsoft 365 users can use dynamic arrays, XLOOKUP and modern co-authoring. Compatible alternatives are demonstrated for older Excel versions.

08Pivoting DataFast summaries with PivotTables, slicers and timelines+

Rearrange the same dataset into different analytical views without rewriting formulas.

Create and rearrange PivotTables
Filter, sort, group and drill down
Show percentages, differences and ranks
Report layouts and number formats
Refresh and source-data changes
Slicers and date timelines

Practice preview: Analyse sales by region and product, identify top performers, group dates and add interactive filters.

09Charting Pivoted DataInteractive PivotCharts and dashboards+

Convert PivotTable analysis into visual reports that respond to filters and changing questions.

Create a PivotChart
Move fields between chart areas
Change chart types and layouts
Format titles, labels and axes
Filter with slicers and timelines
Arrange a simple dashboard

Practice preview: Assemble a compact sales dashboard with coordinated PivotCharts, slicers and a timeline.

Learning approach

See it. Try it. Apply it.

1. Guided demonstration

The instructor explains the business problem and demonstrates the relevant Excel workflow.

2. Hands-on activity

Participants reproduce the technique using a prepared dataset and guided instructions.

3. Practical challenge

A short task checks whether participants can select and apply the right feature independently.

Ready to work smarter in Excel?

This course is designed for users who already understand basic worksheets and want practical, job-relevant analytical skills.