Excel can analyze mountains of data, but you might be working too hard if you're not utilizing the Data Model feature to corral it. This feature lets you integrate data from multiple tables by creating relationships based on a common column. The model works behind the scenes and simplifies PivotTable objects and other reporting features. In this article, I'll show you how to create a PivotTable using data from two tables by using the Data Model feature to create a relationship between the two tables before building the PivotTable.
I'm using Excel 2016 on a Windows 10 64-bit system. You can work with your own data or download the demonstration .xlsx file. The Data Model is available in versions 2013 and 2016. Excel 365's browser edition supports PivotTable objects. However, you can't implement the Data Model in the browser.
More about Office
- Office Q&A: An easy Word Replace trick for a big problem and exposing duplicate records in Excel
- How to build a simple timesheet that accommodates projects in Excel 2016
- Microsoft SharePoint: A guide for business professionals (Tech Pro Research)
- Tap into the power of Excel’s data validation feature (free PDF)
A simple problem
Now let's suppose you're working for a large grocery franchise and you want to analyze shelving data. You've imported a table of products and each product has a shelving code, which is, meaningless to you. So, you import a table of shelving codes that includes a helpful description, but how do you add the description with each record?
Most of us would use VLOOKUP() to add a column to the original data set. It's what we know, and it works well unless you have thousands of records to analyze. But, even if it slows things down, it still works. Then, you'd most likely use a PivotTable to analyze the data set that now includes the description for each product. Thanks to Excel's Data Model, you can bypass VLOOKUP() altogether and move straight on to the PivotTable.
Excel's Data Model creates a relationship between two (or more) sets of data using a common field. In this case, the common field is Shelf Code, as shown in
Figure A. We have two tables: the data table on the left and the lookup table on the right. Using Excel's Data Model feature, we'll display the description field instead of the shelf code when grouping and analyzing the values without using VLOOKUP() or any other functions. Displaying the description instead of the shelf code will improve the readability of the final product.
Two data sets related by the Shelf Code field.
If you've worked with databases, the term relationship is known to you. If you're unfamiliar with the term, a relationship connects two sets of data by a common column (field) of values. By relating the two data sets, you can combine the data in meaningful ways.
SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic)
Convert the data to Table objects
You can't create a relationship between ordinary data sets. The Data Model works only with Table objects. The example data sets have been converted already, but you might need to know how to do this. Fortunately, it's easy:
- Click anywhere inside the data set.
- Press Ctrl+t or click the Insert tab and click Table in the Tables group.
- Check or uncheck the My table has headers options. In this case, it does (
- Click OK.
- With the new Table still selected, enter a meaningful name in the Name Box control (to the left of the formula bar (
Figure C). Be sure to press Enter. I named the original data set ProduceTable.