How & When To Create A Data Model [5 Simple Steps]

Excel data models are made to help users integrate data from multiple tables.

It is of great use to professional Excel users as it saves tons of effort and time that otherwise goes into collating data manually.

All that you need to know about creating a data model in Excel is pulled together in the article below.

Data models are very common in todays workplace. Delegates on our Excel Courses ask about them every week!Data Model Cartoon 1

What Is A Data Model?

The Data Model feature of Excel is specifically designed to help users collate data scattered across multiple data tables.

Under data models, two or more tables are transformed into one based on one or more common columns.

Using data modeling, you can bring together data from multiple tables or external sources on the platform.

A data model offers a wide variety of advantages to Excel users, some of which are as follows.

  1. They  enable Excel users to create Pivot Tables with sheer ease.
  2. As it helps integrate tables, it is a useful tool when it comes to data analysis using Power View in Excel.
  3. It enables the export of data from different external data sources.
  4. It is a great substitute for the complicated VLOOKUP function of Excel.

Having all your data in one place will not only help your efficiency, but show your supervisor you know how to keep multiple spreadsheets under control.

How To Make A Data Model

To learn how to create a data model in Excel, let’s quickly stipulate an example.

Below is a screenshot that shows the sales data of a Company.

Scattered data related to sales

However, the main table only includes the country code which is of little use until the parallel Region against each code is mentioned against each sale.

The information of the corresponding region against each country code is available in the parallel table.

To combine them both in a Data Model, here are the steps to be followed.

Step 1:

First, take note of the type of data. The data related to sales is simply populated in an Excel file.

However, as data modeling integrates data tables, it is important to ensure that the source data is in the form of data tables.

To do so, select each dataset (Including headers) and turn it into a data table as follows.

Insert > Table

Once this is done, here is what the data tables should look like.

Data transformed into data tables

Step 2:

Next, name each table for ease of building connections.

Select each table > Tools > Table Name

Naming the tables

Once this is done, all your tables would be uniquely named.

We have named the first table as ‘Sales’ and the other as ‘Country’ depending upon the information contained therein.

Step 3:

Next, you need to build a relationship between both tables through the following route.

Data > Relationships

This opens up the ‘Manage Relationships’ dialogue box as follows.

Manage Relationships dialogue box

Click on ‘New’ as shown above to form a relationship between both the data tables.

This takes you to the ‘Edit Relationship’ dialogue box.

To relate both the Table and the Related Table, select the column common between both.

As we have the Country Code common to both, we have selected the same as the Column (Foreign) and the Related Column (Primary).

Edit Relationship

This forms a relationship between both tables.

See more details of building relationships in Excel in the segment that follows.

Once the relationship is formed, it appears under ‘Manage Relationships’ as follows.

Relationship added to Excel

Step 4:

Next, create a pivot table by simply launching the editor as follows.

Insert > Tables > Pivot Table

This opens up the Create PivotTable dialogue box as follows.

PivotTable dialogue box

As we have already built relations among the data tables, a data model is formed in Excel memory.

Simple select the option ‘Use this workbook’s Data Model’ and the destined location as highlighted above.

PivotTable created

Step 5:

Both the tables now appear under the Pivot Table fields.

To categorize the sales made to each customer in terms of country of sale along with the sum of sales achieved each month, expand each table by clicking on it.

Drag the column ‘Region’ and ‘Customer Name’ to Rows.

Similarly, take ‘Months’ under the box Column and ‘Sales’ to the Values box to have the month-wise sales populated against each region and customer.

Pivot Table fields

Here are how the final results look after all the necessary formatting.

Sales organized country-wise

Learn more Excel data analysis techniques here.

Data Model Cartoon 4

Data Model Use Cases

Data Models, being one of the most advanced functions of Excel, are of great use to both regular and proficient Excel users.

Here are a few practical cases where you may readily employ them.

  • Globally spread businesses often prepare individual accounts for each region.
    • These accounts are then consolidated together to prepare the summary financials for the entire business.
    • A data model can be conveniently used to consolidate these accounts.
  • If you prepare your domestic budgets using Excel and have all your monthly bills scattered across different files:
    • You can bring them along in a single table using a data model.

In addition to the foregoing, a data model can surprisingly help you with your routine data analytics.

Especially for businesses who use Excel for accounting and bookkeeping, data modeling is their go-to tool.

Creating Relationships

Creating relationships between tables turns out to be a tricky step of the data modeling exercise.

However, it only requires you to understand how things work at the Excel end.

While creating relationships, you will come across four options, each of which is explained below.

  • Table: This refers to the table that contains the main data to be linked with other data in Excel.
  • Column (Foreign): This refers to the column in the above table where the values are to be looked up.
  • Related Table: This refers to the table where the values are to be looked up for.
  • Related Column (Primary): This refers to the column in the related table that is common to the main table.

Excel links both the tables based on these two, foreign and primary columns.

Let’s create multiple relationships through the example below to see how the linking works.

Below is a data set that contains multiple tables relevant to the sales of a Company.

Multiple data sets to be turned into a data model

To create relationships among them all, here are the steps to be followed.

Step 1:

First of all, ensure all the tables of your data set are constructed as a ‘Table’ in Excel.

If not, Excel won’t recognize them as tables when creating relationships.

You can quickly turn your dataset into a table by going to Insert > Table.

Next, name each table for ease of reference. We have named all the three tables as Sales, Country, and Customer ID, respectively.

Data converted into tables

Step 2:

Go to Data > Data Tools > Relationships.

Relationships on the Data Tab

Pro Tip: If the ‘Relationships’ option of your data tab is greyed out (frozen for selection), probably your Excel sheet consists of only a single table.

Must note that while creating relationships, Excel considers tables of all the Excel books launched at any given time and not a single book.

From the ‘Manage Relationships’ window, select ‘New’ as shown below.


Manage Relationships Window

This opens up the ‘Edit Relationship’ window.

Now, to create a relationship between the first two tables i.e. Sales and Country, let’s start choosing the options.

Under the option Table, select ‘Sales’ and under the option Related Tables, select the option ‘Country’.

Now to link both these tables, select the column that is common to both the tables under the option Column (Foreign) and Related Column (Primary).

This is how it should look like when populated.

Edit Relationships Window

Click ‘Okay’, and Excel would construe it as a relationship.

Note: The Related Table (Country) where the value is to be looked up contains unique values i.e. no region is repeated.

However, the same country code appears for more than once in the main table (Sales). For example, 42009 appears at Serial No. 1 and Serial No. 7.

The Related table must only contain unique values. Such relations are known as one-to-many relationships.

Looking for more Excel tips? Read our guide on how to Recover Unsaved Files in Excel here!

Step 3:

Do the same to build a relationship with the last table.

Open the ‘Edit relationship’ window and select Sales as the Table, and Customer ID as the Related Table.

Next, under the foreign and primary column options select the column that is common to both i.e. Customer ID.

Edit Relationships window all populated

Hit ‘Okay’, and there you have your relationships created.

You can view the relationships created in Excel under the ‘Manage Relationships’ window as follows.

Relationships created in Excel

Power Pivot Diagram View:

Also, once you have created relationships, you can go over them through Power Pivot Diagram.

You may access Power Pivot Diagram through the following route.

Data > Data Tools > Manage Data Model

Within the window that opens up, select ‘Diagram View’ from ‘View’ in the Home Tab as highlighted below.

This shows a diagram view of the relationship you have created.

Power Pivot Diagram View

Pro Tip: If the ‘Manage Data Model’ option is greyed out, you probably don’t have it enabled, or your Excel version is not supportive of it.

You can enable the Data Analysis add-in once prompted after you click on the said option.

For more on Excel Functions, read our guide here on the XMATCH Function!

Data Model Cartoon 2

Add Data To Your Data Model

After we have come across the tricky exercise of putting together models in an Excel sheet, what if some datasets are still left out?

For Excel users who employ models, it is often the case.

At times, this is an indispensable part of the data collation phase as the underlying data may undergo regular updates.

Or even otherwise, you may forget to add a chunk of data (a table or two) to your final data model.

In either case, embedding additional data into your data model is easier than you think.

This can be primarily done as follows.

If you’ve already constructed your Pivot Tables, simply add data to the excel data model.

Or, you may quickly set up a Pivot table using the existing data model tables and then add additional data to it.

Data model in Pivot Table

However, the following chunk of data needs to be incorporated in the foregoing data model too.

Data to be added to the data model

Step 1:

Convert the data that you want to be added to the data model in a table. Simply select it and turn it into a table as follows.

Insert > Table

Step 2:

Select this table (or a cell from this table) and go as follows.

Data > Get & Transform Data > From Table / Range

Step 3:

Within the home tab of Power Query Editor, select the option ‘Close and Load’ as appears on the left side of the panel.

Importing the data to Power Query Editor

Step 4:

This opens up the ‘Import Data’ dialogue box as follows.


Import Data


As shown in the image above, select how you want the data to be viewed in the Workbook.

As we only want to embed the data into the data model, select ‘Only Create Connection’.

For the location of the data, select ‘Add this data to the Data Model’.

And that’s it – you have your data added to your data model.

It should now appear as a field under the PivotTable fields.

You can expand it to incorporate it in your Pivot Table as required.

We have added the Customer ID along with the name of each customer in our Pivot Table as shown below.

Additional data added to existing Data Model

Pro Tip: What if you don’t want to add a whole new table to the Pivot Table but only a new column or row?

Simply make the desired changes to the source data (the tables included in the Pivot Table) and go to Data > Refresh > Refresh All.

Making A Pivot Table

Forming Pivot Tables from a data model is a short process with a few thoughtful clicks, and you are all done.

Take a look at the example below to see how you can quickly assemble a Pivot Table out of a Data Model.

Using the same data as above, we have three tables; each containing different information relevant to the sales of a Company.

Data to be turned into a Pivot Table

To convert multiple tables into a Pivot Table, you first need to put them together as a Data Model.

To do so, let’s start creating relationships between them.

As is evident, the common column between the first two tables, i.e. Sales and Country is Country Code.

Whereas, the common column between the table Sales and CustomerID is, Customer ID.

Using the same information, we have built relationships as follows.

Relationships created among data

Next, launch the Power Pivot Table as follows.

Insert > Tables > PivotTable

This launches the ‘Import Data’ dialogue box.

Import data into Pivot Table

As we’ve already created a data model by creating relationships among tables, check the Option ‘Use this workbook’s data model’.

Also, select the destination where you want the Pivot Table to be located, be it in the same worksheet or a new one.

Hit ‘Okay’ and Excel would form a Pivot Table. Next, drag and drop the Power Pivot fields to rows, columns, values, and filters to have your Pivot Table presented to your choice.

Pivot Table created in Excel

Take a look into the fields column to see how each table appears there by the name designated to it. Click on it to expand it column-wise as shown below.

Tables in Pivot Table fields

Format the sales amount as currency, add some borders and brush the final look to have a month-wise sale report of each region ready.

Final Pivot Table created from a data model

There’s much more to Power Query that you may want to learn.

Data Model Cartoon 3


At a glance, data modeling in Excel might seem like adding unnecessary chores to your plate.

However, that is only in case you have a simple set of data whereby VLOOKUP might act as a time-saver.

As the data grows in magnitude, you’d vouch for data modeling as it helps collate data efficiently and effortlessly.

Try practicing using different datasets to master the technique of simplifying data, no matter how extensive.

Looking for more top tips on Excel? Read our guide here to Creating a Flowchart in Excel or our article about creating a histogram.




About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.

He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.

He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.