All courses

Level 3

Intermediate Lookups & Data Summaries

Connect messy datasets, master XLOOKUP, and calculate multi-condition summaries with ease.

New course(0 reviews) 11 modules · 93 lessons 18 knowledge checks About 3.5 hours of hands-on practice
View pricing

What you’ll learn

  • Summarise data by condition with COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS
  • Connect tables with XLOOKUP and VLOOKUP, and handle missing matches and errors
  • Clean imported text and dates with text and date functions and Flash Fill
  • Control inputs with Data Validation and highlight insights with conditional formatting

Course overview

Most workplace spreadsheets involve more than one table, and this course teaches you to connect them. You'll summarise data by condition with COUNTIFS, SUMIFS and their cousins, and pull information from one table into another with XLOOKUP and VLOOKUP, including what to do when a match is missing.

Real data is rarely tidy, so you'll also clean imported text and dates with text and date functions and Flash Fill, handle errors gracefully, control what people can enter with Data Validation, and use conditional formatting to make the important numbers stand out.

How you’ll learn

Watch, then do it yourself

Short guided demos narrated by Kyle, then you repeat each step on the same live spreadsheet, right in your browser.

Instant feedback

“Check my work” grades your sheet the moment you finish, and knowledge checks every few lessons lock it in.

Help when you're stuck

Owl, your AI tutor, sees exactly what's on your sheet. Members can also message Kyle directly through 'Ask Kyle'.

Your course project

Northstar Solutions: Regional Performance Workbook

Northstar now has three tables that don't talk to each other: Sales, Products and Customers (exported from their old system, full of messy text and dates). You'll clean the imports, connect the tables with XLOOKUP, build a regional summary with SUMIFS/COUNTIFS, add dropdown-driven inputs with Data Validation, and highlight what needs attention with conditional formatting.

Every course in the academy works with the same company, so each project builds on the skills, and the data, from the last.

Course content

11 modules · 93 lessons · 18 knowledge checks
  • Interactive lesson
  • Step-by-step guide
  • Knowledge check

Where this course fits

Level 3 of the five-level core track. Each level builds on the one before it.

  1. Level 1Excel Foundations
  2. Level 2Essential Formulas & Everyday Logic
  3. Level 3Intermediate Lookups & Data SummariesYou are here
  4. Level 4Advanced Dynamic Arrays & Logic
  5. Level 5Expert Techniques & Workbook Architecture

Before you start

Level 2: Essential Formulas & Everyday Logic, or comfort with SUM, IF, absolute references and Excel Tables.

Who this course is for

  • Anyone who works with data from more than one table or system
  • People who have heard of XLOOKUP and want to use it with confidence
  • Learners ready to move past basic formulas

Meet your instructor

Kyle Stott

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade, consulting on and teaching best practices to over 400 companies across 30+ countries.

He built My Excel Academy so learners can practice in the same window they are learning in, with instant checks alongside an AI tutor, all while offering 1-on-1 instructor support when something doesn't add up.

Student reviews New course

This course doesn’t have reviews yet. Here’s what students say about the academy — rated 5.0 out of 5 across 3 reviews.

“My experience with My Excel Academy was excellent! The instructor was knowledgeable, engaging and provided plenty of hands-on practice. The course was perfect for beginners as it clearly outlined the workspace as well as some basic features that excel has to offer. I highly recommend this course for anyone looking to improve their excel skills or to begin their journey of learning excel.”
MarioMicrosoft Excel – Overview · April 21, 2023
“Really enjoyed this course! Packed full of useful insights. Even though I consider myself somewhat experience with Excel, I still took away new learnings.”
JackMicrosoft Excel – Overview · April 14, 2023
“I really appreciate the refresher of key basics. I use Excel daily and learned new tools, lingo and strategies. It’s tempting to skip past these foundational elements to tackle the sticky and more complex problems. With these basics in hand I’ll have a much easier time navigating the system and bringing efficiency to my work! Time to customize my Quick Access Toolbar!”
AleksandraMicrosoft Excel – Overview · April 13, 2023