Excel 2013 Feature - Excel Data Model Tutorial

Editor’s note: The following post was written by Excel MVP Ben Currier

PivotTables have certainly become one of the most powerful ways to quickly and easily transform data into information. Yet there are still many Excel users who, for one reason or another, find themselves avoiding PivotTables. Well, Microsoft has made a lot of strides over the years to introduce new functionality and options to make PivotTables easier and more intuitive to use. With Excel 2013, Microsoft has introduced many new PivotTable features, including the new Excel Data Model which takes PivotTables to a whole new level!

Turning Data into Information

One of the limitations of PivotTables has been the fact that you could only use information from one data table, requiring any additional information to be added into the table in order to be included in the PivotTable. In many instances, you won’t have a lot of control over the format of the initial data or the fields provided, especially when this is pulled into Excel from some other software. For example, you may have Travel Expenses for employees that you’ve extracted from your accounting system that looks something like this:

This is a perfectly fine range of data, and you could certainly make a PivotTable based off of it, but there are a few issues. Unless you’re very familiar with your company’s internal coding, the ‘Dept Code’ and the ‘Employee Type’ fields likely won’t provide you with too much information. Especially if the output is being provided to someone without extensive systems knowledge, you’ll need to convert these codes into understandable values to provide the most clear and coherent information (see Figure 1-2 below for the related lookup ranges).

In prior versions of Excel, you would likely use a VLOOKUP function (or some other convoluted method) to add additional columns to bring in the associated Department or Employee Level. Doing this introduces more room for error (based on the quality of the formula), and is a strain on calculation speed and memory.

With the introduction of the Excel Data Model, you can create relationships between columns of related information in separate tables. Using this new functionality, we can bring in the related Department/Employee information by adding the related Dept Code and Employee Type tables to our Data Model. This is a very simplistic usage of the Data Model, but there are many additional ways you can utilize it to your advantage (especially when incorporating PowerPivot/PowerView).

How to Create an Excel Data Model

Now I’ll walk you through the process of taking our Employee Travel Expenses and creating a data model with the relevant ranges.

First, you must convert your data ranges to tables. To do this, simply highlight the range you want to convert, go to the Insert tab, and click on Table:

Once you’ve converted all three ranges to Tables, you can change the name of each Table so that you can easily identify them by going to the Design tab once you have a table selected (I’ve named them: ExpenseTable, DeptTable, EmployeeTable):

Now we will add all three of our tables to the Data Model by going to Connections under the Data tab, and clicking Add to the Data Model:

Making a PivotTable using your Data Model

After adding these tables, you will now see ThisWorkbookDataModel, which can be now be used to create our PivotTable! Go to the Insert tab, and choose PivotTable. Then choose the option ‘Use an external data source’ which allows you to choose our Data Model as the connection:

Once you’ve selected the Data Model as your data source for the PivotTable, you should be able to see all of your tables in the field list. Also, notice the sleek new look of PivotTables in Excel 2013, including that little box with the gear logo in the top right which allows for many different views/options to be changed quickly and easily:

Now, we can start adding fields from each table to one of the PivotTable sections below. When we add information from more than one table, Excel will prompt us to create a relationship. If I want to see the Employee Expenses by Employee Level, I will need to create a relationship between the Employee Type found in both of the tables.

And once you click OK, you’re ready to rock! As you can see above, I’ve already added the Employee Level for the Columns, with the Employee Name in the Rows, and their Expense Amount in the Values section. Therefore, I should get a nice PivotTable that shows me the breakdown of expense spending across Employee Levels (with a little bit of additional changes to make it look prettier):

Feel free to play around and see which other ways you can slice and dice the data once you add in the Dept Code relationship. Of course, this is only a simple example of how you can use the Data Model. It becomes much more powerful if you have multiple tables with lots of data, and can even include connections to other workbooks or data sources (like Access for example). Once the setup of the Data Model and relationships are complete, you can slice and dice your data very quickly and easily without having to use complicated in-between formulas. Hope you’ve enjoyed the lesson!

About the author

Ben Currier has been working in Financial Planning and Analysis over the past 10 years. He also teaches an on-going free online Excel course at Excel Exposure with video tutorials and lessons, which aims to help people to improve their Excel skills.  He is excited about the emerging trends in online education and loves the thought of quality, free, and accessible educational information available for anyone who wants to improve their knowledge and abilities.

About MVP Monday

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.