A Quick Guide to Excel Tables and Functions Course

Lesson 1 • Workbook and Worksheet Management

Teaches creating, saving, and organising workbooks and sheets. Proper file management prevents data loss and supports structured project workflows.

Lesson 2 • Cell Referencing and Navigation

Explains absolute, relative, and mixed references alongside navigation shortcuts. Reference mastery is critical for writing formulas correctly in later chapters.

Lesson 3 • Entering and Editing Cell Data

Introduces data types, cell editing techniques, and AutoFill. Accurate data entry is the prerequisite for reliable table and formula results.

Lesson 1 • Converting Data Ranges to Tables

Demonstrates how to select a range and apply the Table format using Insert or Ctrl+T. Students understand why tables outperform plain ranges for dynamic data.

Lesson 2 • Table Naming and Design Options

Covers assigning meaningful table names and using the Table Design tab. Named tables enable cleaner formulas and easier cross-sheet references.

Lesson 3 • Managing Table Rows and Columns

Teaches inserting, deleting, and resizing table rows and columns dynamically. Structural edits automatically extend formulas and formatting across the table.

Lesson 4 • Applying and Customising Table Styles

Explores built-in style gallery and custom style creation for professional presentation. Consistent styling improves readability and aligns with organisational standards.

Lesson 1 • Writing Formulas Inside Table Columns

Demonstrates entering formulas in calculated columns that auto-fill to all rows. Calculated columns eliminate repetitive formula entry and reduce errors.

Lesson 2 • Cross-Table and External References

Explains referencing one table from another sheet or workbook using structured syntax. Cross-table references support multi-table data models used in advanced analysis.

Lesson 3 • Understanding Structured Reference Syntax

Introduces the TableName[ColumnName] notation and its components. Structured references replace cell addresses, making formulas readable and automatically adjustable.

Lesson 1 • Date and Time Functions

Introduces TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF, and NETWORKDAYS for temporal calculations. Date functions enable deadline tracking and duration reporting.

Lesson 2 • Error Handling Functions

Explains IFERROR, IFNA, and ISERROR to trap and manage formula errors gracefully. Error handling prevents broken dashboards and misleading outputs in shared workbooks.

Lesson 3 • Logical Functions and Conditionals

Covers IF, AND, OR, NOT, and nested IF logic for decision-based outputs. Logical functions drive automated categorisation and flag-based reporting in tables.

Lesson 4 • Text Manipulation Functions

Teaches LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE, and TEXTJOIN for string operations. Text functions clean and reshape imported data before analysis.

Lesson 1 • XLOOKUP for Modern Data Retrieval

Introduces XLOOKUP as the successor to VLOOKUP with built-in error handling and flexible return arrays. Students adopt the current best-practice lookup function.

Lesson 2 • VLOOKUP and HLOOKUP Fundamentals

Teaches vertical and horizontal lookup syntax, match types, and common pitfalls. VLOOKUP remains the most requested lookup skill in professional Excel environments.

Lesson 3 • INDEX and MATCH for Flexible Lookups

Combines INDEX and MATCH to overcome VLOOKUP column-order restrictions. This pairing enables left-side lookups and multi-criteria retrieval in complex tables.

Lesson 1 • SUMIF and COUNTIF Essentials

Teaches single-criterion sum and count functions with range, criteria, and sum-range arguments. These functions answer the most common business aggregation questions quickly.

Lesson 2 • AVERAGEIF and AVERAGEIFS

Applies conditional averaging to measure performance metrics filtered by one or more criteria. Conditional averages reveal segment-level insights hidden in raw totals.

Lesson 3 • SUMIFS and COUNTIFS for Multiple Criteria

Extends single-criterion functions to handle two or more simultaneous conditions. Multi-criteria aggregation replaces complex nested IF formulas in reporting workflows.

Lesson 1 • Refreshing and Maintaining PivotTables

Explains manual and automatic refresh, source range updates, and PivotTable cache management. Reliable refresh workflows prevent stale data in shared reports.

Lesson 2 • Configuring Value Field Settings

Teaches sum, count, average, and percentage summary types within value fields. Correct value settings ensure PivotTable metrics match intended business calculations.

Lesson 3 • Creating a PivotTable from a Table

Walks through inserting a PivotTable using a named table as the source. Table-sourced PivotTables expand automatically when new rows are added to the source.

Lesson 4 • Slicers and Timelines for Interactivity

Adds slicers and timeline controls to enable one-click filtering of PivotTable data. Visual filters make PivotTable reports accessible to non-technical stakeholders.

Lesson 1 • LET and LAMBDA for Formula Reuse

Introduces LET for naming intermediate calculations and LAMBDA for creating custom reusable functions. These tools reduce formula complexity and enable formula-level code reuse.

Lesson 2 • UNIQUE and SEQUENCE Functions

Extracts distinct values with UNIQUE and generates number series with SEQUENCE for dynamic lists. Both functions eliminate manual deduplication and series-entry tasks.

Lesson 3 • TEXTSPLIT, TEXTBEFORE, and TEXTAFTER

Splits and extracts text strings dynamically without helper columns using modern text functions. These functions handle messy imported data that legacy text functions cannot parse cleanly.

Lesson 4 • Introduction to Dynamic Array Behaviour

Explains spill ranges, the spill operator (#), and how dynamic arrays differ from legacy array formulas. Understanding spill behaviour is prerequisite for all functions in this chapter.