Your cart is empty.
Keep shoppingExcel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY
Role Play
13,360 students
Excel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY
Build Excel tables and write SUMIF, date, text and XLOOKUP formulas - and stop rebuilding reports by hand
Created byIan Littlejohn
Last updated 8/2026
English
Greek [Auto],English [Auto],
What you'll learn
- Create, format and filter Excel tables so your data answers questions instead of just sitting there
- Aggregate table data with Sum, Count, Average, Max and Min, and filter it visually with Slicers
- Apply conditional formatting - cell rules, Top 10 analysis, data bars, color scales and icon sets
- Write SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS to calculate on filtered data
- Use date formulas - YEAR, MONTH, WEEKDAY, WEEKNUM, NETWORKDAYS, WORKDAY, DATEDIF and EOMONTH - for time and date intelligence
- Repair a broken date field that Excel refuses to recognize, a problem almost every real dataset has
- Manipulate text with LEFT, MID, RIGHT, TRIM and SUBSTITUTE, plus TEXTBEFORE and TEXTAFTER
- Build IF logic and lookups with IF, VLOOKUP and HLOOKUP, then replace them with XLOOKUP
- Use the newest Excel functions - GROUPBY, PIVOTBY, VSTACK, HSTACK, TOCOL and TOROW - to summarize and reshape data without a PivotTable
- Practice on real sales data with 22 downloadable files, practical activities and full worked answers
Explore related topics
Course content
10 sections • 67 lectures • 4h 26m total length
Requirements
- Excel for Microsoft 365 is recommended. Most of the course runs on Excel 2016 or later.
- The New Functions section - GROUPBY, PIVOTBY, VSTACK, HSTACK, TOCOL and TOROW - and the TEXTBEFORE and TEXTAFTER lectures need Excel for Microsoft 365 or Excel 2024. XLOOKUP needs Excel for Microsoft 365 or Excel 2021 or later. Everything else works from Excel 2016.
- You should be comfortable entering data into a spreadsheet. No formula experience is assumed - the course starts at the beginning.
- The course is recorded on Excel for Windows.
Description
This course contains the use of artificial intelligence.
Every lesson in this course is written, created and recorded by me. AI is used only to help produce supporting images and written materials around the lessons.
Which three products slipped last quarter? Which customers have not ordered in ninety days? How many working days is that invoice overdue?
Those are the questions someone asks you, and the answer is already sitting in your spreadsheet. This course is about getting it out - with tables, conditional formatting and the formulas that do the actual work.
It is the starting point of my Excel series, and it assumes nothing beyond being able to type data into a sheet.
WHAT YOU WILL BUILD
Tables
Create and format Excel tables, and filter text, numeric and date fields
Sum, Average, Count, Max and Min inside a table
Use Slicers to filter visually
Understand table syntax, so your formulas keep working when the data grows
Conditional formatting
Highlight according to cell rules, and run a Top 10 analysis
Data bars, color scales and icon sets
Manage rules, so the formatting does what you meant
Formulas
SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS
Dates: YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, WORKDAY, NETWORKDAYS, DATEDIF and EOMONTH
Repairing a date field Excel refuses to recognize - the ten minutes that saves the most time in this course
Text: LEFT, MID, RIGHT, TRIM, SUBSTITUTE, and TEXTBEFORE and TEXTAFTER
Logic and lookups: IF, VLOOKUP, HLOOKUP, and RANK
The new Excel functions
XLOOKUP, in two parts - what it does that VLOOKUP could not
GROUPBY and PIVOTBY - summarize data without building a PivotTable
VSTACK, HSTACK, TOCOL and TOROW - reshape data with a formula instead of copy and paste
HOW IT IS TAUGHT
Every section opens with an overview, then works through short lessons on real sales data. Four sections finish with a practical activity and a full worked walkthrough, so you find out whether it went in. There are 18 written articles, 22 downloadable files and role-play exercises where you work through a data project as if a colleague had asked you for it.
WHAT YOU NEED
Excel for Microsoft 365 is recommended. Most of the course runs on Excel 2016 or later. The New Functions section needs Excel for Microsoft 365 or Excel 2024, and XLOOKUP needs Excel for Microsoft 365 or Excel 2021 or later.
ABOUT THE TRAINER
I have been training business people to work with data since 2008, and publishing on Udemy since 2013. I now have 16 live courses with more than 400,000 students and more than 139,000 reviews, at an average rating of 4.6. This one is my highest rated, at 4.7.
I teach Microsoft Excel, Copilot in Excel, Microsoft Power BI, Looker Studio and Amazon QuickSight. What makes my courses different is that I teach the analysis, not just the tool - every lesson starts with a business question someone actually asks, and shows you how to answer it with software you already have.
WHAT STUDENTS ARE SAYING
"It is very helpful and informative."
"Great refresher for formulas which I had forgotten."
"Great class to brush up on the Excel Equations. The instructor teaches multiple ways to approach the same problem, helping create a foundational understanding for more complex equations down the line. Would highly recommend to anyone looking to upskill!"
Open the first lesson, download the sales data, and by the end of the first section you will have a table that answers questions you used to work out by hand.
Who this course is for:
- Excel users who can enter data and write a basic SUM, and now need the formulas that answer actual questions - which customers, which months, which products.
- Analysts and administrators who rebuild the same summary by hand every week and suspect there is a faster way.
- Managers handed a spreadsheet who have to get an answer out of it before a meeting starts.
- VLOOKUP users who keep hitting its limits and want to see what XLOOKUP, GROUPBY and PIVOTBY do instead.
- This is the starting point of my Excel series. If your problem is messy data arriving from somewhere else, take Complete Introduction to Excel Power Query next. If your problem is summarizing data you already have clean, take Complete Introduction to Excel Pivot Tables.