Have you ever been in a VLOOKUP hell? Show
Its what happens when you have to write a lot of vlookup formulas before you can start analyzing your data. Every day, millions of analysts and managers enter VLOOKUP hell and suffer. They connect table 1 with table 2 so that all the data needed for making that pivot report is on one place. If you are one of those, then you are going to love Excel’s data model & relationships feature. In simple words, this feature helps you connect one set of data with another set of data so that you can create combined pivot reports. Practical Example – V(X)LOOKUP hell vs. Data Model heavenLets say you are looking sales data for your company. You have transaction data like below. And you want to find out how many units you are selling by product category and customer’s gender. Unfortunately, you only have product ID & customer ID. With VLOOKUP Hell,
Assuming you have 30,000 transactions, you have to write 60,000 VLOOKUP formulas to create this one report!!! With Data Model heaven,
Creating a relationship in Excel – Step by Step tutorial
Using relationships in Pivot reports & analysis
Example: Category & Gender Sales Report
Things to keep in mind when you using relationships
Benefits of Data Model based Pivot TablesOnce you have a data model in spreadsheet, you will enjoy several benefits (apart from multi-table pivots that is). They are,
Drawbacks of Data Model:Of course, its not all cup cakes and coffee with Data Model. There are a few drawbacks of data model based pivot tables.
Download Example FileClick here to download Excel data model demo file. It contains 3 different tables and a combined pivot report (with slicer) to show you what is possible. Do you use relationships?Ever since discovering PowerPivot, I kind of stopped using VLOOKUP (or XLOOKUP) for most of my own analysis. Now that relationships are part of main Excel functionality, I am using them even more. What about you? Are you using relationships & data model in Excel? What cool things are you doing with it? Share your tips with us using comments. Want even more? Try PowerPivotIf you want even more out of your reports, then try PowerPivot. It is a new feature in Excel 2013 (available as add-in in Excel 2010) that can let you do lots of powerful analysis on massive amounts of data. Here is an introduction to PowerPivot. Share this tip with your colleagues Get FREE Excel + Power BI TipsSimple, fun and useful emails, once per week. Learn & be awesome.
Welcome to Chandoo.orgThank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book
Excel School made me great at work. 5/5 From simple to complex, there is a formula for every occasion. Check out the list now. Calendars, invoices, trackers and much more. All free, fun and fantastic. Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here. Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
Related Tips41 Responses to “How to use Excel Data Model & Relationships”
Leave a ReplyCan Excel create relationships between two tables?You can create a relationship between two tables of data, based on matching data in each table. Then you can create Power View sheets and build PivotTables and other reports with fields from each table, even when the tables are from different sources.
What is the data model in Excel?A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts.
Where would you access the Manage Relationships dialog box to create a data model in Excel?Create relationships within your data model
Click on the "Analyze" tab within the Excel ribbon. Choose the "Relationships" button. Within the "Manage Relationships" dialog box, choose the "New" button. Select the tables and columns you want to incorporate into your relationship.
What are the two ways to create the relationship between the tables?Create a table relationship by using the Relationships window. On the Database Tools tab, in the Relationships group, click Relationships. On the Design tab, in the Relationships group, click Add Tables (or Show Table in Access 2013). Select one or more tables or queries and then click Add.
|