Repeat the process to convert the lookup values to a Table and name it ShelfCodesTable. Figure C Name the Table objects so they’re easier to work with. I named the original data set ProduceTable.įigure B Convert the data set into a Table. With the new Table still selected, enter a meaningful name in the Name Box control (to the left of the formula bar ( Figure C).Check or uncheck the My table has headers options.Press Ctrl+t or click the Insert tab and click Table in the Tables group.
The example data sets have been converted already, but you might need to know how to do this. The Data Model works only with Table objects. You can’t create a relationship between ordinary data sets. SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic) Convert the data to Table objects By relating the two data sets, you can combine the data in meaningful ways. If you’re unfamiliar with the term, a relationship connects two sets of data by a common column (field) of values. If you’ve worked with databases, the term relationship is known to you.
Figure A Two data sets related by the Shelf Code field. Displaying the description instead of the shelf code will improve the readability of the final product. 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. We have two tables: the data table on the left and the lookup table on the right. In this case, the common field is Shelf Code, as shown in Figure A. Thanks to Excel’s Data Model, you can bypass VLOOKUP() altogether and move straight on to the PivotTable.Įxcel’s Data Model creates a relationship between two (or more) sets of data using a common field.
Then, you’d most likely use a PivotTable to analyze the data set that now includes the description for each product. But, even if it slows things down, it still works. It’s what we know, and it works well unless you have thousands of records to analyze. Most of us would use VLOOKUP() to add a column to the original data set. So, you import a table of shelving codes that includes a helpful description, but how do you add the description with each record? You’ve imported a table of products and each product has a shelving code, which is, meaningless to you. Now let’s suppose you’re working for a large grocery franchise and you want to analyze shelving data. How to create a fun Fly In effect in PowerPoint Learn advanced Excel skills for less than $35 However, you can’t implement the Data Model in the browser. Excel 365’s browser edition supports PivotTable objects. The Data Model is available in versions 20.
You can work with your own data or download the demonstration.
I’m using Excel 2016 on a Windows 10 64-bit system. 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. The model works behind the scenes and simplifies PivotTable objects and other reporting features. This feature lets you integrate data from multiple tables by creating relationships based on a common column.
Here's how to use it to make data analysis easier.Įxcel can analyze mountains of data, but you might be working too hard if you’re not utilizing the Data Model feature to corral it. How to use Excel’s Data Model to turn related data into meaningful informationĮxcel's Data Model feature allows you to build relationships between data sets for easier reporting.