Creating a Data Model in Excel
Last Updated : 23 Jan, 2023
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.
Dataset 2: It contains product information with the product ID, category, size, and price field.
Dataset 3: It contains sales information with the Sales ID, Date, Customer ID, Product ID, and Quantity fields.
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.
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.
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.
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.
Step 5: Now it shows the customer sheet just check it if it is not checked and then again click on FINISH.
Step 6: Now sheet 1 is successfully imported with all the rows in the Power Pivot table. Now just click on Close.
Step 7: Now as you can see Customer table is successfully imported with all the fields.
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.
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.
Step 10: Now go to the home tab on the top of the ribbon and then select Diagram view from the view group.
Step 11: Now you can see it creates a dataset of all three tables i.e., Customer, Sales, and Product.
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.
Step 13: Go to the home tab and then select the Pivot table option from the top of the ribbon.
Step 14: Create PivotTable dialog box appears. Here just select the new worksheet and then click on OK.
Step 15: Now in the Excel sheet pivot table is open with the PivotTable fields pane that shows all three tables.
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.
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.
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.
Similar Reads
How to Create a Dashboard in Excel Creating a dashboard in Excel transforms raw data into actionable insights, making it easier to track performance and make informed decisions. The dashboard helps users to understand and analyze the complex data in an easy way. Hence, nowadays most of the business professionals and analysts want to
6 min read
Perl | Creating Excel Files Excel files are the most commonly used office application to communicate between computers. It is used to create rows and columns of text, numbers, and formulas for calculations. It is a good way to send reports. This demonstration works on Linux, Windows and other platforms as well. In excel, rows
3 min read
How to Create a Two-Variable Data Table in Excel? Two-Variable Data Table is a very significant tool for what-if data analysis. With the help of two-variable data tables, we can find all possible trends that can arrive by changing different values. For example, if we know the annual sales of a company, its percentage of expenses and growth. So, by
2 min read
Data Models in Excel Power View The Data Model gives us the ability to work with multiple tables and integrate them with each other using a specified relationship to build a relational database inside Microsoft Excel. It helps to work with larger datasets and provides us with a huge set of interactive features for creating, modify
5 min read
How to Plot Bivariate Data in Excel? Bivariate data is the most used type of data representation for the plotting of scatter plots . The data depends on two variables as its name suggests, and it is analyzed using different machine-learning algorithms, using different charts, etc. Bivariate can be performed using different methods. Amo
8 min read