Intermediate Excel Course

Lesson 1 • Custom Views and Display Settings

Configures freeze panes, custom views, and display options for large datasets. Improves usability when working with complex workbooks.

Lesson 2 • Named Ranges and Range Management

Defines named ranges to make formulas readable and maintainable. Supports cleaner formula construction in later chapters.

Lesson 3 • Navigating the Excel Ribbon

Covers ribbon tabs, groups, and contextual tools for efficient access. Builds the interface fluency needed for all subsequent chapters.

Lesson 4 • Workbook and Worksheet Management

Teaches creating, renaming, moving, and protecting sheets. Establishes organised file structures used throughout the course.

Lesson 1 • Lookup Functions: VLOOKUP and HLOOKUP

Introduces VLOOKUP and HLOOKUP for retrieving data from tables. Establishes lookup logic that is extended with advanced functions in the next chapter.

Lesson 2 • Date and Time Functions

Applies TODAY, NOW, DATEDIF, EDATE, and NETWORKDAYS to time-based calculations. Date functions support scheduling and deadline tracking in business models.

Lesson 3 • Text Functions for Data Cleaning

Teaches TRIM, CONCATENATE, LEFT, RIGHT, MID, and TEXTJOIN for manipulating strings. Clean text data is essential before analysis and reporting.

Lesson 4 • Logical Functions for Decision Making

Covers IF, AND, OR, and nested logical functions to automate decisions. Logical functions underpin conditional calculations used throughout the course.

Lesson 1 • INDEX and MATCH Combination

Teaches INDEX-MATCH as a flexible alternative to VLOOKUP for any-direction lookups. Builds the reference logic required for advanced data retrieval tasks.

Lesson 2 • XLOOKUP for Modern Lookups

Covers XLOOKUP syntax, default values, and match modes for cleaner lookups. XLOOKUP simplifies formulas that previously required INDEX-MATCH combinations.

Lesson 3 • Dynamic Array Functions

Introduces FILTER, SORT, UNIQUE, and SEQUENCE for spill-range outputs. Dynamic arrays automate list generation and replace manual copy-paste workflows.

Lesson 4 • Error Handling in Complex Formulas

Applies IFERROR, IFNA, and ISERROR to manage lookup failures gracefully. Robust error handling is critical in production workbooks shared with stakeholders.

Lesson 1 • Sorting and Filtering Data

Applies single-level, multi-level sort and AutoFilter for targeted data views. Sorting and filtering are prerequisites for accurate summarisation and reporting.

Lesson 2 • Data Validation Rules

Creates dropdown lists, numeric constraints, and custom validation formulas. Validation prevents entry errors that corrupt analysis results.

Lesson 3 • Importing and Transforming External Data

Imports CSV and text files and applies basic Power Query transformations. Prepares external data for analysis without manual reformatting.

Lesson 4 • Removing Duplicates and Inconsistencies

Uses Remove Duplicates, Flash Fill, and text functions to standardise messy data. Clean data is the foundation for reliable pivot tables and charts.

Lesson 1 • Advanced PivotTable Techniques

Covers GETPIVOTDATA, multiple consolidation ranges, and PivotTable options. These techniques support complex reporting scenarios encountered in professional settings.

Lesson 2 • Building Your First PivotTable

Walks through source data requirements, field placement, and layout options. Establishes the core PivotTable workflow used in all subsequent pivot sections.

Lesson 3 • Grouping and Filtering in PivotTables

Groups dates, numbers, and text items and applies slicers and timelines for filtering. Interactive filtering makes PivotTables suitable for executive dashboards.

Lesson 4 • PivotTable Design and Formatting

Applies report layouts, styles, subtotals, and number formats for polished output. Professional formatting ensures PivotTables are presentation-ready.

Lesson 1 • Combination and Secondary Axis Charts

Builds combo charts with dual axes to display two metrics with different scales. Combination charts are widely used in financial and operational reporting.

Lesson 2 • Choosing the Right Chart Type

Maps data relationships to appropriate chart types including bar, line, pie, and scatter. Correct chart selection prevents misleading visual representations.

Lesson 3 • Building and Editing Charts

Creates charts from worksheet data and edits series, axes, and data ranges. Editing skills allow charts to adapt as underlying data changes.

Lesson 4 • Sparklines and Dynamic Chart Techniques

Inserts sparklines for in-cell trend visualisation and links charts to dynamic ranges. These techniques create self-updating visuals for live dashboards.

Lesson 1 • Built-In Conditional Formatting Rules

Applies highlight cell rules, top-bottom rules, and data bars from the built-in gallery. Built-in rules provide fast visual cues for common analytical needs.

Lesson 2 • Managing and Prioritising Rules

Uses the Rules Manager to edit, reorder, and delete conflicting formatting rules. Proper rule management prevents unexpected formatting behaviour in shared workbooks.

Lesson 3 • Formula-Based Conditional Formatting

Creates custom rules using formulas to format entire rows or non-contiguous ranges. Formula-driven rules handle complex conditions that built-in rules cannot address.

Lesson 1 • Scenario Manager for Multiple Cases

Creates named scenarios with different input sets and generates summary reports. Scenario Manager documents best-case, worst-case, and base-case assumptions.

Lesson 2 • Goal Seek for Reverse Calculation

Uses Goal Seek to find the input value that produces a desired formula result. Reverse calculation supports pricing, break-even, and target-setting decisions.

Lesson 3 • Solver for Optimisation Problems

Configures Solver to find optimal solutions subject to constraints for resource allocation. Solver extends what-if analysis to multi-variable optimisation scenarios.

Lesson 4 • One-Variable and Two-Variable Data Tables

Builds data tables to display formula outputs across a range of input values. Data tables replace repetitive manual calculations in sensitivity analysis.