How to Use a Data Model in Excel

This guide will explain how to start using a data model in Excel.

Excel’s Data Model tool is useful when working with data from multiple datasets. Users can work with this tool to create associations or connections between tables.

The Data Model tool in Excel is a way of organizing data from multiple tables into one big table. Data models also work well when combined with PivotTables and PivotCharts. 

Let’s take a look at a quick example where you might have to use a data model in Excel.

Suppose you have a list of orders in a spreadsheet. Each order has a corresponding product ID and the quantity of the purchased item.

You have a separate table that works as your product database. Each product has a corresponding product ID, product price, and product name.

We want to use both tables to determine the total price of each order. To do this, we must set up a data model and make the appropriate connections.

Once we’ve set up a data model from our various tables, we can use the PivotTable feature to determine the price of each order.

This scenario is one common use case for data models in Excel. If you’re working with large amounts of data, using a data model is much more efficient than setting up multiple lookup formulas to connect different tables.

Now that we have a grasp of when to use data models, let’s learn how to use it on an actual sample spreadsheet.

 

 

A Real Example of Using a Data Model in Excel

The following section provides several examples of how data models can be useful when analyzing your datasets. We will also go into detail about the formulas and tools used in these examples.

For our next two examples, we’ll use the following tables as our data source:

sample data

 

The first table provides a list of orders. Each order has an associated product ID and the quantity that was purchased. The second table provides more information about each product, such as the product name and price.

First, let’s take a look at a real example of a PivotTable that uses a data model to connect multiple tables.

Using a PivotTable, we can determine the quantity sold for each item by using the Sum aggregation. We could also determine how many different customers bought each item by using the Count aggregation.

using data model with pivottable

 

Excel also includes a PivotChart feature that works well with data models. In the chart below, we produced an easy-to-read visualization of how many orders each product received.

using pivotchart with data model

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to test out the PivotChart and PivotTable features. If you want to learn how to use the data model feature in Excel, read the next section for a step-by-step guide.

 

 

How to Use a Data Model in Excel

This section will explain everything you need to know to start using a data model in Excel. 

You’ll learn how to establish a relationship between two Excel tables through the Data Model tool. We’ll also show how to use the data model when working with a PivotTable or PivotChart.

Follow these steps to start using data models:

  1. First, identify the tables you want to connect. Ensure that these tables have a column that can link our data. For example, we can join our order and product tables by using the Product ID column, which is present in both tables.
    source data for data model
  2. Let’s add our data into a data model. In the Power Pivot tab, click on the Add to Data Model option. Select the cell range that you would like to add.
    select Add to Data Model option
  3. The user must perform the previous step until two or more tables have been added to the data model.
    Excel table for connecting
  4. In the Power Pivot tab, click on the Manage icon.
  5. In the Power Pivot window, navigate to the Design tab and click on Create Relationship.
    data model view
  6. Select the two tables you would like to connect. Ensure that the common field is selected. In this example, we’ll create a relationship between our order and product tables through the Product ID field. Click on OK to create the new relationship.
    establishing a relationship between tables
  7. We can now use the Data Model and the PivotTable to analyze data belonging to both tables. For example, the product name in the example below is retrieved from the product table while the numerical data is aggregated from the order table.
    using a data model in Excel with pivottable
  8. Similarly, we can use the PivotChart feature together with our data model to visualize data from both tables.
    using a data model in Excel with pivotchart

 

These are all the steps needed to start using a Data Model in Excel. 

 

 

This step-by-step guide should provide you with all the information you need to begin using the Data Model in Excel.

You should now understand how to use data models to explore data spread across multiple tables in your worksheet.

The data model tool is just one example of the many Excel tools you can use in your spreadsheets. Our website offers hundreds of other tutorials and guides to help you get more out of Microsoft Excel.

For example, check our detailed guide on how to fix your data models if you find any issues working with them.

With so many other Excel functions available, you can find one that works best for your workflow or use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like