Creating a pivot table in a worksheet connecting to another worksheet in the same workbook

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.

Pivot Table Created in another worksheet. Data source in Sheet1.

We are going to create another 3 pivot tables in the same worksheet (Sheet4). Here is the process how we can do it:

Download this sample file to follow us in this article.

Employee-list.xlsx

Step 1:

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.

Step 2:

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

Step 3:

In Existing Connections dialog box, there are two tabs, Connections and Tables. Select Tables and then choose Table1 and then click Open.

Follow these steps to choose data source from another worksheet.

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.

Connected to Table1 data source.

Step 4:

Click OK and a new pivot table skeleton will be placed in your worksheet like the following figure.

This skeleton will be placed in your worksheet.

Step 5:

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.

Finally we got this pivot table.

Download this sample file to follow us in this article.

Employee-list.xlsx