Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the time you'll create a PivotTable from a data list or Excel table. But you can also summarize multiple data ranges using a PivotTable, if the data is formatted appropriately. To consolidate a series of data ranges into a PivotTable the ranges must be laid out in cross tabular format and have exactly the same structures. In a sample file here, I have three worksheets: SupportCalls, OrderCalls, and ReturnCalls. And the data on the SupportCalls worksheet has a series of distribution regions, North, South, East and West, along the Row Label area and then the Years 2007, 2008, 2009 and 2010 along the top, providing the values for the Column Labels.
And then here in cell B3 you'll see that I have Region-Year. Now, normally if you were to create a cross-tabular table like this, you would put Region in cell B3 and Year in cell C2. The reason you don't do that in this case is because this region that contains the data and all of the labels must be perfectly rectangular. In other words, there must be data in each one of those cells. So if I were to have Region in cell B3 and Year in cell C2, then the cell would be blank as would cells D2 through F2 and the data layout would not be perfectly rectangular, so the technique I am about to show you would not work.
And the data I have on my other worksheet, OrderCalls, has the same layout and ReturnCalls has the same layout as well. So now, I'll switch back to the first worksheet, SupportCalls, and continue on. If you want to create a PivotTable using this data, you need to do something a little bit different than you would when you create a PivotTable from a data list or an Excel table. To run the PivotTable and PivotChart Wizard, you need to hold down the Alt key, press D, and then press P.
When you do, you launch the PivotTable and PivotChart Wizard. On the first page of the wizard, select the Multiple consolidation ranges option and then click Next. Then on the second page select the I will create the page fields option and click Next. And now on the next page you can select the worksheet ranges that you want to consolidate. Just start with the cursor flashing in the Range box, click the collapse dialog box button, and then on the first worksheet select the cells that contain the data.
So we have B3 through F7. Click the expand dialog box button and then click Add and now we do the same for the other worksheets. So click the collapse dialog box button again, change to the OrderCalls worksheet by clicking the sheet tab, and then select B3 through F7. Expand dialog. Click Add to add that range to the list of ranges and then finally click the collapse dialog box button again, click ReturnCalls, select that data, expand the dialog, and click Add.
In this case, we don't want any page fields, which are also called report filter fields. So we'll leave that option selected to 0 and then click Next. On the final page of the wizard, verify that the New worksheet option is selected and click Finish. When we do, the data appears in the new PivotTable. Now notice over here in the PivotTable Field List, that the fields have the names Row, Column and Value. Value is okay, but I'd like to change the other two.
To do that I click any cell in the PivotTable with a value for the field that I want. In this case, we have North and that is part of the row group. If I want to change the name of the active field, then on the Options contextual tab in the Active Field group select the name in the Active Field box and change the name. In this case, I'll change it to Region and press Enter, and you'll notice that the name of the field changed over in the PivotTable Field List.
And now I can do the same for year. I'll click any cell that contains a year of Value and then on the Options tab in the Active Field box, I can type Year, press Enter, and the change takes place. You probably won't create many PivotTables by consolidating multiple data ranges. But it's nice to know the capability is available if you're working with legacy workbooks that have their data laid out in cross tabular formats.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64867 Viewers
80 Video lessons · 124403 Viewers
52 Video lessons · 60327 Viewers
59 Video lessons · 46153 Viewers