When you want to create a PivotTable, what do you do if you have data in different worksheets? If you’re using Excel 2013, you have a streamlined method of doing this. There’s a technique called the Data Model, and it uses data relationships the way a database does. In this tutorial, I'll show you everything you need to make a PivotTable in Excel 2013 from data in multiple sheets, using the Data Model.
Screencast
If you want to follow along with this tutorial using your own Excel file, you can do so. Or if you prefer, download the zip file included for this tutorial, which contains a sample workbook called Pivot Consolidate.xlsx.
Examining the Data
This workbook has three worksheets: Customer Info, Order Info, and Payment Info. Click the Customer Info sheet, and see that it contains order numbers and the name and state of customers.
Customer Info sheet Click the Order Infosheet, and see that it also contains order numbers, as well as fields for the month, the products ordered, and whether or not the products are organic.
Order Info sheet Click the Payment Info sheet, and see that it contains order numbers, the dollar amount of each sale, the payment method, and whether the order was placed by a new or an existing customer.
Payment Info sheet By connecting all these sheets inside the PivotTable’s task pane, we can selected data from each of the sheets. Since the order numbers exist in all three sheets, they will become the connection points. This is what a database calls a primary key. Note that it isn’t always necessary to have a primary key, but it reduces the chance of error.
Create Named Tables
Before creating the PivotTable, let’s create a table from each of the sheets. Click back on the Customer Table, then click anywhere inside the data area. Go to the Insert tab of the ribbon bar, then click the Table icon.
Convert data on a worksheet by selecting Insert > Table The Create Table dialog box correctly identifies the area of the table. The checkbox on bottom should also identify that the first row of the table is for headers. (If not, select that option.)
The Create Table dialog should correctly guess the data area Click OK, and now you have a table with striped shading and filter buttons. You can click inside it to deselect, if you want a better look at it (just don’t click outside the table). The ribbon bar also displays a Designtab for the table. On the left side of the ribbon, the Table Name box shows a temporary name of Table1. Delete that, and call it Customer_Info (use an underscore instead of a space). Then press Enter.
Apply a name to each table Repeat this process on the Order Info and Payment Info sheets. Call the tables Order_Infoand Payment_Info, respectively. Now we’re ready to insert the PivotTable.
Inserting the PivotTable
On the Payment Info sheet, make sure the cursor is somewhere in the table. Go back to the Inserttab of the ribbon, and click the PivotTableicon (it’s the very first icon).
With the cursor inside one of the tables, select Insert > PivotTable The dialog box that appears should correctly identify the table and select that the PivotTable will go on a new worksheet. On bottom, click the check box for Add this data to the Data Model. Then click OK.
Adding the data to the Data Model is what allow the connections to work You’ll now have a PivotTable on a new worksheet, there will be a task pane on the right side of the screen, and the ribbon bar will display the Analyze tab. The task pane shows the table and fields of just the active sheet, so click ALL to see all the tables that you created. But before we can use them, we have to connect them to each other, and that means creating relationships. Click the Relationships button on the ribbon bar.
Setting Up Table Relationships
Clicking that button displays the Manage Relationships dialog. Click the New button, and that displays the Create Relationship dialog. We’ll create two relationships using the Order # field as the connector. From the drop-down lists, choose Payment_Info for the table, and next to it, pick Order # from the Column drop-down. On the second row, choose Customer_Infofrom the Related Table drop-down list, and next to it, pick Order # from the Related Column drop-down list.
There are three tables, so create two relationships This means the Payment_Info and Customer_Info tables are related where they have matching order numbers. Click OK, and we see that relationship listed in the Manage Relationships box. Repeat this process to create a relationship that joins Payment_Info to Order_Info, also using the Order # field. The Manage Relationships box should now look like this:
The Manage Relationships dialog will display the relationships that you create Note that it isn’t necessary to create a relationship between the Order_Info and Customer_Info tables, since they’re joined automatically through the Payment_Info table. Click the Closebutton at the bottom of the box. Now we can finally drag fields into the PivotTable.
Inserting Fields Into the PivotTable
In the ALL section of the task pane, click the small arrows to twirl open the three tables, so you see their fields. Drag fields into the PivotTable areas as follows:
Stateand Month into rows
Productinto columns
$ Saleinto values
Statusinto filters
Drag fields from each of the three tables into the PivotTable You can now use and modify this like any other PivotTable.
Conclusion
Using the new Object Data Model feature in Excel 2013, you can cherry-pick fields from multiple worksheets to create a unified PivotTable. Keep in mind that the rows of each table need to be related to each other in some way. You’ll have the best chance of success when the tables have a common field with unique values.
0 Comments