Data Analysis with Excel Course

Lesson 1 • Cell Referencing and Selection

Introduces absolute, relative, and mixed cell references. Accurate referencing is the prerequisite for building reliable formulas in later chapters.

Lesson 2 • Navigating the Excel Environment

Covers the Ribbon, Quick Access Toolbar, and worksheet tabs. Establishes spatial awareness of the interface as the foundation for all subsequent tasks.

Lesson 3 • Printing and Page Layout Settings

Configures print areas, headers, footers, and page breaks. Properly formatted printouts ensure analysis results are presentable to stakeholders.

Lesson 4 • Workbook and Worksheet Management

Teaches creating, saving, and organising workbooks and sheets. Proper file structure prevents data loss and supports collaborative analysis workflows.

Lesson 1 • Structuring Data as Excel Tables

Converts ranges into formal Excel Tables with headers and structured references. Tables automate formatting and expand dynamically as data grows.

Lesson 2 • Filtering and Advanced Filtering

Uses AutoFilter and Advanced Filter to isolate relevant records. Filtering reduces dataset scope so analysts focus on meaningful subsets.

Lesson 3 • Data Validation and Input Controls

Restricts cell input using validation rules, drop-down lists, and error alerts. Validation enforces data integrity at the point of entry.

Lesson 4 • Removing Duplicates and Cleaning Data

Identifies and removes duplicate records and corrects inconsistent entries. Clean data is the prerequisite for accurate formulas and pivot analysis.

Lesson 1 • Conditional Aggregation Functions

Uses SUMIF, COUNTIF, AVERAGEIF, and their multi-criteria variants. These functions aggregate data by category without requiring pivot tables.

Lesson 2 • Logical Functions and Conditional Logic

Applies IF, AND, OR, NOT, and nested logic to categorise and flag data. Conditional logic enables dynamic outputs that respond to changing data values.

Lesson 3 • Date and Time Functions

Calculates durations, extracts date parts, and builds date-based conditions. Date functions are critical for time-series and deadline-driven analyses.

Lesson 4 • Arithmetic and Statistical Functions

Covers SUM, AVERAGE, MIN, MAX, COUNT, and COUNTA for basic quantitative analysis. These functions form the computational backbone of any data model.

Lesson 1 • XLOOKUP and Modern Lookup Functions

Uses XLOOKUP for simplified, flexible lookups with built-in error handling. XLOOKUP replaces VLOOKUP and INDEX-MATCH in modern Excel versions.

Lesson 2 • Error Handling in Lookup Formulas

Applies IFERROR, IFNA, and ISERROR to suppress and manage lookup errors. Robust error handling keeps dashboards clean when source data is incomplete.

Lesson 3 • INDEX and MATCH Combination

Combines INDEX and MATCH for flexible two-way lookups without column-order constraints. This pairing overcomes the key limitations of VLOOKUP.

Lesson 4 • Cross-Sheet and Cross-Workbook References

Links formulas across worksheets and external workbooks using structured references. Cross-file lookups consolidate data from multiple sources into one model.

Lesson 1 • Formatting Charts for Clarity

Applies titles, labels, legends, gridlines, and colour schemes to charts. Consistent formatting ensures charts meet professional presentation standards.

Lesson 2 • Sparklines and Conditional Formatting

Embeds sparklines in cells and applies conditional formatting rules for visual cues. These tools highlight patterns and outliers directly within data tables.

Lesson 3 • Building and Editing Charts

Creates charts from table data and edits series, axes, and data ranges. Hands-on chart construction reinforces the link between data structure and visual output.

Lesson 4 • Combination and Secondary Axis Charts

Builds combo charts with dual axes to compare metrics of different scales. Secondary axes allow overlaying trend lines on bar charts without distortion.

Lesson 1 • Building Your First PivotTable

Creates PivotTables from structured data and arranges fields in rows, columns, and values. The PivotTable layout directly controls how data is summarised.

Lesson 2 • Calculated Fields and Items

Adds custom calculations inside PivotTables using calculated fields and items. These extend PivotTable analysis beyond built-in aggregation functions.

Lesson 3 • Grouping and Filtering in PivotTables

Groups dates, numbers, and text fields and applies slicers and timelines for filtering. Dynamic filtering enables rapid exploration of different data segments.

Lesson 4 • Show Values As and Percentage Analysis

Displays values as percentages of totals, running totals, and rank comparisons. These options reveal proportional relationships hidden in raw aggregated numbers.

Lesson 1 • Advanced Conditional Aggregation

Uses SUMPRODUCT with Boolean arrays for multi-condition aggregation without helper columns. This technique handles complex criteria that SUMIFS cannot express.

Lesson 2 • Dynamic Array Functions Overview

Introduces SPILL behaviour and functions like UNIQUE, SORT, and FILTER. Dynamic arrays eliminate manual range adjustments when source data changes.

Lesson 3 • LET and LAMBDA Functions

Defines reusable variables with LET and custom functions with LAMBDA. These functions reduce formula complexity and enable modular formula design.

Lesson 4 • SEQUENCE and RANDARRAY Functions

Generates number sequences and random arrays for modelling and simulation tasks. These functions automate repetitive data generation in analytical templates.

Lesson 1 • Data Tables for Sensitivity Analysis

Builds one-variable and two-variable data tables to test formula sensitivity. Sensitivity tables reveal how outputs change across a range of input assumptions.

Lesson 2 • Solver for Optimisation Problems

Configures Solver to maximise, minimise, or meet targets subject to constraints. Solver handles resource allocation and scheduling problems beyond manual trial-and-error.

Lesson 3 • Goal Seek and Scenario Manager

Uses Goal Seek to back-solve for target values and Scenario Manager to compare multiple input sets. These tools support structured decision-making under uncertainty.

Lesson 4 • Descriptive Statistics with Data Analysis ToolPak

Generates descriptive statistics, histograms, and correlation matrices using the ToolPak. Built-in statistical outputs accelerate exploratory data analysis significantly.