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 of 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 the F3 cell in the Sheet4 worksheet. Then choose INSERT ⇒ Tables ⇒ PivotTable. Create PivotTable dialog box will appear on 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 to 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 a data source from another worksheet.

Existing Connections dialog box has 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 the 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 the 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

Kawser

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 them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo