skip to content

In Excel, what does the Power Pivot Data Model add that a plain PivotTable cannot do?

level: middleimportance: nice to knowfreq 35%

answer

  1. a plain pivot reads only one flat range
  2. you stopped needing lookup columns
  3. one aggregation is missing without it
  4. the grid limit stops applying
  5. named calculations live in the model, not the sheet

basics

~10 s

The Data Model relates several tables on shared keys instead of flattening them with lookups, supports DAX measures, offers a true Distinct Count aggregation, and holds far more rows than the 1,048,576-row worksheet grid.

solid answer

~50 s

An ordinary PivotTable reads **one flat range**, so combining sales with product attributes means a `VLOOKUP` down every row first. Adding tables to the **Data Model** (Power Pivot) lets you define **relationships** on shared keys instead, and a single PivotTable can then slice a sales measure by an attribute that lives in another table. On top of that you get **DAX measures** — named expressions like `Revenue := SUM(Sales[Amount])` that evaluate per pivot cell rather than being materialised per row — and **Distinct Count**, an aggregation that plain PivotTables do not offer at all. Storage is compressed and columnar rather than cell-based, so the model can hold many more rows than the worksheet's 1,048,576-row limit, bounded by memory and workbook size rather than by the grid. Power Pivot ships with Excel for Windows; availability differs by edition and platform, so check your build.

code

text · 5 lines
text
Sales(OrderID, ProductID, CustomerID, Amount)   -- fact rows
Products(ProductID, Category, Brand)            -- attributes

-- plain PivotTable: VLOOKUP Category onto every Sales row first
-- Data Model: relate Sales[ProductID] -> Products[ProductID], done

go deeper

for a junior

Know that Excel has a second engine behind PivotTables and that it lets one pivot use fields from more than one table. Recognising the name Power Pivot and the Distinct Count aggregation is enough here.

for a middle

Explain relationships as the replacement for lookup-flattening, and describe a measure as a named calculation evaluated per pivot cell rather than per source row. Be precise that Power Query loads and Power Pivot models.

for a senior

Discuss when a Data Model workbook is the right answer and when it is a liability: no scheduled refresh, no access control, one file on one machine. Explain what carries over if you rehost the model.

for a principal

Own the boundary question. A widely used Data Model workbook is an unmanaged production dataset; decide whether to sanction, host or retire it, and what the organisation loses in agility if you insist every model be centrally built.

## Two engines in one application A classic PivotTable summarises a **single rectangular range or Excel Table**. Everything it can group by has to be a column on that one range. That is why spreadsheets end up with a wide "master" sheet: someone ran lookups to drag the product category, the region name and the sales-rep team onto every transaction row so they could be used as pivot fields. The **Data Model** is a different engine living inside the same workbook — an in-memory, compressed, columnar store. You load tables into it, relate them, and build PivotTables on the model instead of on a sheet. It is the same family of technology that underpins Power BI's models, which is why the concepts transfer directly. ## Relationships instead of flattening In the model you define a relationship between `Sales[ProductID]` and `Products[ProductID]`. Now a PivotTable can put `Products[Category]` on rows and `Sales[Amount]` in values, and the engine filters sales through the relationship. Three consequences: - No lookup columns to maintain, so no column-index bugs and no recalculation cost on hundreds of thousands of rows. - Attribute changes happen once in the dimension table, not once per transaction row. - The workbook shrinks, because you are not storing the same category string 400,000 times. ## Measures A measure is a named DAX expression stored in the model: ``` Revenue := SUM( Sales[Amount] ) Distinct Customers := DISTINCTCOUNT( Sales[CustomerID] ) Revenue per Customer := DIVIDE( [Revenue], [Distinct Customers] ) ``` A measure is evaluated **per PivotTable cell**, against whatever filters that cell implies, rather than being computed once per source row. That means one definition serves the grand total, each subtotal and each detail cell, and ratios behave correctly at every level — `Revenue per Customer` at the grand total divides total revenue by total distinct customers, not the average of the row-level ratios, which is what a sheet formula copied down would give you. ## Distinct Count Ask a plain PivotTable for the number of distinct customers and there is no such option in Value Field Settings; you end up with helper columns, `SUMPRODUCT` tricks or `COUNTIF` gymnastics that break on refresh. Add the source to the Data Model and **Distinct Count** appears as an aggregation. The reason is structural. Distinct count is **non-additive**: you cannot derive the distinct count of a total from the distinct counts of its parts, because the same customer may appear in several parts. The classic PivotTable cache works from pre-aggregated groupings, so it cannot produce one. The model's engine evaluates the expression against the column store for each cell independently, so it can. ## Scale A worksheet is capped at 1,048,576 rows by 16,384 columns — that is the grid, and it is fixed. The Data Model does not store data in cells, so it is not bound by the grid; its ceiling is memory and workbook size. Columnar compression helps a great deal on the low-cardinality columns typical of dimension data. This is *not* the same as "no limits": a large model in Excel is still a single file on one laptop with no scheduled refresh and no access control. ## Power Query and Power Pivot are not the same thing Worth being precise, because the names blur. Power Query is the **load and shape** step — it connects, cleans and types the data and lands it somewhere. Power Pivot is the **model and calculate** step — relationships, measures, hierarchies. The usual pipeline is Power Query to load into the Data Model, then Power Pivot measures on top of it. ## When to leave Excel The model concepts carry over to Power BI unchanged, so building one in Excel is genuine modelling practice, not a dead end. Move it out when you need scheduled unattended refresh, sharing beyond emailing a file, row-level access control, or a dataset larger than one machine's memory comfortably holds. Until then a Data Model workbook is a legitimate, and surprisingly capable, analysis tool.

  • Why is Distinct Count unavailable in an ordinary PivotTable?
    Distinct count is non-additive — the distinct customers of a total is not the sum of the distinct customers of its parts, because the same customer can appear in several. The classic PivotTable cache builds cells from pre-aggregated groupings and cannot derive it. The Data Model's engine re-evaluates the expression against the column store per cell, so it can.
  • How do relationships in the Data Model change how you would build the source data?
    You stop building one wide flattened sheet. Load transactions and attribute tables separately, relate them on their keys, and let the PivotTable filter across the relationship. Attributes are then stored once and corrected in one place, the workbook is far smaller, and there are no lookup formulas to recalculate or to break when someone inserts a column.
  • When should a Data Model workbook move to Power BI or the warehouse?
    When it needs unattended scheduled refresh, sharing beyond mailing a file, access control per user, or more data than one laptop's memory holds comfortably. The modelling work transfers — relationships and DAX measures are the same concepts — so the migration is mostly about where the model is hosted and who is allowed to see which rows.

saying these in an interview costs you the question

  • Describes Power Pivot as just a PivotTable that holds more rows
  • Thinks you still need VLOOKUP to combine tables inside the model
  • Claims Distinct Count is available in any PivotTable
  • Says the Data Model removes Excel's memory constraints entirely
  • Uses Power Query and Power Pivot as interchangeable names

context

resolving links…