If you’re importing foreign data or sharing a workbook, the data often ends up in several sheets. As a result, turning data into meaningful information can be difficult. That’s where Excel 2013’s new data modeling capabilities can help even casual users. By building a relationship between sheets, Excel 2013 makes summarizing data spread across multiple sheets easy.
Putting this new feature into practice is easy, but it works mostly behind the scenes. As a result, it can be difficult to get a handle on how to implement it. In the next 10 steps, we’ll define a reporting need and meet it using Excel 2013’s new data model.
This article is for users unfamiliar with the feature and trainers supporting Excel users. This feature isn’t for serious database developers, and please don’t blast me for Microsoft’s unfortunate choice for naming this new feature.
1. Defining the need
Figure A shows two sheets with related data. As you can see, each record in Daily Totals contains a value that’s further described by date, personnel, and city. The records in Sites list the cities in each region. Now, suppose you want to total the values in Daily Totals by region.
These two sheets contain records that are related.
Working through your data won’t always be this easy. I’ve purposely kept this example simple to keep the focus on the steps.
2. Convert data to tables
To use Excel’s data model, convert the data into tables. When working with your own data, make sure your data set has a row of headers. Our data sets already have a row of headers. To convert data to a table, do the following:
- Click anywhere inside the data set and click the Insert tab.
- Click Table in the Tables group.
- In the resulting dialog box, shown in Figure B, click OK. In this case, the header option is already checked. When applying this technique to your own data, you’ll want to check or uncheck this option, appropriately.
Convert your data to a table.
Use the above steps to convert both data sets. Naming the new tables isn’t necessary, but it will make working with them easier. To that end, click inside a table and click the contextual Design tab. Then, enter a meaningful name in the Table Name field, as shown in Figure C. Name them DailyTotalsTable and SitesTable.
Named tables are easier to work with.
3. Finding the related data
There’s no regional information in the data set that contains the values you want to summarize. With data modeling, that’s not a problem. All you need is a relationship between the table with the values you want to summarize and the regional data you’ll use to summarize those values. A relationship is a connection between two tables based on a single column in both. In other words, when two data sets share a similar column of data, they are related by that common column. In the case of our example, the City column relates the two data sets.
4. Create the relationship
Creating a relationship between two data sets is new to Excel, but don’t let that worry you—it’s easy. To create a relationship between the two tables do the following:
- Click the Data tab.
- Click Relationships in the Data Tools group. (If this option is dimmed, return to #2 and create the tables.)
- From the first Table dropdown, choose DailyTotalsTable.
- In the Column (Foreign) dropdown, choose City.
- In the Related Table dropdown, choose SitesTable.
- In the Related Column (Primary) dropdown, choose City, as shown in Figure D.
- Click OK.
- Click Close to return to the sheet.
Specify the column that both columns share to create a relationship between the two tables.
5. Generate a blank PivotTable
To summarize the values, we’ll generate a PivotTable. Click inside DailyTotalsTable and click the Insert tab. In the Tables group, click PivotTable. When Excel displays the dialog shown in Figure E, click OK.
Generate a blank PivotTable in a new worksheet.
You can use the new Recommended PivotTables option, but it won’t consider your second table, so it’s easier to start with a blank table.
6. Add the second table
Currently, the PivotTable frame evaluates only one table, DailyTotalsTable. Add SitesTable as follows:
- Click the MORE TABLES link shown in Figure F.
- In the resulting dialog, click Yes. Doing so engages the new data modeling feature.
As you can see in Figure G, both tables are now part of your PivotTable structure.
Clicking MORE TABLES will engage the data modeling feature.
Both tables are now available to you.
7. Add fields
It’s time to start adding fields to the PivotTable. First, click the expand arrow to the left of DailyTotalsTable to see its fields. Check Value and City. Then, use the scroll bar to access SitesTable. Expand its fields and select Region. Doing so adds these fields to the PivotTable frame, as shown in Figure H.
Thanks to the data modeling feature, you can add fields from both tables to the PivotTable frame.
8. Add regions to summarize
At this point, the PivotTable probably won’t be perfect, so it’s time to start tweaking a bit. Figure I shows the result of dragging the Region field to the COLUMNS section.
Adding Regions summarizes the values accordingly.
9. A quick switch
If you don’t like that layout, you can quickly switch the column and row headings. Simply drag the City field to the COLUMNS section and the Region field to the ROWS section, as shown in Figure J.
Switching the rows and columns is easy.
If you’re paying close attention, you might have noticed the (blank) row. Can you guess where that came from? By reviewing the city headings for those items, you can quickly troubleshoot the problem. Using Figure A, you can determine that there’s no region value for Boston and Cleveland. Fortunately, it’s a quick fix. Add the values to SitesTable, as shown in Figure K.
Add the missing regions for Boston and Cleveland.
After adding the new regional records, refresh the PivotTable. To do so, click inside the table and then click the contextual Analyze tab. In the Data group, click Refresh. Figure L shows the refreshed PivotTable.
After refreshing the table, it displays the regional values correctly.
Did you notice the little magnifying glass icon in Figure L? Clicking that will let you drill deeper into the details that aren’t currently visible. Clicking this icon with the Cleveland value selected, displays the dialog shown in Figure M. Figure N shows the results of drilling to the City, the cities in the Central region.