Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
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.
What is Data Model in Excel?
A Data Model in excel allows integrating data from multiple tables by creating relationships based on a common column. Data models are used transparently, providing tabular data that can be used in Pivot Tables and Pivot Charts. It integrates the tables, enabling extensive analysis using Pivot Tables, Power Pivot, and Power View.
The data model allows loading data into Excel’s memory. It is saved in memory where we cannot directly see it. Then Excel can be instructed to relate data to each other using a common column. The ‘Model’ part of the Data Model refers to how all tables relate to each other.
With relationships or connections, the Excel Data Model can access all the information it needs even when the information is in multiple tables. After the Data Model is created, Excel has the data available in its memory. With the data in its memory, the data can be accessed in many ways.
Excel Data Models – Example #1
If we have three datasets related to the salesperson: First containing revenue information, a second containing income of salesperson, and third containing expenses of the salesperson.
To connect these three datasets and make a relationship with these, we make a Data Model excel 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 Table objects. To do this:
- Click anywhere inside the dataset then, click on the ‘Insert’ tab and then click on ‘Table’ in the ‘Tables’ group.
- Check or uncheck the option: ‘My Table has Headers’ and click OK.
- With the new table selected, enter the name of the Table in the ‘Table Name’ in the ‘Tools’ group.
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:
Adding the ‘Table’ objects to the Data Model: Via Connections or Relationships.
- Select one table and click on the ‘Data’ tab and then click on ‘Connections’.
- In the resulting dialog box, there is an icon of ‘Add’. Expand the dropdown of ‘Add’ and click on ‘Add to the Data Model’.
- Click on ‘Tables’ in the resulting dialog box and then select one of the tables and click ‘Open’.
On doing this, a workbook Data model would be created with one table and a dialog box appears as follows:
So if we repeat these steps for the other two tables as well, the Excel Data Model will now contain all three tables.
We can now see that all three tables appear in the Workbook Connections.
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’.
- We will see an empty dialog box as there are no current connections.
- Click on ‘New’ and another dialog box appears.
- 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 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.
- With all these four settings selected, click on ‘OK’. A dialog box appears as follows on clicking ‘OK’.
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:
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).
This is how the Data Model is created.
Excel Data Models – Example #2
Now, let’s say in the above example we wish to create a Pivot Table that evaluates or analyzes the Table objects:
- Click on ‘Insert’-> ‘Pivot Table’.
- In the resulting dialog box, click on the option stating: ‘Use an external data source’ and then click on ‘Choose Connection’.
- Click on ‘Tables’ in the resulting dialog box and select the Workbook data Model containing three tables and click ‘Open’.
- Select the ‘New Worksheet’ option in the location and click on ‘OK’.
- The Pivot Table Fields pane will display table objects.
- Now changes in Pivot Table can be done accordingly so as to analyze the table objects as required.
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:
This is of immense help in 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
- Using the Excel Data Model, we can analyze data from several tables at once.
- By creating relationships with Data Model, we surpass the need for using VLOOKUP, SUMIF, INDEX function, and MATCH formulas as we don’t need to get all columns within a single table.
- When datasets are imported in Excel from outside sources, then models are created implicitly.
- Table relationships can be created automatically if we import related tables that have primary and foreign key relationships.
- While creating relationships, the columns that we are connecting in tables should have the same data type.
- With the pivot tables created with data Model, we can add slicers too and slice the pivot tables on any field we want.
- The advantage of the Excel Data Model over LOOKUP() functions is that it requires substantially less memory.
- Excel 2013 supports only one to one or one to many relationships, i.e one of the tables must have no duplicate values on the column we are linking to.
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 downloadable excel template. You may learn more about excel from the following articles –