Two limits eventually find every serious Excel user. The hard one: a sheet stops at 1,048,576 rows, and modern exports laugh at that. The soft one: your workbook has five related tables held together with lookups, and every analysis starts by dragging columns between them. Both limits fall to the same tool — one that’s been hiding inside Excel since 2010: Power Pivot and the data model.

The idea in one sentence: instead of data living on sheets, it lives in an in-memory engine behind the workbook — where tables hold tens of millions of rows, relate to each other properly, and feed PivotTables that work across all of them at once.

Loading: the recipe grows a destination

You already know the front door. Power Query imports and cleans as usual — the only change is the last step: Close & Load To… → Only Create Connection + Add this data to the Data Model. The rows never land on a sheet (they couldn’t; there may be four million of them) — they land in the engine, compressed hard. A file that would be absurd as cells is routinely a modest .xlsx as a model.

Enable the Power Pivot tab (File → Options → Add-ins → COM Add-ins) and its window shows what you’ve loaded — a data view with no row limit and no fear of someone sorting it wrong, because model tables aren’t editable cells. The workbook becomes what the architecture lesson always wanted: a thin visible layer over an engine.

Relating: the lookup you stop writing

In Power Pivot’s Diagram View, drag Orders[CustomerID] onto Customers[ID]. That line is a relationship — and it replaces the lookup column entirely. Build a pivot from the data model and the field list offers every table: Region from Customers as rows, Amount from Orders as values, and the relationship joins them on the fly. No XLOOKUP columns duplicating data, no stretched references — the join is the model.

If this sounds like the Power BI lesson’s “relate, don’t merge” — it’s not similar, it’s identical: the same engine (VertiPaq), the same modelling, the same DAX. Power Pivot is where an Excel person learns Power BI’s hard part without leaving the spreadsheet.

the model (behind the workbook) Orders 4.2M rows CustomerID · Amount Customers ID · Region relationship one pivot, both tables North £1.42M South £1.18M East £0.97M no lookup column exists
Four million rows the grid could never hold, joined by a drawn line instead of a written lookup — and the pivot on top neither knows nor cares.

Measures: the step past SUMIFS

The model’s third gift is measures — calculations defined once, in DAX, that work in any pivot at any grouping:

Total Sales := SUM(Orders[Amount])
Margin % := DIVIDE([Total Sales] - [Total Cost], [Total Sales])

The second one is the point. A margin isn’t summable — you can’t add up percentages by region and get the year (the averaging trap) — but a measure recomputes at whatever level the pivot shows: right by region, right by month, right at grand total. That’s the class of answer SUMIFS can’t give and calculated columns give wrongly. Start exactly there: ratios that must be right at every level. (DAX runs deep — time intelligence, share-of-total — but the LAMBDA rule applies: learn it when a real report demands it.)

When to reach for it — and when not

The honest triggers: data past a few hundred thousand rows; a slow workbook whose weight is lookup columns; three-plus related tables; or ratios that must survive regrouping. Below those, plain Tables and pivots stay simpler to share and audit — and above a certain point (dashboards for an audience, refresh schedules) the same skills graduate to Power BI unchanged.

The million-row ceiling was never Excel’s edge — it was the grid’s edge. The engine behind it has been waiting all along, and everything this course taught — recipes, relationships, honest aggregation — turns out to have been the syllabus for it.