Pivot Tables Based on Multiple Ranges

Welcome to another series of my best Microsoft Excel tips and tricks! I am sure that most of you use Pivot Tables regularly – in the accounting world, Pivot Tables can help organize data for everyday tasks immensely. Most of the time, you probably put your cursor in the data and Excel miraculously recognizes the source data for the Pivot Table.

In this article, I will show you how to create Pivot Tables based on multiple data ranges. For example, sales data for each month of the year may be located in individual worksheets, and you may want to create a Pivot Table combining sales data from all months. In our example, the data sources will reside in different worksheets, but our data sources could easily be in different workbooks.

The workbook Beckerdecember2020.xlsx contains quarterly sales of Planes, Trains, and Cars in Asia, Europe and the USA. We want to create a Pivot Table that summarizes the sales data from all quarters.

To begin, use the keystroke combination ALT+D, release D and press P while still holding down ALT to bring up the PivotTable and PivotChart Wizard dialog box shown in Figure 1.

Figure 1: PivotTable and PivotChart Wizard Dialog Box

After checking “Multiple consolidation range,” “Pivot Table,” and selecting Next, check “Create a Single Page Field” on the Step 2a Dialog Box, and the Step 2b Dialog Box shown in Figure 2 appears. We first select the range D4:G7 in worksheet Q1 and select Add – you can do this by manually typing or by selecting within the Excel sheet! Click Next, and select the range D4:G7 in worksheet Q2. Continue in this fashion until the data in all four worksheets has been selected (see Figure 3.)

Figure 2: In Step 2b, we can add Multiple Ranges as the source data for a Pivot Table

Figure 3: All Source Data for PivotTable has been selected

After clicking Finish you will see in the worksheet Q1 the Pivot Table shown in Figure 4.

Figure 4: Pivot Table Summary of Sales

We find, for example, that 280 Cars were sold in Asia and 136 Trains were sold in the USA.

Inserting Slicers

Slicers enable the user to easily slice and dice their data in many ways.  With your cursor inside the Pivot Table, navigate to the Insert tab. Under the Filter Group, choose Slicer. After selecting Row, Column and Page 1, you will see the Slicers shown in Figure 5.

Figure 5: Pivot Table Slicers

Using the Shift and Control keys, you may choose any subset of product, locations and worksheets (Item 1 = Q1, Item 2 = Q2, etc.) to govern the calculations in the Pivot Table. Hold down Shift to select multiple items within one slicer. For example, selecting Cars and Planes, Asia and Items 1 and 2 yields total sales of Cars and Planes in Asia during Q1 and Q2 (see Figure 5). We can see that during Q1 and Q2, a total of 140 Cars were sold in Asia.  Selecting the Funnel icon in the top right of any Slicer clears the Filter for that slicer.

Figure 6: Sales of Cars and Planes in Asia during Q1 and Q2

I want to wish you all a great Holiday season! We will begin 2021 with two articles that show you how to utilize Excel’s great financial functions.