What you'll learn:
- Load millions of rows into the Excel Data Model, then browse, filter and sort them inside Excel
- Create relationships between tables so one PivotTable can report across your whole dataset
- Write DAX calculated columns for dates, week days and categories using IF and SWITCH
- Build DAX measures with CALCULATE, ALL and ALLEXCEPT so a number is filtered by exactly what you intend
- Compare one period against another with Time Intelligence measures
- Rank and segment your customers using RANKX, SUMX and SWITCH
- Add KPIs, sets and hierarchies to the model, and build a four-chart dashboard in the practical activities
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.
Your sales are in one table, your customers in another and your calendar in a third - and the PivotTable will only look at one of them.
That is the wall every Excel user eventually hits, and Power Pivot is the way through it. It loads millions of rows into Excel, joins your tables with relationships, and lets you write measures that stay correct however anyone slices them. There is nothing to buy - it is already built into Excel for Windows, switched off by default, and the first section shows you how to turn it on.
I teach the analysis, not the tool. Every section starts with a question a manager actually asks - which customers are growing, how does this quarter compare with last year, who are my top ten accounts - and then shows you the Power Pivot method that answers it.
WHAT YOU WILL LEARN
Loading data into the Excel Data Model, then browsing, filtering and sorting it
Building PivotTables and PivotCharts on the model, with slicers and multi-chart layouts
Calculated columns: year, month, week day, a proper date table, IF and SWITCH
Measures: aggregations, CALCULATE, ALL and ALLEXCEPT, so a number is filtered by exactly what you intend
Time Intelligence measures for comparing one period against another
SUMX, RANKX for ranking your customers, and SWITCH for customer segmentation
Relationships between tables, and calculations that cross them
KPIs, sets and hierarchies in the data model
Two case studies, including loading from Power Query and building summary tables
HOW IT IS TAUGHT
Short lessons of four to ten minutes in HD, with all the training data files provided. Every section has a Practical Activity - a written brief and the data - followed by a walkthrough of the completed answer, so you build the model yourself rather than watch me build it. There is a four-chart dashboard to construct in the Relationships section.
A NOTE ON COPILOT
Copilot in Excel is very good at answering a question about one table you already have. It does not build a data model, join tables or write DAX. That is what this course teaches, and it is the part that does not go away - a model is the thing an AI assistant needs before it can give you a trustworthy answer about more than one table.
ABOUT THE TRAINER
I am a Udemy Instructor Partner. I have been training business people to work with data since 2008 and publishing on Udemy since 2013. Across 16 courses I have taught more than 398,000 learners and hold a 4.6 average from more than 139,000 reviews.
This course is rated 4.6 from more than 2,700 ratings. I specialise in training business users in Microsoft Excel, Copilot in Excel, Power Query, Microsoft Power BI, Looker Studio and Amazon QuickSight.
WHERE THIS COURSE SITS
This course assumes you can already build a basic PivotTable. If you cannot, take Complete Introduction to Excel Pivot Tables first. If your problem is getting messy data into Excel rather than modeling it once it is there, that is Complete Introduction to Excel Power Query. And Power Pivot uses DAX, the same formula language as Power BI - so this is also the least expensive way to learn DAX before you move across.
Ready to get past the one-table limit? Let's get started.
