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.

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

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:

Master Excel Pivot Table: Top Pivot Table Courses Online

Download this sample file to follow us in this article.

Employee-list.xlsx

Master Excel Conditional Formatting in Just 1.5 Hours (Free Course)!

Learn Excel Conditional Formatting with 7 Practical Problems!

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.

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

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.

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

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.

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

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.

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

Finally we got this pivot table.

Download this sample file to follow us in this article.

Employee-list.xlsx


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply