Data Model in Excel

What is the Data Model in Excel?

Data model in excel is a type of data table where we two or more than two tables are in relationship with each other through a common or more data series, in data model tables and data from various other sheets or sources come together to form a unique table which can have access to the data from all the tables.

Explanation

Data Model in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Data Model in Excel (wallstreetmojo.com)

Examples

You can download this Data Model Excel Template here – Data Model Excel Template

Example #1

If we have three datasets related to the salesperson: First containing revenue information, the second containing the income of the salesperson, and the third containing expenses of the salesperson.

Data Model Example 1

To connect these three datasets and make a relationship with these, we make a Data Model with the following steps:

  • Convert the datasets to Table objects:

We can’t create a relationship with ordinary datasets. The Data Model works with only Excel TablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more objects. To do this:

  • Step 1 – Click anywhere inside the dataset then, click on the ‘Insert’ tab and then click on ‘Table’ in the ‘Tables’ group.
Data Model Example 1-1
  • Step 2 – Check or uncheck the option: ‘My Table has headers’ and click OK.
Data Model Example 1-2
  • Step 3 – With the new table selected, enter the name of the Table in the ‘Table Name’ in the ‘Tools’ group.
Data Model Example 1-3
  • Step 4 – Now we can see that the first dataset is converted to ‘Table’ object. On repeating these steps for the other two datasets, we see that they also get converted to ‘Table’ objects as below:
Data Model Example 1-4

Adding the ‘Table’ objects to the Data Model: Via Connections or Relationships.

Via Connections

  • Select one table and click on the ‘Data’ tab and then click on ‘Connections.’
Model Via Connections Example 1-5
  • In the resulting dialog box, there is an icon of ‘Add.’ Expand the dropdown of ‘Add’ and click on ‘Add to the Data Model.’
Model Via Connections Example 1-6
  • Click on ‘Tables’ in the resulting dialog box and then select one of the tables and click ‘Open.’
Model Via Connections Example 1-7

On doing this, a workbook Data model would be created with one table and a dialog box appears as follows:

Model Via Connections Example 1-8

So if we repeat these steps for the other two tables as well, the Data Model will now contain all three tables.

Model Via Connections Example 1-9

We can now see that all three tables appear in the Workbook Connections.

Via Relationships

Create the relationship: Once both the datasets are Table objects, we can create a relationship between them. To do this:

  • Click on the ‘Data’ tab and then click on ‘Relationships.’
Model Via Relationships Example 1-10
  • We will see an empty dialog box as there are no current connections.
Model Via Relationships Example 1-11
  • Click on ‘New,’ and another dialog box appears.
Model Via Relationships Example 1-12
  • Expand the ‘Table’ and ‘Related Table’ dropdowns: ‘A Create relationship’ dialog box appears to pick the tables and columns to use for a relationship. In the expansion of ‘Tables,’ select the dataset we wish to analyze in some way, and in ‘Related Table,’ select the dataset that has lookup values.
  • The lookup table in excel is the smaller table in case of one to many relationships, and it contains no repeated values in the common column. In the expansion of ‘Column (Foreign),’ select the common column in the main table, in ‘Related Column (Primary),’ select the common column in the related table.
Model Via Relationships Example 1-13
  • With all these four settings selected, click on ‘OK.’ A dialog box appears as follows on clicking ‘OK.’
Model Via Relationships Example 1-14

If we repeat these steps to relate other two tables: Revenue Table with Expenses table, then they also get related in the Data Model as follows:

Data Model Example 1-15

Excel now creates the relationship behind the scenes by combining data in the Data Model based on a common column: Salesperson ID (in this case).

Example #2

Now, let’s say in the above example, we wish to create a Pivot Table that evaluates or analyzes the Table objects:

  1. Click on ‘Insert’-> ‘Pivot Table.’


    Data Model Example 2

  2. In the resulting dialog box, click on the option stating: ‘Use an external data source’ and then click on ‘Choose Connection.’


    Data Model Example 2-1

  3. Click on ‘Tables’ in the resulting dialog box and select the Workbook data Model containing three tables and click ‘Open.’


    Data Model Example 2-2

  4. Select the ‘New Worksheet’ option in the location and click on ‘OK.’


    Data Model Example 2-3

  5. The Pivot Table Fields pane will display table objects.


    Data Model Example 2-4
    Now changes in Pivot Table can be done accordingly so as to analyze the table objects as required.

  6. For instance, in this case, if we wish to find the total revenue or revenue for a particular salesperson, then a Pivot Table is created as follows:


    Data Model Example 2-5

    Data Model Example 2-6
    This is of immense help in the case of a model/table containing a large number of observations.

So, we can see that Pivot Table instantly uses the Data Model (picking it by choosing connection) in Excel memory to show relationships between tables.

Things to Remember

Recommended Articles

This has been a guide to Data Model in Excel. Here we discuss how to create a data model from excel tables using Connection & Relationship along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>