Excel Tables and Formulas: SUMIF, XLOOKUP and GROUPBY (Udemy.com)

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.