The data model in Excel is a type of data table where two or more two tables are in a relationship with each other through a common or more data series. In the data model, tables and data from various other sheets or sources come together to form a unique table that can access the data from all the tables. For example, a data table containing tables of customers, product items, and product sellers. We can use the Excel data model to connect this dataset and create a relationship between them.
You are free to use this image on your website, templates, etc, Please provide us with an attribution link Article Link to be HyperlinkedFor eg: Source: Data Model in Excel (wallstreetmojo.com) ExamplesExample #1We have three datasets related to the salesperson: the first contains revenue information, the second includes the salesperson’s income and the third consists of the expenses of the salesperson. To connect these three datasets and make a relationship with them, we make a data model with the following steps:
We cannot create a relationship with ordinary datasets. The data model works with only Excel 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:
Adding the “Table” objects to the Data Model: Via Connections or Relationships. Via Connections
In doing this, it would create a workbook data model with one table, and a dialog box would appear as follows: So if we repeat these steps for the other two tables, the data model will contain all three tables. 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:
If we repeat these steps to relate the other two tables: the “Revenue” Table with “Expenses” table, then they also get connected in the data model as follows: 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 #2Now, in the above example, we wish to create a PivotTable that evaluates or analyzes the table objects:
It is an immense help in the case of a model/table containing a large number of observations. So, we can see that the pivot table instantly uses the data model (picking it by choosing connection) in Excel memory to show relationships between tables. Things to Remember
Recommended ArticlesThis article is a guide to Data Model in Excel. We discuss creating a data model from Excel tables using connections and relationships, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: – |