Most people learn Excel by copying whatever the last person did. That works until someone sorts the wrong column, inserts a row above the totals, or the lookup quietly returns the wrong price.
Over two days your team rebuilds those habits properly. They turn loose ranges into tables, write references that copy correctly, summarise data with conditions, pick the right lookup for the job, and clean messy text and dates without retyping them. Everything is practised on one sales and operations workbook, and the course finishes with dynamic arrays and how to check a formula that Copilot wrote for you.
By the end, participants can
- Convert ranges into Excel Tables, use named ranges, and sort and filter data
- Apply relative, absolute and mixed references to build accurate, copyable formulas
- Summarise data with COUNT, SUMIF, AVERAGEIF and their multi-condition versions
- Make decisions in formulas using IF, AND, OR, IFS and error handling
- Retrieve data from large tables using XLOOKUP, VLOOKUP, HLOOKUP and INDEX MATCH, and choose the right one
- Clean and combine text using text functions, Flash Fill and Text to Columns
- Calculate deadlines and durations with date and time functions
- Use FILTER, SORT, UNIQUE and other dynamic array functions, and use AI assistance responsibly
- Set up worksheets for professional printing and presentation
Course outline
Day 1 · Build accurate formulas
01Excel Tables and Named Ranges
- Table versus range, and why tables are the foundation of good workbooks
- Table design: fields, records and structured references
- Sorting and filtering a table, and using slicers
- Creating, updating and removing named ranges
02Cell References and Formula Foundations
- Relative, absolute and mixed references
- Copying formulas correctly across rows and columns
- Common formula errors and what they mean
- Handling errors with IFERROR
03Summarising Data with Statistical Functions
- COUNT, COUNTA and COUNTBLANK
- COUNTIF and COUNTIFS
- SUMIF and SUMIFS
- AVERAGEIF and AVERAGEIFS
- Highlighting results with conditional formatting
04Logical Functions and Decision Making
- IF, AND and OR
- Nested IF, and simpler alternatives with IFS and SWITCH
- Combining logical functions with summary functions
- Building rules for discounts, approvals and exceptions
Day 2 · Look Up, Clean and Present
05Lookup Functions
- VLOOKUP and HLOOKUP, including exact and approximate match
- XLOOKUP: the modern, more flexible lookup
- INDEX and MATCH, and when to use them
- Choosing the right lookup and handling lookup errors
06Text Functions, Data Cleaning and Paste Special
- LEFT, RIGHT, MID and TRIM
- CONCATENATE, and the newer CONCAT and TEXTJOIN
- Flash Fill, Text to Columns and removing duplicates
- Paste Special: arithmetic operations, transpose and values only
07Date and Time Functions
- TODAY and NOW
- WORKDAY and NETWORKDAYS
- EDATE and EOMONTH
- Calculating ages, durations and deadlines
08Modern Excel: Dynamic Arrays and AI Assistance
- FILTER, SORT and UNIQUE for dynamic lists
- Spill ranges and how they update automatically
- Using AI assistance (such as Copilot in Excel, where licensed) to draft and explain formulas, and how to check the result
09Printing and Presentation
- Margins, orientation and page size
- Headers and footers
- Scaling to fit pages and print titles on every page