5 Ways To Create Multiple Pivot Tables On One Sheet

Pivot tables are a powerful tool in Excel, allowing users to summarize and analyze large datasets with ease. One common challenge many users face is how to create multiple pivot tables on a single sheet. This can be useful when you want to display different summaries or views of the same data on the same sheet. In this article, we'll explore five ways to create multiple pivot tables on one sheet.

Understanding Pivot Tables

Before we dive into the methods, let's quickly review what pivot tables are and how they work. A pivot table is a summary of a large dataset that allows you to rotate and aggregate the data to display different views. Pivot tables are created using a set of data, called the data range, and can be customized using various options such as filters, columns, and rows.

Method 1: Using Multiple Data Ranges

One way to create multiple pivot tables on one sheet is to use multiple data ranges. This involves selecting different ranges of data and creating separate pivot tables for each range.

To do this, follow these steps:

• Select the first data range and go to the "Insert" tab in the ribbon.
• Click on the "PivotTable" button and select a cell where you want to place the pivot table.
• Repeat the process for the second data range, selecting a different cell for the second pivot table.
• Customize each pivot table as needed using the "PivotTable Fields" pane.

Method 2: Using the "PivotTable" Button

Another way to create multiple pivot tables on one sheet is to use the "PivotTable" button in the "Insert" tab.

To do this, follow these steps:

• Select the data range you want to use for the first pivot table.
• Go to the "Insert" tab and click on the "PivotTable" button.
• In the "Create PivotTable" dialog box, select a cell where you want to place the pivot table.
• Click "OK" to create the pivot table.
• To create a second pivot table, select a different cell and repeat the process.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to create and manage data models. You can use Power Query to create multiple pivot tables on one sheet.

To do this, follow these steps:

• Select the data range you want to use for the first pivot table.
• Go to the "Data" tab and click on the "From Table/Range" button.
• In the "Power Query Editor" window, select the data range and click on the "Load" button.
• To create a second pivot table, repeat the process and select a different data range.
• Use the "PivotTable" button to create a pivot table for each data range.

Method 4: Using VBA Macro

You can also use VBA macros to create multiple pivot tables on one sheet. This involves writing a macro that creates a pivot table for each data range.

To do this, follow these steps:

• Open the Visual Basic Editor by pressing "Alt + F11" or by navigating to the "Developer" tab.
• In the "Visual Basic Editor" window, create a new module and paste the following code:

Sub CreatePivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim i As Integer
    
    ' Select the worksheet where you want to create the pivot tables
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Select the data ranges for the pivot tables
    Dim dataRanges As Variant
    dataRanges = Array("A1:C10", "E1:G10")
    
    ' Create a pivot table for each data range
    For i = 0 To UBound(dataRanges)
        Set pt = ws.PivotTables.Add(ws.Range(dataRanges(i)))
        pt.Name = "PivotTable" & i + 1
    Next i
End Sub

• Modify the code to suit your needs and run the macro.

Method 5: Using Excel Add-ins

Finally, you can use Excel add-ins to create multiple pivot tables on one sheet. There are several add-ins available that provide this functionality, such as Power Pivot and Excel-Addin.

To do this, follow these steps:

• Download and install an Excel add-in that provides the functionality to create multiple pivot tables.
• Follow the instructions provided by the add-in to create multiple pivot tables.

Gallery of Pivot Table Images

Pivot Table Image Gallery

Conclusion

Creating multiple pivot tables on one sheet can be a powerful way to summarize and analyze large datasets. By using one of the five methods outlined in this article, you can create multiple pivot tables and display different views of the same data on the same sheet. Whether you're using multiple data ranges, the "PivotTable" button, Power Query, VBA macros, or Excel add-ins, the key is to find the method that works best for your needs and to practice using it to get the most out of your data.