On the Payment Info sheet, make sure the cursor is somewhere in the table. Go back to the Insert tab of the ribbon, and click the PivotTable icon (it’s the very first icon). With the cursor inside one of the tables, select Insert PivotTable. Sep 18, 2012 Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it. And you could also use Paste Append to directly paste into PowerPivot.
You’ve probably come across data that looks something like this before. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it.
In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount. The year should feature as a piece of data too in its own column. Salesperson and product should also be in their own column.
If this data was in a proper tabular format, it would be easy to summarize it in a similar manner with pivot tables and pivot charts. As is is now, it’s really hard to do anything with it.
This type of data can be a pain to convert to a proper data set. I’ve already shown you how to convert this type of data into a proper data set by using the unpivot feature found in power query.
In this post, I’m going to show you how to combine these tables and unpivot them using the pivot table wizard. Essentially we will use a pivot table to unpivot the data.
I very briefly touched on the PivotTable And PivotChart Wizard in my 101 pivot table tips post where I mentioned the keyboard shortcut Alt + D + P to open the wizard.
This is a keyboard shortcut from an older version of Excel and unless you know about it, there’s no place to find it in the ribbon.
You can also access this by adding a command to the quick access toolbar for the wizard. It can be found under the Commands Not in the Ribbon section and it’s labeled PivotTable and PivotChart Wizard.
Let’s get started and combine the data. Open the PivotTable and PivotChart Wizard using the Alt + D + P keyboard shortcut, then choose Multiple consolidation ranges then press the Next button.
In the next step of the wizard, choose the Create a single page field for me then press the Next button.
Now select the ranges you want to consolidate.
Select the location for your new pivot table, either a new worksheet or somewhere in an existing worksheet. Then press the Finish button. You will now have a blank pivot table.
With the new blank pivot table, we need to set it up to build out a new tabular set of data. Move the Page1, Row and Column field into the Rows area of the pivot table. Bring the Value field into the Values area of the pivot table.
Now we will need to change the format of the pivot table to create our tabular data set. Select the pivot table and go to the Design tab then select the Report Layout command and choose the Show in Tabular Form option from the menu.
We also want to turn off any subtotals the report may have. Go to the Design tab then select the Subtotals command and choose the Do Not Show Subtotals option from the menu.
Next we want to repeat all labels just like we’d have in a normal data set. Go to the Design tab then select the Report Layout command and choose the Repeat All Label Items option from the menu.
Now we just need to change a couple of the column headings by typing over them.
Now we have a proper data set which has combined all the data from our 4 ranges.
Even if your data is in a proper unpivoted tabular format, it might be broken up into multiple ranges across different sheets. Maybe your sales data for each salesperson is on a different sheet or each month of data is in its own sheet.
We can use the pivot table wizard to combine the data into one pivot table. It’s the same process, just combining data from different sheets!
Excel has a lot of functionality, and if something seems like a pain and a lot of effort to do manually there is usually a better way.
Power Query can easily consolidate and unpivot data. But not everyone will be using the most recent version of Excel which has power query out of the box or is able to install the add-in because they don’t have the admin rights from IT.
It’s good to know there are older methods, even if they’re a bit hidden.
Pivot tables have long been a powerful tool for summarizing data and more, but most of us are accustomed to using them with data from one worksheet. Those running Excel on Windows computers, however, can create a pivot table with data from multiple worksheets as long as the worksheets have one field in common.
The ability to link data from two worksheets debuted as an add-in in Excel 2010. It was built into Excel 2013, but the relationship-building tools that help make it easy to do first arrived in Excel 2016.
Say that you have a large invoice register on Sheet1 with fields like “Product ID” and “Customer Number.” If the data on Sheet2 is a product database and the data on Sheet3 is a customer list, then you can easily build a pivot table from data from all three worksheets without doing a bunch of VLOOKUP formulas to get the data back onto Sheet1.
The Format as Table icon on the Home tab (or Ctrl+T) sounds like it’s made for quickly formatting a worksheet. But those words, “Format as Table,” undersell how much happens when you make a worksheet into a table. Behind the scenes, it will make a data set eligible for use in the Relationships dialog.
On each of the three worksheets, select the individual data set and press Ctrl+T. Excel will ask you to verify that your data has a header row. Click OK to create the table. By default, these three tables will be called Table1, Table2, and Table3. You can easily change the name of each table before you build the relationships: Select a cell in the table. A Table Design tab appears in the ribbon, and the Table Name can be edited in a box on the left side of the ribbon. For this example, call the three data sets “Data,” “Products,” and “Customers.”
If you’re using Excel 2016 or newer, you’ll see a Relationships icon in the Data Tools section of the Data tab of the ribbon.
Click the Relationships icon to open the Manage Relationships dialog. On the right side of the Manage Relationships dialog, click New… to create the first relationship.
In the Create Relationship dialog, specify the Data table has a column called ProdID. It’s related to the Products table using the column called Product.
Click New… again and define a second relationship. This one should specify that the Data table has a CustID column that’s related to the Account Number column in the Customers table.
After creating both relationships, they’ll be listed in the Manage Relationships dialog. Click Close to close this dialog.
This next step is counterintuitive because most people start a pivot table by selecting the data that they want to appear in the pivot table. But for our purposes, you need to insert a blank worksheet in your workbook or simply start from a blank cell on Sheet1 and go to Insert, PivotTable.
When you start from a blank cell after defining relationships, the Create PivotTable dialog will default to “Use This Workbook’s Data Model.” This sentence always seems cryptic to me. What’s a data model? The answer is that by creating relationships, you unknowingly created a data model that lives in the workbook. The data model contains pointers to the three tables and defines the relationships between those tables.
The first thing you’ll notice in the PivotTable Fields pane is a list of table names instead of a list of field names. Each table has a greater than sign (>) to the left of the table name. Click that icon to reveal the fields available in the table.
The power of the data model happens here. You can choose Quantity from the Data table, Region from the Customer table, and Vendor from the Products table. Microsoft will join the data from the three tables much like a query in Access or SQL Server. You don’t have the overhead of thousands of VLOOKUPs.
In the pivot table shown in Figure 2, the vendor names in column A come from the Product table on Sheet2. The Regions shown in row 2 are from the Customers table on Sheet3. The quantities reported in cells B3:E8 are from the invoice register on Sheet1.
The Data Model was brand new in Excel 2013, and there was no obvious way to create a relationship before you built the pivot table. In Excel 2013, you would convert all three sheets to tables. From the table on Sheet1, choose Insert, Pivot Table and choose the box for “Add This Data to the Data Model.” In the PivotTable Fields pane, change from Active to All to reveal all three tables.
As soon as you select fields from more than one table, a yellow warning box appears in the PivotTable Fields pane with a button to Create Relationships. The process feels backwards compared to the easier workflow introduced in Excel 2016, but if you’re still stuck using Excel 2013, it will work.
There have always been two types of pivot tables. A normal pivot table based on data from a single worksheet is a Pivot Cache pivot table. A pivot table created from external data is treated as an OLAP pivot table, and a number of pivot-table features only work with OLAP pivot tables.
When you create a relationship between tables, Excel sees your data as being an external data set. This enables features such as Include Filtered Items in Totals and Distinct Count or the ability to convert the pivot table to Cube Formulas, create subsets of rows or columns, and define new calculations with the DAX formula language.
Joining worksheets in the Data Model brings the relational power of Access or SQL Server to Excel.