Get a handle on Relationships with Excel 2013’s Data Model feature and create PivotTable reports with ease.
I use VLOOKUP to combine data from multiple tables so that I can create a PivotTable report. Is there an easier way?
In Excel 2007 and earlier versions, no, that’s as good as it gets. In Excel 2010 you can download the PowerPivot Add-in free from Microsoft and use PowerPivot to create PivotTable reports based on multiple tables.
I won’t cover PowerPivot in this article as it is a huge topic. PowerPivot is extremely powerful and if you use data frequently I highly recommend you investigate it further. PowerPivot is also available with some versions of Excel 2013.
Excel 2013 also has a new Relationships option that reduces the need to use VLOOKUP to combine data. Excel 2013’s Data Model allows you to create relationships between tables and lets you report on multiple tables with a PivotTable. Previously, PivotTables could only report on a single table.
What is a Relationship?
A relationship exists when two requirements are met:
- There must be a common column within two or more data tables. It is not necessary for the fields (columns) to have the same name.
- In one of the tables, the shared column has to contain only unique entries.
Relational databases (for example, SQL and Access) are built on these types of relationships and can contain hundreds of tables. The Data Model feature lets you create a basic relational database structure within Excel.
As an example, consider an Order data table and a Sales data table. Both might contain a State column, but in both tables those fields will contain duplicates, so you can’t use the State field to create a relationship.
The Order table, however, will have a unique Order ID field. If the Sales data table also includes that Order ID column then a relationship could be established between the two tables. This means you could create a PivotTable report based on data from both tables.
Related: Use this spreadsheet to practice these techniques
Why create a Relationship?
In many cases, a single data table doesn’t have all the information you need to create your report. Relationships provide a way to extract data from multiple tables to complete your report.
In the Sales/Order example, if you wanted to create a report based on the person who raised the order it is unlikely to be in the Sales data table. Using the Order ID to create a relationship between the Sales and Order tables lets you extract the name from the Order table and use it in your Sales report.
Note: In Excel, columns equate to fields in a database. PivotTables use the term “field” for a column.
Setting up a Relationship
I will use a simple example with a Sales data table that includes a column heading Sales_State. We want to report on regional sales where each state or territory is allocated to a region. However the Sales data table doesn’t include a region field. We have a separate table which lists the states and territories and their respective regions – see Figure 1.
I have used the Format as Table option from the Home ribbon on the Sales and States tables. They have been named tblSales and tblStates respectively. I highly recommend using the Format as Table feature as it tells Excel to treat the table as a database table. The companion video includes more details about Format As Table.
In the Data ribbon tab, in the Data Tools section, is the Relationships icon, see Figure 2.
This opens the Manage Relationships dialog, see Figure 3.
Click the New button, which opens the Create Relationships dialog, as shown in Figure 4. The bottom dialog in Figure 4 has the selections necessary to create the relationship between the two tables.
The dropdowns on the left allow you to select a table name. The dropdowns on the right list all the column names in the table selected. You need to select the shared column in both tables. This example shows that the names of the columns can be different. One of the columns must contain unique entries, if not, an error message will be displayed.
This is a simple example and in practice you may need to create multiple relationships between multiple tables to get to the data you need. Relationships between tables can be complex.
Once created, the relationship will be listed in the Manage Relationship dialog, see Figure 5.
Now you can create a PivotTable report based on the two tables.
Creating a PivotTable report
To create a PivotTable, click the Insert Ribbon and select the PivotTable icon on the far left. Click the Use an External Data Source
option, then click the Choose Connection button. In the Existing Connections dialog, click the Tables tab and then the Tables in Workbook Data Model option, then click the Open button and OK – see Figure 6.
The PivotTable Fields list has both tables listed – see Figure 7. Clicking the icon to the left of the table name will display all the fields (columns) from the table.
Figure 8 shows the final report using fields from both tables.
Another advantage of using the Data Model is that you can convert the PivotTable into a formula-based report. This means you can then
then make structural and formatting changes to the report. PivotTables only allow basic changes to the report. The companion video on intheblack.com demonstrates this.
Note: A similar regional sales report could also have been created using the PivotTable Grouping feature, without the need to use a separate table. This type of Grouping is a manual process. Using a table allows you to modify the Grouping by changing the table and refreshing the PivotTable.
You’ll find a companion video with more about the Format As Table feature and tips on changing a PivotTable to a formula-based report on intheblack.com.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to various organisations. Questions can be sent to firstname.lastname@example.org
Want more Excel? We've gathered nine handy demonstrations together in the Slideshare below.