Skip to main content
HomeTutorialsSpreadsheets

How to Create Data Models in Excel: A Comprehensive Guide

We create data models by formatting data, creating relationships, using Power Query, and leveraging Power Pivot for seamless data integration and analysis.
Jul 2024  · 13 min read

Data models allow us to load, integrate, and analyze data from multiple sources, facilitating a more dynamic and insightful view of the data. 

Across sections of the tutorial, we'll explore several ways to create data models in Excel, including using existing tables, importing new tables, and leveraging tools like Power Query, Power Pivot, and PivotCharts.   

Let’s get going!

How to Create a Data Model in Excel

Here are the general steps to be followed for creating a data model in Excel:

  1. Format your data as tables.
  2. Create relationships between the tables.
  3. Use Power Query to load and transform data.
  4. Use Power Pivot for advanced data modeling.
  5. Utilize PivotTables and PivotCharts to visualize your data.

When to Use a Data Model in Excel

Creating a data model in Excel is particularly useful in several common scenarios, including data cleansing, data integration and transformation, and complex data analysis. Here are some of the main benefits.

  • Facilitates Data Cleansing: Data models help in cleaning and standardizing different data sets to maintain uniformity and consistency. This is critical when dealing with data from different sources, which requires blending and preprocessing. 
  • Performs or Solves Data Integration and Transformation: With the data model in place, all the data available from any other source can easily be integrated and transformed, and data users can receive a unified view that is easy to process and analyze.
  • Enhances Data Visualization: Data models support the development of advanced data visualizations such as PivotCharts and Power View reports. 
  • Improves Data Security and Management: With a data model, controlled access to different parts of your data can be realized. You will have an easier time managing permissions and data within the model to reduce the risks of unauthorized access and data breaches.
  • Streamlines Data Reporting: Through data models, reporting of data can be fully automated, thereby facilitating the reporting and decision making process. This makes it possible for reports and dashboards to be adjusted dynamically in case there is an insertion of new data. Thus all stakeholders are provided with updated information for their use.
  • Handles Large Datasets: The data models can work with big datasets in Excel, overcoming the constraints in traditional Excel worksheets.
  • Enables Complex Data Analysis: The feature of creating relationships between multiple data tables allows advanced data analysis that might not be easy with regular Excel functions.

How to Create a Data Model by Importing Data

In this section, we will walk through the process of creating a data model in Excel by importing sample datasets. We will use two sample datasets: Order and Product. I have created a public GitHub repository called Excel-Data-Model so you can download the datasets and follow along in this tutorial.

The first step is to import data into Excel. Start by opening a new Excel workbook, and click on the Data tab. Under Get Data, select Get Data > From File > From Excel Workbook.

Using Excel data models to importing data into Excel

Importing data into Excel. Image by Author

Select theOrder.xlsx workbook, and click on Import. This will open the Navigator window: Select the Sheet1 table.

Excel data models navigator window

The Navigator window in Excel. Image by Author

Click the Load To option that’ll open the Import Data window. In the dialog box, select Only Create Connection and check Add this data to the Data Model. Click OK.

 Import Data window with Excel data models

Import Data window. Image by Author

Repeat the above steps to load the Product sheet. Once these two tasks are completed, your Excel workbook will look like this:

 Excel data models queries & connections pane in Excel data model

Queries & Connections pane. Image by Author

Congratulations! You have imported both the sheets into the Excel file. The next step is to establish relationships between the imported tables. To do this, go to the Data Tools tab and click Relationships.

Data Tools tab in Excel data model

Data Tools tab. Image by Author 

A new window will emerge.

Manage Relationships window in Excel data modelManage Relationships window. Image by Author

Click New to create a relationship. The Create Relationship pop-up will open. Define the relationship as shown below. 

Create Relationship window in Excel data modelCreate Relationship window. Image by Author

Click OK. The relationship is established, and you can confirm it in the Connections tab.

Queries & Connections pane in Excel data modelQueries & Connections pane. Image by Author

The next stage is to create the PivotTable. Go to the Insert tab and click PivotTable. Then, in the PivotTable dialog box, select Use this workbook’s Data Model.

PivotTable dialog box in Excel data modelPivotTable dialog box. Image by Author

Select the location for the PivotTable. Click OK. The PivotTable will open and you can see the fields under the PivotTable Fields.

PivotTable Fields in Excel data modelPivotTable Fields. Image by Author

To build the PivotTable, perform the following step.

In the PivotTable Fields pane, drag fields to the Rows, Columns, Values, and Filters areas:

  • Rows: ProductName
  • Values: Quantity

The resulting PivotTable will look like this:

PivotTable Fields pane in Excel data model

PivotTable Fields pane. Image by Author

You can customize the PivotTable and format it as needed.

How to Create a Data Model Using Power Query 

For this section, we’ll be using the data stored in the Excel file named, Sample_Data. This file is also available in the same link provided in the previous section. There are two tables in the sheet named, Data, that have the following records.

Excel tables to join in Excel data modelExcel tables to join. Image by Author

We’ll build data models in this existing Excel sheet. To begin, select any cell within the Customer ID and Name table and then select Ctrl + T.

Selecting a cell in a table in Excel data modelSelecting a cell. Image by Author

This opens a Create Table pop-up as shown below.

Create table pop-up in Excel data model

Create table pop-up. Image by Author

 Click OK and you’ll have the table ready. 

PivotTable in Excel data modelPivotTable in Excel. Image by Author

Now let’s give a name to this table. Go to the Table Design contextual window and under Table Name, rename the table to CustomerPQ. Note that you can give any other name as well. 

Table Design context window in Excel data model

Table Design context window. Image by Author

Now this table is set as CustomerPQ.

Table Design contex window in Excel data modelTable Design context window. Image by Author

Repeat the process for the second table. This time, once the table is created, set the name to PurchasePQ.

Two tables in Excel ready for Data Model in ExcelTwo tables in Excel ready for Data Model. Image by Author

Now, the active table is the PurchasePQ table. Go to Data, and select From Table Range. The following sheet will appear.

Opening the Power Query Editor in Excel data modelOpening the Power Query Editor. Image by Author

To create a connection and add the Purchase PQ table to the Data Model, click on the drop-down tab of Close & Load, and select Close & Load To…  

Closing the Power Query Editor in Excel data modelClosing the Power Query Editor. Image by Author

This will open an Import Data window, that needs to be filled in the following manner:

Import Data window in Excel data modelImport Data window. Image by Author 

Once you complete the above steps, you will see the Queries & Connections box.

Queries & Connections box in Excel data modelQueries & Connections box. Image by Author

Now, add the CustomerPQ table to the connection. For that, click on any cell of the table, go to Data, and select From Table Range. The following sheet will appear.

Tables ready for Excel Data ModelTables ready for Excel Data Model. Image by Author

Repeat the process you completed for the PurchasePQ table, and fill-in the Import Data window as shown below:

Import Data window in Excel data modelImport Data window. Image by Author 

 Click on OK. The Queries & Connections tab will now display both the tables.

Queries and Connections pane in Excel data modelQueries and Connections. Image by Author

Now we are ready to establish relationships. Within the Data tab, go to the Data Tools section, and click on Manage Data Model.

Data Tools pane in Excel data modelData Tools. Image by Author

This will open the Power Pivot for Excel.

Power Pivot for Excel data modelPower Pivot for Excel. Image by Author

Now to create a relationship between the two tables, click on the Diagram View.

View pane in Excel data modelView pane. Image by Author

 This will open a screen like the one shown below. 

Diagram View in Excel data modelDiagram View. Image by Author

You can see there are two tables, but not connected yet. To create the relationship, go to the Design tab and click on Create Relationship tab.

Design tab in Excel data modelDesign tab. Image by Author

A new window will open. Fill in the details as shown below:

Create a Relationship window in Excel data modelCreate a Relationship window. Image by Author

Click on OK. You’ll notice that the relationship is now established.

Window showing Relationship is established in Excel data modelWindow showing Relationship is established. Image by Author 

Save the Power Pivot by using the save tab or Ctrl + S, and close the tab. The following output is displayed.

Enable Content pop-up window in Excel data modelEnable Content pop-up. Image by Author

Click on Enable Content to create the connection. Now the connection is established. We can use the PivotTable now. To do that, go to Insert and click on PivotTable option, and select From Data Model.

PivotTable Pane in Excel data modelPivotTable Pane. Image by Author

You’ll get a pop-up like this:

PivotTable from Data Model tab in Excel data modelPivotTable from Data Model tab. Image by Author

Click on New Worksheet and click OK. A new sheet named Sheet1 is created.

PivotTable sheet in ExcelPivotTable sheet. Image by Author 

In the PivotTable Fields, you can find the tables.

Labels under PivotTable Fields in Excel data modelLabels under PivotTable Fields. Image by Author

Select Customer Name in the CustomerPQ table. Next under PurchasePQ, select Purchase. PivotTable Fields will look like this:

Selecting Items in the PivotTable Fields in Excel with our Excel data modelSelecting Items in the PivotTable Fields. Image by Author 

This will generate the PivotTable inside the sheet that’ll look as below:

PivotTable Output in Excel data modelPivotTable Output. Image by Author

Bingo! In this way, you can use Power Query to create the Data Model and use Power Pivot to create summary reports.

How to Create a Data Model Directly in Power Pivot

In this section, you’ll use Power Pivot to create a data model, and prepare a summary report. 

To begin, go to the sheet named Power Pivot in the data we discussed above, and create the tables for our two records.

Records to be used in the Excel data modelRecords to be used. Image by Author 

We’ve already seen how to create tables in the previous sections, so follow those steps to create the two tables.

The only difference would be that we’ll set new names for these tables, so that we can differentiate between the earlier section: 

  • Set the name of the first table to CustomerPP.
  • Set the name of the second table to PurchasePP.

Two tables used for Excel data model

Data Tables from the records. Image by Author

The two tables are created and the next step is to add these tables to the data model. To do this, go to Power Pivot, and select Add to Data Model.

Add to Excel Data Model under PowerPivot in Excel

Add to Data Model under PowerPivot. Image by Author

This will open a Power Pivot for Excel pop-up that’ll look like this.

Power Pivot for Excel pop-up with Excel data model

Power Pivot for Excel pop-up. Image by Author 

 

Save and close this window. Repeat the same process for the CustomerPP table to add it to the data model.

Power Pivot for Excel pop-up with Excel data model

Power Pivot for Excel pop-up. Image by Author

Once these tables have been added to the data model, you can establish the relationship between them using Power Pivot. To do so, click on the Diagram View.

Diagram View in Excel data model

Diagram View. Image by Author

Next, go to the Design tab and click on Create Relationship.

Create a Relationship tab in Excel data model

Create a Relationship tab. Image by Author

A new window will open. Fill in the details as shown below:

Create a relationship between two tables using Data models in Excel

Create a relationship between two tables. Image by Author

 Click OK. You’ll notice that the relationship is established.

 Relationships are established in Excel Data Model

Relationships are established. Image by Author

It is easy to create a PivotTable from here onwards, as we’ve done in the previous section. The end output should look like this.

PivotTable summary with Excel Data Model

PivotTable summary. Image by Author

Following the steps above, you can use Power Pivot to create the Data Model and create a summary analysis.

How to Create a Data Model with PivotTable or PivotChart

Once you’ve created the PivotTable, it’s easy to insert a PivotChart. With the PivotTable (Sheet 1) selected, go to the Insert tab.

PivotTable summary inside an Excel Data Model

PivotTable summary. Image by Author

Click on PivotChart, select the chart type and click OK.

Using Data Models in Excel with the Insert Chart options

Insert Chart options. Image by Author

This will create the following PivotChart.

Using Data Models in Excel with PivotCharts

PivotChart visualization. Image by Author

You can customize the PivotChart by:

  • Adjusting the chart elements, such as titles, legends, and axis labels, and
  • Formatting the chart to improve its appearance and readability.

Great, you’ve used the PivotTable to build your first visualization.

Common Excel Data Model Questions and Issues

Here are some of the questions and issues commonly associated with Excel Data models:

What should I do if a data model issue is preventing Microsoft Excel from opening a workbook?

This issue can occur due to corruption in the data model or compatibility problems. Here are steps to resolve it:

  1. Open in Safe Mode: Try opening Excel in Safe Mode by holding down the Ctrl key while starting Excel. This can sometimes bypass the issue.
  2. Check for Updates: Ensure Excel is up to date by going to File > Account > Update Options.
  3. Repair Office: Use the Office repair tool. Go to Control Panel > Programs and Features, select your Office installation, and choose Change > Repair.
  4. Disable Add-ins: Go to File > Options > Add-ins and disable all add-ins. Restart Excel and try opening the workbook again.
  5. Extract Data: If you cannot open the workbook, try using a data recovery tool or contacting Microsoft support for advanced assistance.

How can I troubleshoot errors in the data model?

To troubleshoot errors in the data model:

  1. Check Data Types: Ensure the data types in columns used for relationships are consistent.
  2. Validate Relationships: Verify that all relationships are correctly defined.
  3. Review Calculations: Check calculated columns and measures for errors.
  4. Refresh Data: Ensure all data connections are updated.
  5. Error Messages: Review any error messages in the Power Pivot window for specific issues.

By following these steps, you can effectively manage and troubleshoot your data model in Excel, ensuring smooth and efficient data analysis. Microsoft Support is another helpful resource for troubleshooting. 

Final Thoughts

Thanks for completing the tutorial! You’ve learnt an important concept in your journey to master data analysis using Excel. Creating data models will significantly enhance your and your organization’s data analysis capabilities. It also increases your efficiency in managing different data tables and records. 

For further learning, consider exploring the following sources:   

  • Introduction to Excel: A comprehensive guide to get you started with Excel, covering basic functionalities and features.
  • Data Analysis in Excel: Learn various data analysis techniques and tools available in Excel to enhance your analytical skills.
  • Excel Fundamentals: A course that covers the fundamental concepts and operations in Excel to build a strong foundation.
  • Data Visualization in Excel: Explore how to create impactful and interactive visualizations using Excel's built-in tools.
  • Introduction to Power Query in Excel: Understand how to use Power Query to import, clean, and transform data efficiently in Excel.

Additionally, learn How to Earn a Microsoft Excel Certification to validate your skills.


Photo of Vikash Singh
Author
Vikash Singh

Seasoned Professional in Data Science, Artificial Intelligence, Analytics, and Strategy Functions with over 18 years of experience in the areas of -: Data Science, ML and AI ~ Data Science, Supervised and Unsupervised Machine Learning, Deep Learning, Predictive Modeling, Natural Language Processing (NLP), Statistical Modeling & Analysis, Optimization, Business Strategy and Analytics ~ Developing & Evaluating Business Models, Descriptive and Diagnostic Analytics, EDA, Visualization, Root Cause Analysis, Sensitivity and Scenario Analysis.

Frequently Asked Questions

What is a data model in Excel, and how is it useful?

With a data model in Excel, it is possible to join data from multiple sources into one data source. This enables you to not just perform complex analysis using PivotTables and PivotCharts; but also allows you to make many-to-many calculations and relationships without redundancy in your data.

Can I update my data model if my source data changes, and how do I do it?

Yes, you can easily update your data model if your source data changes. To do this, all you need to do is to go to the Data tab and click Refresh All. This will update the data in your model and any associated PivotTables and PivotCharts.

Can I use data from external databases in my Excel data model?

Yes, you can use data from external databases. Go to Data > Get Data and select from the appropriate source. Next, follow the prompts to connect to the database, select the tables you need, and add them to the data model.

How do I remove a table from the data model?

To remove a table from the data model, go to Data > Manage Data Model to open the Power Pivot window. Select the table you want to remove, right-click on it, and select Delete.

What are the benefits of using Power Query for data modeling in Excel?

The benefits of using Power Query for data modeling in Excel include data transformations, automation, integration with various sources, intuitive user-friendly interfaces, and scalability.

Topics

Learn Excel with DataCamp

Course

Data Preparation in Excel

3 hr
23.8K
Understand how to prepare Excel data through logical functions, nested formulas, lookup functions, and PivotTables.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

Data Modeling in Power BI Tutorial

Discover what data modeling in Power BI is and how good data modeling practices can take your Power BI reports to the next level.
Joleen Bothma's photo

Joleen Bothma

11 min

tutorial

Power BI Matrix: A Comprehensive Guide

Enhance your data analysis skills by learning how to customize a Power BI matrix.
Joleen Bothma's photo

Joleen Bothma

10 min

tutorial

Data Types in Excel and Their Uses: A Complete Guide

Learn to identify and format all types of data in Excel, then explore valuable tips on converting between data types to make your spreadsheet more functional.
Laiba Siddiqui's photo

Laiba Siddiqui

9 min

tutorial

How to Create a Dashboard in Excel in 3 Easy Steps

Learn everything you need to know about how to create a dashboard in Excel, with tips and examples.
Joleen Bothma's photo

Joleen Bothma

12 min

tutorial

Power BI Tutorial for Beginners

Learn the basics of Power BI and how to create a basic report with this step-by-step tutorial.
DataCamp Team's photo

DataCamp Team

16 min

tutorial

Mastering Predictive Analytics with Power BI: A Comprehensive Guide for Data Practitioners

Learn how to use Power BI for predictive analytics and create your first model in Power BI.
Joleen Bothma's photo

Joleen Bothma

12 min

See MoreSee More