96 practical Excel lessons

A clear path from first formula to decision-ready spreadsheet work.

Spreadsheet foundations

Start confidently with cells, formulas, references, and repeatable worksheet habits.

  • Cells, rows, and columns — Navigate a worksheet and understand cell coordinates.
  • Arithmetic formulas — Calculate product revenue with a formula that stays connected to its inputs.
  • Copying formulas — Use relative references and the fill handle to repeat reliable logic.
  • Absolute and mixed references — Keep assumptions fixed while copying formulas across a model.

Everyday calculations

Summarise and understand everyday business numbers with dependable functions.

  • SUM, AVERAGE, and MAX — Summarise expenses and performance with readable functions.
  • AVERAGE — Find useful averages and recognise the effect of blanks, zeros, and outliers.
  • MIN and MAX — Identify the smallest and largest values and use them as quality checks.
  • COUNT and COUNTA — Count numeric records and non-empty records correctly.

Business decisions

Turn business rules into formulas that are readable, testable, and safe.

  • IF and business rules — Return different outcomes when a target is met or missed.
  • AND and OR — Combine requirements and alternative decision routes.
  • ROUND and percentages — Calculate proportions and control precision deliberately.
  • IFERROR — Handle expected formula errors without hiding real problems.

Logical tests and counting

Classify records, test data quality, and count outcomes against clear rules.

  • TRUE, FALSE, and comparison operators — Understand Boolean results and build precise comparisons.
  • COUNTIF — Count records that meet one text, number, date, or wildcard condition.
  • COUNTIFS — Count records that satisfy several criteria at the same time.
  • IS functions — Detect blanks, numbers, text, errors, and other data types.
  • IFS and SWITCH — Return one of several controlled outcomes without unreadable nesting.
  • Logic and count quality review — Combine tests and counts into a reconciled exception report.

Conditional summaries

Answer focused business questions with criteria-based totals, averages, and counts.

  • SUMIF — Total values that meet one condition.
  • SUMIF with a separate sum range — Separate the field being tested from the field being added.
  • SUMIFS — Summarise values using multiple text, number, and date conditions.
  • AVERAGEIF and AVERAGEIFS — Calculate averages for a defined group without helper columns.
  • Wildcards in criteria — Use *, ?, and ~ to match variable text safely.
  • Conditional summary case study — Build a multi-question report with control totals and documented criteria.

Selection and keyboard speed

Navigate, select, fill, and edit large worksheets with fewer mouse actions.

  • Arrow-key navigation — Move through a worksheet and jump to data boundaries efficiently.
  • Cell selection shortcuts — Extend selections one cell or one data region at a time.
  • Row and column selections — Select complete rows, columns, and contiguous worksheet regions.
  • Larger and non-adjacent selections — Work safely with large ranges and separate areas.
  • Fill Down and Fill Right — Copy checked formulas without dragging across thousands of cells.
  • Selection speed challenge — Combine navigation and selection shortcuts in a timed worksheet workflow.

Lookup foundations

Retrieve related information from reference tables with dependable matching rules.

  • VLOOKUP exact match — Return a related value from a vertical reference table.
  • VLOOKUP column control — Choose a stable return column and understand structural risk.
  • VLOOKUP approximate match — Match values to ordered bands such as rates, grades, or commissions.
  • HLOOKUP — Search across a horizontal table and return a value from another row.
  • Lookup error handling — Distinguish a missing key from a broken reference or wrong match mode.
  • Inventory lookup case study — Combine exact matches, error handling, and validation in an inventory report.

Modern and advanced lookups

Build flexible one-way, two-way, and multi-criteria retrieval logic.

  • XLOOKUP exact match — Retrieve values in either direction with explicit missing-result handling.
  • XLOOKUP match and search modes — Control exact, approximate, wildcard, and reverse searches.
  • MATCH and XMATCH — Return the position of a value for use in flexible formulas.
  • INDEX and MATCH — Combine a position with a return range for a durable lookup.
  • Two-way and multi-criteria lookups — Match both row and column dimensions or several business conditions.
  • Supplier pricing model — Connect supplier, region, volume, and effective-date reference tables.

Text extraction and cleanup

Standardise imported text and extract reliable codes, names, and categories.

  • LEN, LEFT, and RIGHT — Measure and extract predictable parts of codes and references.
  • MID — Extract characters from the middle of a structured text value.
  • FIND and SEARCH — Locate delimiters and keywords with or without case sensitivity.
  • TRIM, CLEAN, and non-breaking spaces — Remove common hidden characters and inconsistent spacing.
  • TEXTBEFORE, TEXTAFTER, and SUBSTITUTE — Split delimited values and replace known text patterns.
  • Customer data cleanup case study — Standardise names, references, phone fields, and email components.

Productivity shortcuts and editing

Edit, format, inspect, and organise workbooks with a fast, controlled workflow.

  • Cut, copy, paste, and Paste Special — Move values, formulas, formats, and operations deliberately.
  • Text and number formatting shortcuts — Apply readable formats without changing stored values.
  • Data and filter shortcuts — Open filters, tables, and data tools without leaving the keyboard.
  • Find, Replace, and Go To Special — Locate formulas, blanks, constants, and patterns safely.
  • Worksheets and workbook structure — Rename, move, group, protect, and colour-code worksheets.
  • Efficient editing workflow — Combine shortcuts into a repeatable cleanup and review process.

Statistical analysis and rounding

Describe distributions, rank results, and apply operational rounding rules.

  • MEDIAN and MODE — Describe typical values when averages are distorted or repeated values matter.
  • LARGE, SMALL, and ranking — Return ranked values and positions for performance analysis.
  • RANK, PERCENTRANK, and QUARTILE — Place results within a wider distribution.
  • FLOOR, CEILING, ODD, and EVEN — Round operational quantities to required pack sizes or intervals.
  • STDEV and variability — Measure how consistently values cluster around their average.
  • Statistical review case study — Build a compact distribution and outlier analysis with documented assumptions.

Dates and schedule logic

Build reliable timelines, ageing reports, deadlines, and working-day calculations.

  • DATE, YEAR, MONTH, and DAY — Construct and decompose dates without fragile text concatenation.
  • TODAY and NOW — Create reports that respond to the current date or time.
  • DATEDIF and DAYS — Measure elapsed time between milestones in appropriate units.
  • EDATE and EOMONTH — Move through calendar months and calculate reliable month ends.
  • WORKDAY and NETWORKDAYS — Calculate deadlines and durations that exclude weekends and holidays.
  • Schedule and ageing case study — Combine date logic into an overdue, ageing, and workload report.

Tables and PivotTables

Turn growing datasets into controlled tables and refreshable summaries.

  • Excel Tables and structured references — Create expanding datasets with readable calculated columns.
  • Table filters, totals, and slicers — Explore a table and produce controlled filtered totals.
  • PivotTable foundations — Summarise a larger dataset by category without manual formulas.
  • PivotTable grouping and calculations — Group dates and values and control how measures are displayed.
  • PivotCharts and slicers — Connect interactive filters to clear summary visuals.
  • PivotTable management pack — Build, refresh, format, and reconcile a multi-view PivotTable report.

Dynamic arrays and data shaping

Create live lists and transformation pipelines that resize automatically.

  • UNIQUE — Return a distinct list that stays current as source data changes.
  • FILTER — Return only records that meet one or several array conditions.
  • SORT, SORTBY, and SEQUENCE — Order dynamic results and generate structured series.
  • TAKE, DROP, CHOOSECOLS, and VSTACK — Reshape and combine arrays without helper worksheets.
  • Dynamic-array error handling — Control empty, mismatched, and blocked spill results.
  • Live operations tracker — Build an expanding, filtered, sorted worklist with live summary measures.

Reporting, dashboards, and visualisation

Turn accurate summaries into focused decision-ready reporting experiences.

  • Chart selection and design — Match column, line, bar, scatter, and combination charts to the question.
  • KPI calculations and variance — Build actual, target, variance, and variance-percentage measures.
  • Conditional formatting for insight — Use rules, scales, and formulas to surface exceptions without noise.
  • Interactive controls and validation — Create drop-downs and linked formulas for controlled dashboard choices.
  • Dashboard layout and accessibility — Organise hierarchy, spacing, colour, labels, and reading order clearly.
  • Executive KPI dashboard — Combine formulas, controls, charts, and narrative into one reviewed report.

Financial and operational modelling

Turn assumptions into forecasts, scenarios, investment decisions, and capacity plans.

  • Model structure and assumptions — Design clear input, calculation, output, and control sections.
  • Cash-flow forecasting — Build a monthly opening, movement, and closing cash schedule.
  • Scenario and sensitivity analysis — Compare best, base, and downside cases and test key drivers.
  • NPV, XNPV, IRR, and XIRR — Evaluate investments using dated cash flows and discount rates.
  • Capacity and operational planning — Convert demand, productivity, and availability into resource needs.
  • Investment case challenge — Build an integrated, scenario-driven model with controls and decision outputs.

Automation, auditing, and reusable models

Simplify complex logic, detect risk, document controls, and prepare workbooks for handover.

  • Named ranges and named formulas — Replace opaque references with controlled, meaningful names.
  • LET — Store intermediate values once and simplify complex formulas.
  • LAMBDA and reusable functions — Turn tested formula logic into a reusable workbook function.
  • Formula auditing and dependency tracing — Trace precedents, dependents, errors, and calculation inconsistencies.
  • Protection, documentation, and handover — Protect important logic and explain inputs, outputs, controls, and ownership.
  • Professional model review challenge — Audit, repair, simplify, document, and approve a complex business workbook.