Create Two Pivot Tables on Excel Worksheet

In a comment on this blog, someone asked how to create two pivot tables on the same Excel worksheet.

NOTE: See the updated version of this Two Pivot Tables article, from July 2020.

Shown below is a worksheet named Pivot_Reports, with a pivot table on it, based on the data on the Sales_East sheet.

TwoPT01

We’ll add another pivot table to the Pivot_Reports sheet, based on data on the Sales_North sheet.

Add the Second Pivot Table

  • Select the Sales_North sheet, and select a cell in the data table.
  • On the Ribbon, click the Insert tab
  • In the Tables group, click PivotTable (click the top half of the PivotTable command).

RibbonInsertPT

  • In the Create PivotTable dialog box, at the top, leave the default selection of Select a Table or Range, where the Sales_North table shows.
  • In the lower section, click Existing Worksheet.
  • Click in the Location box, then click on the sheet tab for the Pivot_Reports sheet.
  • Click on the cell where the second pivot table should start.

TwoPT02

  • Click OK to create the new pivot table.
  • Add the fields that you’d like in the new pivot table.

The second pivot table is added to the Pivot_Reports worksheet.

TwoPT03

Prevent Pivot Table Overlap

When you have two or more pivot tables on the same worksheet, be careful to prevent them from overlapping.

PTOverlap

Before you add new fields to the pivot table on the left, you might have to add blank columns between the pivot tables. Or, if one pivot table is above the other, add blank rows between them.

If the pivot tables will change frequently, adding and removing fields, it may be better to keep the pivot tables on separate sheet.

This short video shows pivot table refresh problems, and how to avoid them. For detailed written notes, go to the Pivot Table Errors page on my Contextures site.

Related Articles

Create a Pivot Table In Excel

Create Two Pivot Tables On Excel Worksheet

____________

6 thoughts on “Create Two Pivot Tables on Excel Worksheet”

  1. Can someone advise if it’s possible to be able to insert some data manually in a pivot table that can then be included in the calculations using the calculated field option? I have some data that is not drawn from our finance system that I need to add manually and want to keep it neat with as little manual manipulation as possible.

    Thanks

  2. Hope you’ve gotten an answer by now, but anyway, if you are using Excel 2007, on the Pivot Table Tools, Options ribbon (when your cursor is in the table), in the Tools section of the ribbon, select Formulas and you can create any formula you want to add as a field and put it anywhere in the table you want. Really slick!

  3. @Paul, you can create a calculated field, but only with data from the pivot table source data, or amounts that you type into the formula, e.g. Bonus= SalesAmt* 0.05

    On another sheet, could you use the GetPivotData function to pull results from the pivot table, and combine those values with manual entries?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.