Excel data models are purported to help users with integrating 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 data models in Excel is pulled together in the article below.

Additionally, learn much more about other endless useful functions of Excel in our Excel Courses here. Only 48% of people have ever recieved any formal Excel training – this course can put you above just over half of the average candidate! For more Excel Research look here.

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. Data models offer a wide variety of advantages to Excel users, some of which are as follows.

  1. Data models enable Excel users to create Pivot Tables with sheer ease. It creates connections between tabular data that helps quick formation of Pivot tables and charts.
  2. As it helps integrate tables, it is a useful tool when it comes to data analysis using Power View in Excel. Learn more about Pivot Tables in excel here.
  3. It enables the export of data from different external data sources. The same is saved in Excel’s memory and can be used in many ways.
  4. It is a great substitute for the complicated VLOOKUP function of Excel.

To form data models in Excel, all you need is a little smart work. Look into the example that follows to learn how.

 

How To Make A Data Model – Simple Example

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 Power Query 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.

This creates a Pivot Table as follows.

 

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 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 Data Models.

  1. 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. Data Models can be conveniently used to consolidate these accounts.
  2. 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 data models.

In addition to the foregoing, data models 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 Between Tables

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 that is common to other tables 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.

Examples always explain better, don’t they? 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.

 

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!

 

Add Data to your Data Model

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

For Excel users who employ data 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 a Pivot Table, simply add data to the data model. Or, you may quickly set up a Pivot table using the existing data model tables and then add additional data to it.

Let’s continue with the same Pivot Table that we constructed above to see how.

 

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:

This would take the table to the Power Query Editor. 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.

 

Make a Pivot Table from a Data Model

Forming Pivot Tables from Data Models 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. Tap here to know.

 

Conclusion

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.