Data Model in excel

What is a Data Model in Excel?

A data model concept in excel, might seem a primitive topic, but the wide applications and the simplifications that offer, have caused the data organization process to become much effortless, while maintaining precision and reducing reporting complexities.

In other word, data models, merge or incorporate data from several tables, provided that all the tables possess at least one mutual column.

Data Model Application

It is vital to understand when to use data models. Occasionally, applying this practice may complicate the data process further, while a simple "VLOOKUP()", might have been the way to go.

When the data is expanded into multiple tables, and several interrelated extractions are needed for the analysis and report preparation, data models are the key solution.

• Example:

Consider a data set or a table that consists the anonymous information of your clients, such as ID, address, phone number,... and there is another table, in which data consists of the IDs and names, as well as the monthly purchase of your product/ service, and further it goes, other tables with specific data regarding the purchase channels and customer behaviors. Evidently, the IDs of the customers seem to mutual across all tables. This case, can be a perfect candidate for which data model in excel can be applied, aiming to simplification of the data organization.

Steps to Create a Data Model in Excel, by Generating New Tables

1. First, all the data sets need to be converted into tables. Thus, select all the data range, then by the opening "insert" tab, select the "Table" option.
2. After checking the data range and ensuring that it has been selected correctly, make sure mark the checkbox that indicates "My table has headers". You may change your table name, from the "Table Design" tab, by choosing more specific name in the "Name Box".

3. Connecting Tables

When you click on the "Connections" button, the "Workbook Connections" pop-up window appears. Click on the "Add" drop-down button in the upper right corner. Choose the option that says "Add to the Data Model." Switch from the "Connections" tab to the "Tables" tab. This lets you view all the tables within your workbook.

4. Pivoting the "DataModelWrokbooK"

After data integration, meaning adding all tables into one model, then the developed model can be used to create relationships. By pivoting the prepared model, in "Analyze" tab of the created pivot, by the "Managing Relationship" dialogue box, it is quite simple to create the desired relationships.

Instructive Video Clip: