Open In App

Creating a Data Model in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

In Excel, a data model is a form of the data table in which two or more tables are linked by a common or many data series. Tables and data from many other sheets or sources are combined in the data model to generate a single table that can access data from all tables. A Data Model enables you to combine data from different tables, thereby creating a relational data source within an Excel spreadsheet. Data Models are utilized openly in Excel, supplying tabular data for PivotTables and PivotCharts.

Data Model in Excel

We have three datasets: one with customer information, another with product information, and the third with sales information.

Dataset 1: It contains customer information with the customer ID, customer, Gender, and region field.

dataset1

 

Dataset 2: It contains product information with the product ID, category, size, and price field.

dataset2

 

Dataset 3: It contains sales information with the Sales ID, Date, Customer ID, Product ID, and Quantity fields.

dataset3

 

Step By Step implementation of Data Model

Step 1: In Excel, create a new blank Workbook. Then go to Power Pivot on the top of the ribbon. Then select manage from the Data Model group.

selecting-manage-from-data-model

 

Step 2: Power Pivot for excel sheet is open. Now go to the home tab on the top of the ribbon and then select From other sources from the Get External Data group.

get-external-data-group

 

Step 3: After selecting Table Import Wizard dialog box appears. Here select the Excel file option from the Text files group and then click Next.

clicking-next-from-text-file-groups

 

Step 4: Here Browse the file path, where your file is stored. Make sure to check the Use first row as column header option and then again click Next.

browsing-file-path

 

Step 5: Now it shows the customer sheet just check it if it is not checked and then again click on FINISH.

clicking-finish

 

Step 6: Now sheet 1 is successfully imported with all the rows in the Power Pivot table. Now just click on Close.

sheet1-successfully-imported

 

Step 7: Now as you can see Customer table is successfully imported with all the fields.

customer-table-imported

 

Step 8: Now you need to select the From Other Sources option and follow the same steps again for the Product dataset. In this, you can see after doing all the steps it automatically creates a Product sheet with all the fields.

creating-product-sheet

 

Step 9: Now again you need to select the From Other Sources option and follow the same steps again that are used in the customer dataset for the Sales dataset. Now, you can see after doing all the steps it automatically creates a Sales sheet with all the fields.

automatically-creating-sales-sheet

 

Step 10: Now go to the home tab on the top of the ribbon and then select Diagram view from the view group.

selecting-diagram-view

 

Step 11: Now you can see it creates a dataset of all three tables i.e., Customer, Sales, and Product. 

tables-created

 

Step 12: Now you need to connect all three datasets with each other. First, we are connecting the customer Id field from the sales Table to the customer table because it is common in the sales Table and the customer table. Second, we are connecting the product Id field from the sales Table to the product table because it is common in the sales Table and the product table.

ERD-created

 

Step 13: Go to the home tab and then select the Pivot table option from the top of the ribbon.

selecting-pivot-table

 

Step 14: Create PivotTable dialog box appears. Here just select the new worksheet and then click on OK.

creating-pivot-table

 

Step 15: Now in the Excel sheet pivot table is open with the PivotTable fields pane that shows all three tables.

pivot-table-fields

 

Step 16: Just drag the region field of the customer table to the Rows pane and the category field of the product table to the Columns pane.

dragging-fields

 

Now it will show the Pivot table with the region and the product fields.

Step 17: Drag the Quantity field from the sales table to the Values pane. Then it shows all the quantities according to the product and region and also it sums all the quantities.

dragging-fields

 

In only a few minutes, you might evaluate your data from several tables and get the desired report. Because of the pre-existing linkages between the tables in the source database, this was achievable. Excel rebuilt the associations in its Data Model when you imported all of the tables from the database at once. If you do not import the tables at the same time, the data comes from separate sources, or you add additional tables to your Workbook, you must manually build the Relationships between the Tables.



Last Updated : 23 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads