EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Data Model in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Data Model in Excel

By Manisha SharmaManisha Sharma

Data Model in Excel

Excel Data Model (Table of Contents)

  • Introduction to Data Model in Excel
  • How to Create a Data Model in Excel?

Introduction to Data Model in Excel

The data model feature of Excel enables the easy building of relationships between easy reporting and their background data sets. It makes data analysis much easier. It allows the integration of data from a plethora of tables spread across multiple worksheets by simply building relationships between matching columns. It works completely behind the scene and greatly simplifies reporting features such as PivotTable etc.

Our article shall attempt to show how to create a pivot table from two tables by employing the Data Model feature, thus establishing a relationship between two table objects and thereby creating a Pivot Table.

How to Create a Data Model in Excel?

Let’s understand how to create the Data Model in Excel with a few examples.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this Data Model Excel Template here – Data Model Excel Template

Example #1

  • We have a list of products, and we have a shelving code for each product. We need a table where we have the shelving description along with the shelving codes. So how do we incorporate the shelving descriptions against each shelving code? Perhaps many of us would resort to using VLOOKUP here, but we shall altogether remove the need to use VLOOKUP here using Excel Data Model.

Data Model in Excel 1-1

  • The table on the left is the data table, and the table on the right is the lookup table. As we can see from the data, it is possible to create a relationship based on common columns.

Data model in excel 1-2

  • Now, the Data Model is only compatible with table objects. So, it might be necessary sometimes to convert data sets to table objects. To do that, follow the below steps.
  1. Left-click anywhere in the data set.
  2. Click the Insert tab and navigate to Table in the Tables group or simply press Ctrl+T.
  3. Uncheck or check the My Table has the Header option. In our example, it does indeed have a header. Click OK.
  4. While still focused on the new table, we need to provide a name that is meaningful in the Name box (towards the left of the formula bar).

In our example, we have named the table Personnel.

Data model in excel 1-3

  • Now we need to do the same process for the lookup table as well and name it Shelf Code.

Creating a Relationship

So firstly, we shall go to the Data tab and then select Relationships in the Data Tools subgroup. After we click on the Relationships option, in the beginning, since there is no relationship, hence we will have nothing.

data model in excel - Relationship

We will first click on New to create a relationship. We will now need to provide the primary and the lookup table names from the drop-down list and then also mention the column which is common between the two tables so that we can establish the relationship between the two tables from the drop-down list of columns.

Create a Relationship 2

  • Now, the primary table is the table that has the data. It is the primary data table – Table5. On the other hand, the Related table is the table that has the lookup data – it is our lookup table ShelfCodesTable. The primary table is the one that is analyzed based on the lookup table, which contains lookup data that will make the reported data, in the end, more meaningful.

create Relationship

  • So, the common column between the two tables is the Shelf Code column. This is what we have used to establish the relationship between the two tables. Coming to the columns, the Column (foreign) is the one that refers to the data table where there can be duplicate values. On the other hand, the Related Column (primary) refers to the column in the lookup table where we have unique values. We are simply setting up the field to lookup values from the lookup table in the data table.
  • Once we set this up, Excel would create a relationship between the two behind the scene. It integrates the data and creates a data model based on the common column. This is light on the memory requirements and much faster than using VLOOKUP in large workbooks. After defining the Data model, Excel would be treating these objects as Data Model tables instead of a worksheet table.
  • Now to see what Excel has been up to, we can click on Manage Data Models in Data -> Data Tools.

Data Tools 1

  • We can also get the diagrammatic representation of the data model by changing the view. We will click on the View option. This will open up the view options. We will then select the Diagram View. Then we will see the diagrammatic representation, showing the two tables and the relationship between them, i.e. the common column – Shelf Code.

Diagram view1

  • The diagram above shows a one-to-many relationship between the unique lookup table values and the data table with duplicated values.
  • Now we will have to create a pivot table. To do that, we will go to the Insert tab and then click on the Pivot Table option.

Data Tools 3

In the Pivot table’s Create Pivot Table dialogue box, we will select the source as “Use this workbook’s Data Model”.

Create Pivot table

  • This will create the Pivot table, and we can see that both the source tables are available in the source section.

Pivot Table fields

  • Now we shall create a pivot table showing the count of each person who has shelved items.

Pivot Table fields 1

  • We will select Personnel in the Rows section from Table 5 (data table), followed by Description (lookup table).

Description 1

  • Now we shall drag the Shelf Code from Table 5 into the Values section.

Table 5

  • Now we shall add Months from Table 5 to the Rows section.

add Months

  • Or we could add the months as a filter and add it to the Filters section.

Filters section

Example #2

  • We now have Mr Basu running a factory called Basu Corporation. Mr Basu is trying to estimate the revenue for 2019 based on the data from 2018.
  • We have a table where we have the revenue for 2018 and the subsequent revenue at different incremental levels.

Data Model in Excel 2-1

Data Model in Excel 2-2

Data Model in Excel 2-3

  • So, we have the revenue for 2018 – $1.5 M, and the minimum growth expected the following year is 12%. Mr Basu wants a table that will show the revenue at different incremental levels.
  • We will create the following table for the projections at different incremental levels for 2019.

Data Model in Excel 2-4

  • Now we shall give the first Revenue row a reference to the estimated minimum revenue for 2019, i.e. $1.68 M.

Data Model in Excel 2-5

  • After using the formula, the answer is shown below.

Data Model in Excel 2-6

  • Now we shall select the entire table, i.e. D2:E12 and then go to Data ->Forecast ->What-If Analysis ->Data Table.

Data Model in Excel 2-7

Data Model in Excel 2-8

  • This will open up the Data Table dialog box. Here we shall enter the minimum increment percent from cell B4 in the Column Input cell. The reason for that is that our projected estimated Growth percentages in the table are arranged in a columnar fashion.

Estimated Growth Percentages 1

Estimated Growth Percentages 2

  • Once we click OK, the What-If Analysis will automatically populate the table with projected revenue at the different incremental percentages.

Project Revenue

Example #3

  • Now suppose we have the same scenario as above, except that now we also have another axis to consider. Suppose, in addition to showing the projected revenue in 2019 based on the data of 2018 and the minimum expected growth rate, we now also have the estimated discount rate.

Estimated Discount Rate

Estimated Discount Rate 1

Estimated Discount Rate 2

  • First, we shall have a table shown below.

Estimated Discount Rate 3

  • Now we shall give reference to the minimum projected revenue for 2019, i.e. cell B5 to cell D8.

Estimated Discount Rate 4

  • Now we shall select the entire table, i.e. D8:J18 and then go to Data ->Forecast ->What-If Analysis ->Data Table.

Estimated Discount Rate 5

  • This will open up the Data Table dialog box. Here we shall enter the minimum increment percent from cell B3 in the Column Input cell. The reason for that is that our projected estimated Growth percentages in the table are arranged in a columnar fashion. We shall now also additionally enter the minimum discount percent from cell B4 in the Row Input cell. The reason for that is that our projected discount percentages in the table are arranged in a row-wise fashion.

Estimated Discount Rate 6

  • Click OK. This will make the What-If Analysis automatically populate the table with projected revenue at the different incremental percentages as per the discount percentages.

Estimated Discount Rate 7

Things to Remember About Data Model in Excel

  • Upon successfully calculating the values from the data table, a simple Undo, i.e. Ctrl+Z, will not work. It is, however, possible to manually delete the values from the table.
  • It is not possible to delete a single cell from the table. It is described as an array internally in Excel; hence we will have to delete all the values.
  • We need to properly select the Row Input Cell and the Column Input Cell.
  • The data table, unlike the Pivot Table, doesn’t need to be refreshed every time.
  • Using the Data Model in Excel, we can improve performance and go easy on memory requirements in large worksheets.
  • Data Models also makes our analysis much simpler as compared to using a number of complicated formulae all across the workbook.

Recommended Articles

This is a guide to Data Model in Excel. Here we discuss how to create Data Model in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)
  1. Pivot Table Slicer
  2. PowerPivot in Excel
  3. Pivot Table Formula in Excel
  4. Excel Pivot Table
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Data Model Excel Template

EDUCBA

Download Data Model Excel Template

EDUCBA

डाउनलोड Data Model Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more