Quick Answer
Data modelling in Excel is the intentional design of how tables relate to each other — using Excel’s Data Model engine, Power Pivot, and structured references — so formulas, pivots, and DAX behave predictably across thousands of rows. It’s not optional when your sales data spans 7 sheets and your finance team refreshes it weekly.All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Excel Tables + VLOOKUP/XLOOKUP | 1. Convert ranges to Tables (Ctrl+T) 2. Use XLOOKUP with structured refs like Table2[Sales]3. Manually maintain lookup keys |
Small datasets (<5k rows), one-time reports | No automatic relationship enforcement; breaks if key columns change order or name |
| Power Pivot + Relationships | 1. Load tables into Data Model (Alt+A+P+R) 2. Define relationships in Manage Relationships dialog 3. Build pivots using fields from multiple tables |
Multi-table analysis (sales, products, regions), large datasets (100k+ rows) | Not available in Excel for Web or older versions; requires manual refresh triggers |
| DAX Measures in Power Pivot | 1. Create calculated columns or measures using SUMX, CALCULATE, RELATED 2. Reference related tables with dot notation: Products[Category]3. Test context filters in pivot row/columns |
Dynamic KPIs (e.g., YoY growth, margin % by region), role-based filtering | Steeper learning curve; DAX errors don’t surface until pivot use — not during measure creation |
| Power Query M Code + Merged Queries | 1. Load all sources into PQ Editor 2. Merge queries (Home > Merge Queries) 3. Expand only needed columns; avoid duplicating keys 4. Load result to worksheet or Data Model |
Consolidating messy source files (CSV, web scrapes, ERP exports) | Merged tables lose bidirectional filtering unless loaded to Data Model; performance dips above 500k rows |
Method 1 Deep Dive
Let’s say you’re tracking Q1 2024 sales across three teams — but your raw data lives in separate sheets: Sales (A1:E102), Products (G1:H87), and Regions (J1:K56). You want a pivot showing total revenue by product category and region. First, convert each range to an Excel Table: select A1:E102 → Ctrl+T → check “My table has headers” → name ittblSales. Do the same for Products (name it tblProducts) and Regions (tblRegions).
Now go to Data tab → Relationships (or press Alt+A+P+R). Click New. In the dialog:
- Table:
tblSales, Column:[ProductID] - Related Table:
tblProducts, Related Column:[ID]
tblSales[RegionID] is formatted as text but tblRegions[RegionCode] is number, the button stays gray. Fix it with =VALUE(tblSales[@RegionID]) or format both as General first.
Now insert a pivot table. Drag Category from tblProducts and RegionName from tblRegions into Rows. Drag Amount from tblSales into Values. It works — no VLOOKUP, no helper columns. And if you add 200 new sales rows tomorrow? The pivot auto-includes them.
Method 2 Deep Dive
You’ve got a live dashboard for Acme Corp leadership. They need gross margin % by quarter — but margin isn’t stored directly. It’s calculated:(Revenue – COGS) / Revenue. And COGS lives in a separate table linked by OrderID.
Load both tables into Power Pivot (Alt+A+P+R → Add to Data Model). Then click Manage → open the Power Pivot window. Go to Home → Measures → New Measure.
Enter this DAX:
Margin % = DIVIDE(SUM(tblSales[Revenue]) - SUM(tblCOGS[Amount]), SUM(tblSales[Revenue]))
Now try dragging Quarter (from a Date table) and Margin % into a pivot. It works — but only because you built a proper date table with a relationship to tblSales[OrderDate], and marked it as a date table (Design tab → Mark as Date Table). Without that, time intelligence functions like SAMEPERIODLASTYEAR will return blanks. Trust me, I learned this the hard way after rebuilding a forecast model twice.
Sample data used in this scenario:
| OrderID | OrderDate | Revenue | ProductID | RegionID |
|---|---|---|---|---|
| ORD-2024-001 | 2024-01-12 | $14,890 | P-772 | R-NW |
| ORD-2024-002 | 2024-01-15 | $8,210 | P-109 | R-SE |
| ORD-2024-003 | 2024-02-03 | $22,450 | P-772 | R-NW |
| ORD-2024-004 | 2024-02-18 | $5,630 | P-304 | R-CEN |
| ORD-2024-005 | 2024-03-07 | $17,910 | P-109 | R-SE |
Cheat Sheet
| Task | Shortcut / Action | Where to Find It |
|---|---|---|
| Convert range to Table | Ctrl+T | Home tab → Format as Table (or Insert → Table) |
| Open Power Pivot | Alt+A+P | Data tab → Manage Data Model |
| Open Relationships dialog | Alt+A+P+R | Data tab → Relationships |
| Refresh all queries & model | Alt+F5 | Data tab → Refresh All |
| Mark table as Date Table | Right-click table in Power Pivot → Mark as Date Table | Power Pivot window → Design tab |
