Real data almost never lives in one table. Sales sit in one place, products in another, and customers somewhere else. Stitch them together with VLOOKUP, and the workbook soon groans. Formulas break, the file bloats, and every change is a chore. Relationships end that struggle. They link your tables in the Data Model, and a tidy star schema keeps the whole thing fast and clear.
This guide shows how to connect tables the right way. First, it explains why relationships beat lookups. Then it covers the star schema, keys, and the date table. Real steps and a full troubleshooting section follow. By the end, you will design a clean, reliable model.
Why Relationships Matter
A relationship links two tables on a shared key. After that, filters flow between them on their own. So a product filter reaches every matching sale. As a result, you drop the endless VLOOKUP columns.
What a Star Schema Is
A star schema is the gold standard layout. One central fact table holds your events, like sales. Dimension tables sit around it, like spokes on a wheel. The infographic below shows the shape.
Inside it, the fact table stores the numbers you sum. The dimensions store the labels you filter by. Products, customers, and dates all become dimensions. So the design mirrors how you actually ask questions.
One-to-Many and Keys
Most relationships are one-to-many. One product links to many sales rows. The key on the one side must be unique. Meanwhile, the many side can repeat it freely, as below.
How to Build a Relationship
Creating a link takes only a moment. The Diagram View makes it visual and simple. You drag one key onto the matching key. Then Excel draws the relationship for you.
Add a Dedicated Date Table
A proper date table unlocks time intelligence. It holds one row for every calendar date. You then link it to the date in your facts. After marking it, functions like year-over-year work.
Star Versus Snowflake
A star keeps dimensions flat and direct. Meanwhile, a snowflake splits them into smaller linked tables. A spaghetti model tangles links in every direction. For most work, the simple star wins, as below.
Troubleshooting Relationships
All three problems below are the most common. Each has a clear cause and a quick fix.
Excel says the key has duplicate values
The one side of a relationship needs unique keys. So a repeated ProductID in Products blocks the link. First, find the duplicates in that key column. A quick pivot or a Remove Duplicates check will show them. Then decide whether they are true duplicates or an error. Often a dimension table accidentally holds repeated rows. Clean it so each key appears only once. After that, the relationship builds without complaint.
A relationship line is dashed and inactive
Two tables can share several possible paths. To avoid confusion, Excel keeps only one active. It shows the others as dashed, inactive lines. So a formula may seem to ignore a link. First, confirm which relationship you actually need. For everyday use, keep the main path active. To use an inactive one, write a measure with USERELATIONSHIP. That activates the chosen link just for that calculation.
Totals look wrong across tables
Odd cross-table totals often mean a missing link. Without a relationship, one table cannot filter the other. So a measure may repeat the same total everywhere. First, open Diagram View and look for a gap. Then add the relationship on the shared key. Also check the filter direction is single, not both. A single direction keeps the filter flowing the expected way.
Frequently Asked Questions
- What is a star schema in Power Pivot?It is a layout with one central fact table. Dimension tables surround it like spokes. The fact holds numbers, and dimensions hold labels. So it mirrors how you filter and analyse data.
- How do I create a relationship between tables?Open Power Pivot and switch to Diagram View. Then drag one key onto the matching key. A line appears to show the link. Alternatively, use Manage Relationships for a form.
- Why does Excel reject my relationship?Usually the one side has duplicate keys. That side must hold each key only once. So find and remove the duplicates first. Then the relationship builds cleanly.
- Do I really need a separate date table?Yes, for any time-based analysis you do. A date table enables real time intelligence. So year-over-year and running totals work properly. Mark it as a date table to activate them.