Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Excel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY

Name: Excel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY
Rating: 4.6 (3974 reviews)

Build Excel tables and write SUMIF, date, text and XLOOKUP formulas - and stop rebuilding reports by hand

Created byIan Littlejohn

Last updated 9/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

Course content

10 sections • 67 lectures • 4h 26m total length

Introduction to Tables and Formulas with Excel1:31
Meet your instructor and see what the course builds: Excel tables, conditional formatting, and the formula families that do the work - SUMIF, dates, text, lookups and the new Excel functions.
Overview of Tables and Formulas in Excel2:31
Written overview of the course: what an Excel table gives you, and the formula families covered - conditional formatting, SUMIF, dates, text, IF and lookups, and the new Excel functions.
About the Course1:23
How to get the best from the course: setting the video quality to full HD, changing the playback speed to suit you, and where to go for Udemy support and your certificate.
Download the Training Data Files0:04
Download the three training files used through the course - the sales data, the employee master and the formula file - so you can work along with every lecture.
Welcome to Udemy Roleplay0:35
What the Udemy role-play is: practice discussing an Excel tables and formulas project with an AI character, and how to get the most from the conversation.
Meeting with Ada to discuss using tables and formulas in Excel

Table Section0:53
What this section covers: turning a range into an Excel table, filtering text, numbers and dates, totaling with the total row, slicers, and table syntax in formulas.
Section overview: what a table gives you that a range does not0:23
Written overview of the Excel Tables section, with a cheat sheet and Quick Guide PDF to download - what a table gives you that a plain range does not.
Creating Tables9:25
Turn a range into an Excel table, name it and apply a table style, so formatting, filters and formulas extend automatically as rows are added.
Filtering Text Data in Tables6:01
Filter and sort a text column in an Excel table using the header dropdowns - equals, begins with, contains.
Filtering Numeric Data in Tables6:56
Filter a numeric column in an Excel table - greater than, between, top 10 - and sort the results.
Filtering Dates in Tables6:10
Filter a date column in an Excel table, including the built in period filters such as this month and last year.
Aggregating Numeric Data in Tables5:02
Switch on the Total Row in an Excel table for Sum, Average, Count, Max and Min at the foot of the table, and change what each column totals.
Using Slicers to Filter Data in Tables6:23
Add a slicer to an Excel table so it filters with buttons, and watch the total row update as you click.
Download the Sales Data file0:04
Download the Sales Data workbook used for the tables exercise so you can filter and summarize it yourself.
Your turn: filter and summarize the sales table0:37
Your turn: filter the sales table and summarize it with the total row to answer the questions in the brief.
Walkthrough: the sales table exercise7:13
Walkthrough of the sales table exercise: applying each filter and reading the answers off the total row.
Understanding the Table Syntax6:57
Understand structured references: how a formula names the table and its columns instead of using cell addresses, and why that keeps formulas readable.
Meeting to discuss using Excel Tables for data analysis

Introduction to Conditional Formatting2:16
What this section covers: highlighting exceptions with cell rules and top and bottom rules, data bars, color scales and icons, and managing the rules you create.
Section overview: when to use conditional formatting0:29
Written overview of the Conditional Formatting section, with a cheat sheet and Quick Guide PDF to download - when to use conditional formatting and which rule type fits.
Highlight According to Cell Rules7:46
Use highlight cell rules - greater than, less than, between, text that contains - so values that breach a threshold color themselves.
Conditional Formatting - Top 10 Analysis4:56
Use the top and bottom rules to highlight the highest and lowest values, including above and below average.
Data Bars, Color Scales and Icons5:14
Add data bars, color scales and icon sets so the size and the status of every value show at a glance.
Using Manage Rules6:05
Use Manage Rules to see every conditional format on the sheet, change the range it applies to, edit it, or set the order the rules run in.
Your turn: highlight the exceptions0:34
Your turn: highlight the exceptions in the sales data using the rule types from this section.
Walkthrough: the conditional formatting exercise8:29
Walkthrough of the conditional formatting exercise: choosing each rule and the range it applies to.
Meeting to discuss using conditional formatting to highlight data

Section overview: calculating on filtered data0:31
Written overview of the SUMIF section, with a cheat sheet and Quick Guide PDF to download - calculating on a subset of rows without filtering the sheet.
Introduction to SUMIF Section0:53
What this section covers: SUMIF, AVERAGEIF and COUNTIF, their multi criteria versions ending in S, and criteria that use comparison operators.
SUMIF, AVERAGEIF and COUNTIF Formulas7:33
Use SUMIF, AVERAGEIF and COUNTIF to total, average and count only the rows that meet one condition.
SUMIFS, AVERAGEIFS and COUNTIFS Formulas5:16
Use SUMIFS, AVERAGEIFS and COUNTIFS to apply several conditions at once, keeping the sum range and the criteria pairs in the right order.
Additional Formulas5:02
Go further with the IF family: criteria built from comparison operators such as greater than a value, and the options that catch people out.
Your turn: calculate on filtered data0:24
Your turn: calculate on filtered data with the IF family - the totals, averages and counts the brief asks for.
Walkthrough: the SUMIF exercise7:50
Walkthrough of the SUMIF exercise: writing each formula and checking that the criteria and sum ranges line up.
Meeting with Ada to discuss using SUMIF formulas to filter data

Introduction to Date Formulas1:37
What this section covers: the date functions - YEAR, MONTH, DAY, WEEKNUM, WEEKDAY, WORKDAY, NETWORKDAYS, DATE, TODAY and DATEDIF - and fixing dates Excel will not recognize.
Section overview: what date formulas are for0:37
Written overview of the Date Formulas section, with a cheat sheet and Quick Guide PDF to download - what each date function is for.
Year, Month and Day Formulas9:03
Use YEAR, MONTH and DAY to split a date into its parts so you can group and report by period.
WeekNum and WeekDay Formulas4:32
Use WEEKNUM and WEEKDAY to get the week number and the day of the week from a date, including which day the week starts on.
WorkDay and NetWorkDays Formulas4:38
Use WORKDAY and NETWORKDAYS to add working days to a date or count the working days between two dates, allowing for holidays.
Date and Today Formulas4:00
Use DATE to build a date from separate year, month and day values, and TODAY for a date that updates itself.
Download the Formula File0:03
Download the Formula File workbook used through the date, text, lookup and additional formula lectures.
How to Correct a Date Field in Excel10:10
Fix dates that Excel is holding as text: how to spot them and the ways to convert them into real dates you can calculate with.
DateDif Formula5:33
Use DATEDIF to get the difference between two dates in years, months or days - the function behind age and length of service.
Your turn: work with dates0:30
Your turn: work with the dates in the sales data - split them into parts, count working days and calculate a difference.
Walkthrough: the date exercise, part 15:08
Walkthrough of the date exercise, part 1: building the date part formulas and checking the results.
Walkthrough: the date exercise, part 26:36
Walkthrough of the date exercise, part 2: the working day and DATEDIF calculations.
Meeting with Ada to discuss using Date formulas

Introduction to Text Formulas0:43
What this section covers: the text functions for cleaning and splitting data, including the newer TEXTBEFORE and TEXTAFTER.
Section overview: cleaning and splitting text0:41
Written overview of the Text Formulas section, with a cheat sheet and Quick Guide PDF to download - cleaning and splitting text fields.
Working with Text Formulas6:42
Use LEFT, RIGHT, MID, LEN, TRIM and the case functions to pull apart and clean up text fields.
Text Before and After7:14
Use the newer TEXTBEFORE and TEXTAFTER functions to split text at a delimiter without counting characters.

Introduction to New Functions Section2:18
What this section covers: the new Excel functions - GROUPBY, PIVOTBY, VSTACK, HSTACK, TOCOL, TOROW and XLOOKUP - and the older ones they replace.
Section overview: the new Excel functions and what they replace0:42
Written overview of the New Functions section, with a cheat sheet and Quick Guide PDF to download - the new Excel functions and what each one replaces.
Groupby Formula9:20
Use GROUPBY to summarize a table by one or more fields with a single formula, returning a grouped result that updates itself.
Pivotby Formula5:28
Use PIVOTBY to build a cross tab summary - rows, columns and a calculation - as a formula rather than a PivotTable.
VStack and HStack Formulas5:21
Use VSTACK and HSTACK to combine several ranges or tables into one, stacked vertically or side by side.
ToCol and ToRow Formulas3:30
Use TOCOL and TOROW to flatten a range into a single column or row, ready for another function to work on.
XLookup Formula Part 19:24
Use XLOOKUP, the replacement for VLOOKUP and HLOOKUP: give it the lookup value, the lookup array and the return array, with no column counting.
XLookup Part 24:53
A second XLOOKUP worked example: returning territory details from another sheet into your transaction table, and the arguments beyond the basic three.

Introduction to IF,HLOOKUP and VLOOKUP0:48
What this section covers: the IF function for logic, and VLOOKUP and HLOOKUP for pulling values out of another table.
Section overview: logic and lookups0:39
Written overview of the logic and lookups section, with a cheat sheet and Quick Guide PDF to download - IF, VLOOKUP and HLOOKUP.
The IF Formula3:50
Use IF to test a condition and return one value when it is true and another when it is false - the basis of most spreadsheet logic.
The VLOOKUP and HLOOKUP Formulas8:17
Use VLOOKUP and HLOOKUP to pull a value out of another table by its key, and see why the last argument must be FALSE for an exact match.
An Example of VLOOKUP Formula5:30
A worked VLOOKUP across two sheets: looking the key up on one tab and returning the value to another, the situation you meet most often.
Meeting with Ada to discuss using text and lookup formulas

Introduction to Additional Formulas0:46
What this section covers: three more formulas worth knowing - EOMONTH, RANK and SUBSTITUTE.
Section overview: three formulas worth knowing0:38
Written overview of the additional formulas section, with a cheat sheet and Quick Guide PDF to download - EOMONTH, RANK and SUBSTITUTE.
EOMONTH3:43
Use EOMONTH to return the last day of a month, a set number of months forward or back - the function behind period end reporting.
RANK4:07
Use RANK to number items by their value, largest to smallest or smallest to largest.
SUBSTITUTE3:48
Use SUBSTITUTE to replace text inside a cell, stripping unwanted characters or swapping one string for another.

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.

Excel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 6min

Excel Tables13 lectures • 56min

Conditional Formatting in Excel9 lectures • 36min

SUMIF, AVERAGEIF and COUNTIF8 lectures • 27min

Date Formulas in Excel13 lectures • 52min

Text Formulas in Excel4 lectures • 15min

GROUPBY, PIVOTBY and the New Excel Functions8 lectures • 41min

IF, VLOOKUP and HLOOKUP in Excel6 lectures • 19min

EOMONTH, RANK and SUBSTITUTE5 lectures • 13min

Conclusion2 lectures • 1min

Requirements

Description

Who this course is for: