Advanced Excel Course

Lesson 1 • Cell Formatting Fundamentals

Apply number formats, alignment, borders, and cell styles. Ensures data is readable and professionally presented from the start.

Lesson 2 • Efficient Keyboard Navigation

Master shortcuts for moving, selecting, and jumping across cells. Reduces mouse dependency and accelerates all data manipulation tasks.

Lesson 3 • Data Entry and Autofill Techniques

Use Flash Fill, series fill, and custom lists for rapid data entry. Directly reduces manual input errors across all future worksheet tasks.

Lesson 4 • Workbook Management and File Options

Control save formats, AutoRecover, and workbook properties. Prevents data loss and ensures compatibility across different Excel versions.

Lesson 1 • Text Functions for Data Cleaning

Manipulate strings with LEFT, RIGHT, MID, TRIM, and CONCATENATE. Prepares raw imported data for analysis by standardising formats.

Lesson 2 • Logical Functions and Nesting

Build IF, AND, OR, and nested logic chains for conditional outputs. Enables decision-based calculations central to dashboards and reports.

Lesson 3 • Mathematical and Statistical Functions

Apply SUM, AVERAGE, COUNT, MIN, MAX, and rounding functions. These form the arithmetic backbone of financial and operational models.

Lesson 4 • Formula Syntax and Cell References

Distinguish relative, absolute, and mixed references and understand operator precedence. Correct reference behaviour is prerequisite to every formula in this course.

Lesson 1 • Dynamic Reference Functions

Use OFFSET, INDIRECT, and CHOOSE to build references that adapt to user input. Powers dynamic named ranges and interactive dashboard elements.

Lesson 2 • XLOOKUP and Modern Lookup Tools

Leverage XLOOKUP for cleaner syntax, default values, and reverse search. Represents the current best practice for lookup tasks in modern Excel.

Lesson 3 • VLOOKUP and HLOOKUP Mechanics

Construct exact and approximate match lookups with VLOOKUP and HLOOKUP. Understanding their limitations motivates the more powerful functions that follow.

Lesson 4 • INDEX and MATCH Combination

Use INDEX-MATCH for flexible two-way lookups without column-order dependency. Replaces VLOOKUP in complex models requiring left-side or multi-column retrieval.

Lesson 1 • Excel Tables and Structured References

Convert ranges to Tables and use structured references in formulas. Tables auto-expand and make formulas self-documenting, reducing maintenance overhead.

Lesson 2 • Removing Duplicates and Data Cleaning

Identify and eliminate duplicate records and standardise inconsistent entries. Clean data is prerequisite to accurate aggregation and lookup results.

Lesson 3 • Data Validation Rules

Restrict cell input using lists, numeric ranges, and custom formulas. Prevents entry errors at the source, protecting downstream analysis integrity.

Lesson 4 • Sorting and Multi-Level Filtering

Sort by multiple columns and apply advanced filter criteria. Enables rapid data segmentation required before aggregation or reporting.

Lesson 1 • Power Query Interface and Connections

Navigate the Power Query Editor and connect to diverse data sources. Understanding the interface is prerequisite to building any transformation pipeline.

Lesson 2 • Merging and Appending Queries

Combine datasets using merge (join) and append operations. Replicates SQL-style joins without writing code, enabling multi-source data consolidation.

Lesson 3 • Transforming and Shaping Data

Apply column transformations, data type changes, and row filtering in Power Query. Each step is recorded and replayable, ensuring reproducible data preparation.

Lesson 4 • Grouping and Aggregating in Power Query

Use Group By to summarise data before loading to Excel. Pre-aggregation reduces worksheet size and improves PivotTable and formula performance.

Lesson 1 • Building and Configuring PivotTables

Create PivotTables from structured data and arrange fields for meaningful summaries. Field placement decisions directly determine the analytical value of the output.

Lesson 2 • Calculated Fields and Items

Insert custom calculations directly inside PivotTables using calculated fields. Extends analytical capability beyond raw source data columns.

Lesson 3 • Advanced PivotTable Techniques

Use GETPIVOTDATA, multiple consolidation ranges, and PivotTable options. Unlocks programmatic access to pivot results for use in external formulas.

Lesson 4 • PivotChart Creation and Formatting

Generate PivotCharts linked to PivotTables and format them for presentation. Visual summaries communicate trends that tabular data alone cannot convey.

Lesson 1 • Sparklines and In-Cell Visuals

Insert sparklines and icon sets for compact, in-cell trend indicators. Adds visual context to tables without requiring separate chart objects.

Lesson 2 • Chart Formatting and Design Standards

Apply consistent colours, fonts, gridlines, and labels to charts. Professional formatting increases credibility and reduces cognitive load for audiences.

Lesson 3 • Combination and Secondary Axis Charts

Overlay two chart types on one canvas using a secondary axis. Enables comparison of metrics with different scales in a single visual.

Lesson 4 • Chart Types and Selection Principles

Match chart types to data relationships: comparison, trend, composition, distribution. Correct chart selection prevents misrepresentation of analytical findings.

Lesson 1 • Statistical Analysis Functions

Apply FORECAST, TREND, CORREL, and regression tools for predictive analysis. Extends Excel beyond reporting into quantitative forecasting and correlation work.

Lesson 2 • Financial Modelling Functions

Calculate NPV, IRR, PMT, and depreciation for investment and loan models. Provides the function toolkit required for professional financial analysis.

Lesson 3 • Solver for Optimisation Problems

Configure Solver to maximise, minimise, or constrain objective functions. Applies linear and nonlinear optimisation to resource allocation and planning models.

Lesson 4 • What-If Analysis Tools

Use Goal Seek, Scenario Manager, and Data Tables for sensitivity analysis. Quantifies how input changes affect outcomes, supporting evidence-based decisions.