Advanced Excel · Intermediate level

Intermediate level · Module 2: Data Models and Power Pivot · Lesson 1 of 3

Building a Data Model

Load tables into the data model and connect them with relationships.

In this lesson you will

  • Add tables to the data model
  • Create relationships between tables

Key concepts

data modelrelationshipkey columnone-to-many

The lesson

The data model holds several tables inside the workbook and connects them with relationships, so a PivotTable can use fields from all of them without lookup formulas.

Relationships link a key column in one table (each location appears once) to the same column in another (many distributions per location): one-to-many.

The model compresses data efficiently, handling far more rows than a worksheet.

Step by step

  1. Load the distribution and location tables into the model.
  2. Create a relationship on the location code.
  3. Build a PivotTable using fields from both.
  4. Check a total against SUMIFS.

Practice activity

Load the NGO tables into a data model with correct relationships.

Expected result: A model whose PivotTables combine tables correctly.

Check your understanding

  1. What does a relationship do in a data model?

    Show answer

    Connects tables on a key so fields from both can be analysed together.

  2. What does one-to-many mean?

    Show answer

    One row in a table matches many rows in another.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.