Advanced Excel training

Lesson 1 • Data Entry and Validation Basics

Apply AutoFill, Flash Fill, and basic data validation rules to accelerate accurate entry. Ensures clean input data before any analysis begins.

Lesson 2 • Workbook and Worksheet Management

Create, rename, colour-code, group, and protect worksheets within multi-sheet workbooks. Directly supports organised data architecture used throughout the course.

Lesson 3 • Keyboard Shortcuts and Navigation

Execute 40+ essential shortcuts for selection, movement, and formatting. Speed gains here compound across every later chapter's exercises.

Lesson 4 • Cell Referencing Fundamentals

Distinguish relative, absolute, and mixed references and apply F4 toggling. Correct referencing is the prerequisite for all formula work in later chapters.

Lesson 1 • Formula Construction Principles

Understand operator precedence, formula auditing tools, and error types. Provides the diagnostic mindset needed before introducing complex function nesting.

Lesson 2 • Mathematical and Statistical Functions

Apply SUM, AVERAGE, COUNT variants, MIN, MAX, and ROUND families to business datasets. These functions form the arithmetic backbone of all analytical models.

Lesson 3 • Text Manipulation Functions

Extract, combine, and clean text using LEFT, RIGHT, MID, TRIM, and CONCAT. Essential for standardising imported data before lookup or pivot operations.

Lesson 4 • Date and Time Functions

Calculate durations, deadlines, and fiscal periods using TODAY, DATEDIF, EOMONTH, and NETWORKDAYS. Enables automated scheduling and ageing analysis in business reports.

Lesson 1 • Find, Replace, and Special Cells

Use wildcard Find & Replace and Go To Special to locate and fix structural data issues. Enables bulk corrections that would take hours to perform manually.

Lesson 2 • Identifying and Removing Duplicates

Detect duplicate rows using conditional formatting and Remove Duplicates tool. Clean unique datasets are the prerequisite for accurate aggregation and reporting.

Lesson 3 • Text-to-Columns and Data Splitting

Parse delimited and fixed-width text into structured columns using the wizard and Flash Fill. Transforms exported system data into usable table format.

Lesson 4 • Advanced Data Validation Rules

Enforce numeric ranges, date constraints, and custom formula-based validation across datasets. Prevents downstream errors by blocking invalid entries at the source.

Lesson 1 • CHOOSE and Advanced Reference Tricks

Use CHOOSE for index-driven selection and combine with other functions for compact models. Completes the reference toolkit before moving into data analysis chapters.

Lesson 2 • INDIRECT, OFFSET, and Dynamic Ranges

Build references that update automatically as data grows using INDIRECT and OFFSET. Powers dynamic named ranges consumed by charts and pivot tables.

Lesson 3 • XLOOKUP and XMATCH Functions

Use XLOOKUP's return-array, if-not-found, and search-mode arguments for concise lookups. Represents the modern standard replacing both VLOOKUP and INDEX-MATCH.

Lesson 4 • VLOOKUP and HLOOKUP Deep Dive

Configure exact and approximate match lookups and handle common failure modes. Establishes lookup intuition before transitioning to more powerful alternatives.

Lesson 1 • Building Your First PivotTable

Insert a PivotTable, assign fields to rows, columns, values, and filters, and refresh data. Establishes the drag-and-drop mental model for all subsequent pivot work.

Lesson 2 • Value Field Settings and Calculations

Switch aggregation types and add calculated fields and items for custom metrics. Enables business KPIs that go beyond simple sums and counts.

Lesson 3 • Grouping, Filtering, and Slicers

Group dates into months and quarters, apply label and value filters, and add slicers. Transforms static summaries into interactive exploration tools.

Lesson 4 • PivotTable Design and Formatting

Apply report layouts, banded styles, and conditional formatting to pivot output. Professional appearance is essential for stakeholder-facing deliverables.

Lesson 1 • Dynamic Array Functions Overview

Understand spill behaviour and use UNIQUE, SORT, SORTBY, and FILTER for automatic output. Dynamic arrays eliminate manual list maintenance and reduce formula count.

Lesson 2 • LET and LAMBDA Functions

Define named variables inside formulas with LET and create reusable custom functions with LAMBDA. Dramatically reduces formula complexity and enables function libraries.

Lesson 3 • SUMIFS, COUNTIFS, and AVERAGEIFS

Apply multiple criteria across separate ranges to aggregate conditionally. These functions are the workhorses of financial and operational reporting models.

Lesson 4 • Array Formula Techniques

Apply legacy Ctrl+Shift+Enter arrays and modern implicit intersection for complex calculations. Completes the formula toolkit before financial modelling and dashboard chapters.

Lesson 1 • Dashboard Layout and Interactivity

Assemble charts, KPI cells, slicers, and form controls into a single-screen dashboard. Applies all visualisation skills into a professional deliverable for stakeholders.

Lesson 2 • Advanced Chart Types

Build waterfall, funnel, histogram, box-and-whisker, and sparkline charts for specialised analysis. Expands the visual vocabulary beyond standard bar and line charts.

Lesson 3 • Chart Selection and Best Practices

Match chart types to data relationships and audience needs using a structured decision framework. Correct chart choice prevents misinterpretation of business data.

Lesson 4 • Dynamic Charts with Named Ranges

Link charts to dynamic named ranges so visuals update automatically as data grows. Eliminates manual chart resizing in recurring monthly reports.

Lesson 1 • Variables, Loops, and Conditionals

Declare variables, iterate with For-Next and For-Each loops, and branch with If-Then-Else. These constructs transform single-step macros into intelligent automation.

Lesson 2 • VBA Editor and Code Structure

Navigate the VBE, understand modules and procedures, and read recorded code critically. Code literacy here enables confident editing and debugging in later sections.

Lesson 3 • Working with Ranges and Worksheets

Manipulate cells, ranges, and sheets programmatically using Range, Cells, and Sheets objects. Enables macros that process entire datasets without hardcoded cell addresses.

Lesson 4 • Macro Recorder and Security Settings

Record macros with absolute and relative references and configure trusted locations. Establishes safe automation habits before any code is written manually.