Sometimes it may be useful to create several pivot tables in a worksheet connecting data source in another worksheet in the same workbook. Say you have created a pivot table in a new worksheet like the following figure.
We are going to create another 3 pivot tables in the same worksheet (Sheet4). Here is the process how we can do it:
Table of Contents
Download this sample file to follow us in this article.
Select a cell in the worksheet where you want to place the pivot table. The cell must not be in the zone where the previous pivot table exists. We have selected F3 cell in Sheet4 worksheet. Then choose INSERT ⇒ Tables ⇒ PivotTable. Create PivotTable dialog box will appear in the screen.
If you observe the Create PivotTable dialog box closely, you will find three options: Choose the data that you want to analyze, Choose where you want the PivotTable report to be placed, and Choose whether you want to analyze multiple tables.
As our Data is in another worksheet, we have to choose Use an external data source radio button under Choose the data that you want to analyze to select the data source. Existing Connections dialog box will appear.
Read More: Creating an Excel Pivot Table Manually
In Existing Connections dialog box, there are two tabs, Connections and Tables. Select Tables and then choose Table1 and then click Open.
Existing Connections dialog box have disappeared when you have clicked OPEN. Create PivotTable dialog box will appear again with Connection name: Table1. You will find that in Choose where you want the PivotTable report to be placed section, under Existing Worksheet, Location: shows Sheet4!$F$3 value. You can change Location value if you want to place your pivot table in another cell.
Click OK and a new pivot table skeleton will be placed in your worksheet like the following figure.
Now place Location field in Rows area, Sex field in Columns area, and again Location field in Values area. Deselect Field Headers command choosing PIVOTTABLE TOOLS ⇒ ANALYZE ⇒ Show ⇒ Field Headers. Finally you will find the following pivot table.