Advanced Excel · Interactive lesson 7 of 12
Data Model & Power Pivot basics
One flat table forces you to repeat product names on every sales row. A Data Model relates several small, clean tables instead — a sales fact table and a lookup table, connected by one shared column.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialExcel Data Model and Power Pivot BasicsSkill check
Try these first:
- Tell a fact table (transactions) apart from a lookup table (products, regions).
- Relate two tables by a shared column instead of a lookup formula.
- Explain the difference between a calculated column and a measure.
- Write a simple DAX measure using SUM or DIVIDE.
All familiar? Skip ahead to A8 — What-If Analysis.
Concept teach
What you're learning
- Fact vs. lookup tables
- A fact table holds events — one row per sale. A lookup table holds descriptions — one row per product. Facts are many; lookups are few.
- Relationships & one-to-many
- One product can appear on many sales rows, but each sales row points to exactly one product — that's a one-to-many relationship, built by connecting the shared Product ID column.
- Measures vs. calculated columns
- A calculated column stores one value per row, computed once. A measure computes on the fly, in the context of whatever's being viewed — the same measure returns a different number inside each PivotTable cell.
- Simple DAX:
SUM,DIVIDE Revenue := SUM(Sales[Revenue]);Margin % := DIVIDE([Gross Margin],[Revenue])— DIVIDE safely returns blank instead of an error when the denominator is zero.- Using the Data Model in PivotTables
- Once tables are related, a single PivotTable can pull Region from one table and Revenue from another — no lookup formula stitching them together first.
Where to find it in Excel
Power Pivot›Manage · Measures›or Data › Relationships
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Row Labels | Total Sales | Margin % | |||||
| 2 | Hardware | 12,450 | 31.2% | |||||
| 3 | Accessories | 4,380 | 44.8% | |||||
| 4 | Electronics | 9,920 | 27.5% | |||||
| 5 | Grand Total | 26,750 | 31.9% | |||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Power Pivot tab. If you don't see it: File › Options › Add-ins › COM Add-ins › Microsoft Power Pivot for Excel.
- 2Add to Data Model loads a table into the model.
- 3Manage opens the model; Diagram View is where you draw one-to-many relationships.
- 4Measures › New Measure writes DAX such as =DIVIDE([Margin],[Total Sales]).
- 5Measures appear with Σ in the field list of a Data Model PivotTable.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
A Sales fact table and a Products lookup table, connected by Product ID. Relate them, then read the two measures.
Independent extension
Relate your own tables
Split one flat spreadsheet you use into a fact table and one or two lookup tables, relate them by a shared ID column, and write a Revenue and a Gross Margin % measure. Build a PivotTable that pulls fields from both tables at once.
Spot a mistake or have an idea for this lesson? Send feedback