5 Ways To Make Multiple Pivot Tables In Excel

Pivot tables are a powerful tool in Microsoft Excel that allow users to summarize and analyze large datasets. However, when working with multiple pivot tables, it can be challenging to manage and maintain them. In this article, we will explore five ways to make multiple pivot tables in Excel, making it easier to work with complex data.

What are Pivot Tables?

Before we dive into the five ways to make multiple pivot tables, let's briefly discuss what pivot tables are. A pivot table is a data summarization tool that allows users to rotate and aggregate data from a spreadsheet. It provides a flexible way to analyze and present data, making it a popular choice among Excel users.

Method 1: Using Multiple Pivot Table Fields

One way to make multiple pivot tables is by using multiple pivot table fields. This method involves creating a single pivot table and then adding multiple fields to it. To do this, follow these steps:

Select the cell range that contains the data you want to analyze.
Go to the "Insert" tab and click on "PivotTable."
Create a new pivot table and add the fields you want to analyze.
To add multiple fields, drag the field names to the "Row Labels" or "Column Labels" areas.
Right-click on the pivot table and select "PivotTable Options."
In the "PivotTable Options" dialog box, select the " Totals & Filters" tab.
Check the box next to "Use Custom Calculations when summary functions are applied to the data range."

Benefits of Using Multiple Pivot Table Fields

Using multiple pivot table fields is a great way to create multiple pivot tables in Excel. This method allows you to:

Analyze multiple fields in a single pivot table.
Create custom calculations using the "Totals & Filters" tab.
Easily switch between different fields using the "PivotTable Fields" pane.

Method 2: Creating Multiple Pivot Tables from a Single Data Source

Multiple Pivot Tables from Single Data Source

Another way to make multiple pivot tables is by creating multiple pivot tables from a single data source. This method involves creating a single pivot table and then duplicating it to create additional pivot tables. To do this, follow these steps:

Select the cell range that contains the data you want to analyze.
Go to the "Insert" tab and click on "PivotTable."
Create a new pivot table and add the fields you want to analyze.
To create additional pivot tables, go to the "PivotTable Tools" tab and click on "Options."
In the "PivotTable Options" dialog box, select the "Actions" tab.
Click on "Create" and select "PivotTable" from the dropdown menu.
Select the cell range where you want to create the new pivot table.

Benefits of Creating Multiple Pivot Tables from a Single Data Source

Creating multiple pivot tables from a single data source is a great way to analyze different fields in a single dataset. This method allows you to:

Create multiple pivot tables from a single data source.
Easily switch between different pivot tables using the "PivotTable Fields" pane.
Analyze different fields in a single dataset.

Method 3: Using the Power Pivot Add-in

The Power Pivot add-in is a powerful tool that allows users to create and manage multiple pivot tables in Excel. To use the Power Pivot add-in, follow these steps:

Go to the "File" tab and select "Options."
In the "Excel Options" dialog box, select the "Add-ins" tab.
Check the box next to "Power Pivot" and click "OK."
Go to the "Power Pivot" tab and click on "Create."
Select the cell range that contains the data you want to analyze.
Create a new pivot table and add the fields you want to analyze.

Benefits of Using the Power Pivot Add-in

Using the Power Pivot add-in is a great way to create and manage multiple pivot tables in Excel. This method allows you to:

Create and manage multiple pivot tables in a single workbook.
Easily switch between different pivot tables using the "Power Pivot" tab.
Analyze large datasets using the Power Pivot add-in.

Method 4: Creating Multiple Pivot Tables using VBA Macros

VBA macros are a powerful tool that allows users to automate tasks in Excel. To create multiple pivot tables using VBA macros, follow these steps:

Go to the "Developer" tab and click on "Visual Basic."
In the "Visual Basic Editor" window, select "Insert" and then "Module."
Create a new module and paste the following code:

Sub CreateMultiplePivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim i As Integer
    
    Set ws = ActiveSheet
    For i = 1 To 5
        Set pt = ws.PivotTables.Add(cellrange,  _
        "PivotTable" & i,  _
        "PivotTable" & i)
        pt.AddFields "Field1", "Field2"
    Next i
End Sub

Replace the cell range with the cell range that contains the data you want to analyze.
Run the macro by clicking on "Run" or pressing F5.

Benefits of Creating Multiple Pivot Tables using VBA Macros

Creating multiple pivot tables using VBA macros is a great way to automate tasks in Excel. This method allows you to:

Automate the creation of multiple pivot tables.
Easily switch between different pivot tables using the "PivotTable Fields" pane.
Analyze different fields in a single dataset.

Method 5: Using Excel 2013 and Later Versions

Excel 2013 and later versions provide a new feature called "Multiple Pivot Tables" that allows users to create multiple pivot tables from a single data source. To use this feature, follow these steps:

Select the cell range that contains the data you want to analyze.
Go to the "Insert" tab and click on "PivotTable."
Create a new pivot table and add the fields you want to analyze.
To create additional pivot tables, go to the "PivotTable Tools" tab and click on "Options."
In the "PivotTable Options" dialog box, select the "Actions" tab.
Click on "Create" and select "PivotTable" from the dropdown menu.
Select the cell range where you want to create the new pivot table.

Benefits of Using Excel 2013 and Later Versions

Using Excel 2013 and later versions is a great way to create multiple pivot tables from a single data source. This method allows you to:

Create multiple pivot tables from a single data source.
Easily switch between different pivot tables using the "PivotTable Fields" pane.
Analyze different fields in a single dataset.

Multiple Pivot Tables in Excel Image Gallery

Multiple Pivot Tables in Excel 2013 and Later Versions

In conclusion, creating multiple pivot tables in Excel can be a powerful way to analyze and present data. By using one of the five methods outlined in this article, you can easily create and manage multiple pivot tables in a single workbook. Whether you are using multiple pivot table fields, creating multiple pivot tables from a single data source, or using the Power Pivot add-in, VBA macros, or Excel 2013 and later versions, there is a method that is right for you.