Yes, you can place multiple pivot tables on a single worksheet in Excel, and they will work independently as long as they do not overlap. Each pivot table occupies its own rectangular range, and Excel allows any number of them on the same sheet, provided there is enough space and no two pivot tables share cells.
Why Put Multiple Pivot Tables on One Sheet?
Putting several pivot tables on one sheet is a common way to build a compact dashboard or a summary report. Instead of switching between tabs, you can see sales by region, by product, and by month side by side. This is especially useful when you want to compare different slices of the same data without creating a complex single pivot table with many report filters.
For example, you might have a sales data table with columns: Date, Region, Product, Salesperson, and Amount. You could create one pivot table showing total sales per region, another showing total sales per product, and a third showing monthly totals—all on the same worksheet. Each pivot table uses the same source data but summarizes it differently.
How to Create Multiple Pivot Tables on One Sheet
The process is straightforward—you create each pivot table exactly as you normally would, but you choose the same destination worksheet for all of them. Here are the steps for Excel for Microsoft 365 on Windows:
- Select any cell in your source data range or table.
- Go to Insert > PivotTable.
- In the dialog box, confirm the table/range, then choose Existing Worksheet.
- In the Location box, click a cell where you want the first pivot table to start (for example,
$F$3). - Click OK, then build the pivot table as usual.
- Repeat the process for the second pivot table, but choose a different starting cell (for example,
$F$15or$L$3).
On Mac, the steps are the same: Insert > PivotTable, then choose Existing Worksheet and specify a location. In Excel for the web, you can also place multiple pivot tables on a sheet, but you must ensure the location cells are not already part of another pivot table.
Key Rules and Limitations
Excel does not impose a hard limit on the number of pivot tables per sheet, but practical constraints exist:
- No overlapping: Pivot tables cannot overlap each other. If you try to place a new pivot table over an existing one, Excel will show an error message.
- Space: Each pivot table expands as you add fields, so leave enough empty rows and columns between them. If a pivot table grows and collides with another, Excel will warn you.
- Performance: Many pivot tables on one sheet can slow down recalculation, especially with large data sources. Each pivot table stores a cache, but they share the cache if they use the same source data—this is efficient.
- Filtering: Each pivot table has its own filters and slicers. If you want to control multiple pivot tables with one slicer, you need to connect the slicer to each pivot table individually (right-click slicer > Report Connections).
Using Slicers to Control Multiple Pivot Tables
A common reason to put multiple pivot tables on one sheet is to create an interactive dashboard. Slicers are visual filter buttons that can control several pivot tables at once. To connect a slicer to multiple pivot tables:
- Create a slicer for one pivot table (select the pivot table, go to PivotTable Analyze > Insert Slicer).
- Right-click the slicer and choose Report Connections (or PivotTable Connections on Mac).
- Check the box next to each pivot table you want the slicer to control.
- Click OK.
Now, when you click a slicer button (e.g., a specific region), all connected pivot tables update simultaneously. This is a powerful way to build a one-sheet report where every summary responds to the same filter.
Common Problems and How to Avoid Them
Several issues arise when working with multiple pivot tables on one sheet:
- “PivotTable will overlap” error: This happens when the new pivot table’s range would overlap an existing one. Solution: choose a location with more empty space, or move the existing pivot table.
- Pivot tables not refreshing together: If you change source data, you must refresh each pivot table individually (right-click > Refresh) or use Data > Refresh All (on the ribbon, under the Data tab).
- Layout issues when printing: Multiple pivot tables may not print neatly on one page. Use Page Layout > Print Area to set a print area, or adjust page breaks.
- Accidental deletion: If you delete a cell inside a pivot table, you get an error. Always select the entire pivot table before deleting (click on it, then press Ctrl+A twice, or use the PivotTable Analyze menu).
Best Practices for Organizing Multiple Pivot Tables
To keep your sheet readable and maintainable:
- Give each pivot table a clear title in a cell above it, so you know what it summarizes.
- Leave at least one blank row and one blank column between pivot tables to prevent accidental overlap when they refresh.
- Use consistent formatting (e.g., same number format for currency) across all pivot tables.
- Consider using a separate “Dashboard” sheet for the pivot tables and keep raw data on another sheet—this avoids clutter.
- If you have many pivot tables, consider using the PivotTable Options to disable “Autofit column widths on update” to prevent layout jumps.
Alternative Approaches
If you find multiple pivot tables on one sheet cumbersome, consider these alternatives:
- One pivot table with multiple value fields: You can drag multiple fields into the Values area to show different metrics (e.g., Sum of Amount and Count of Orders) side by side.
- Pivot Chart with a single pivot table: A single pivot table can feed multiple charts, each showing a different view.
- Power Pivot and Cube functions: For advanced dashboards, you can use Power Pivot to create a data model and then use CUBEVALUE functions to place individual values anywhere on a sheet—this gives more layout flexibility than pivot tables.
- Grouping fields: Use the “Show Values As” options (e.g., % of Grand Total) to get different perspectives from one pivot table.
Frequently Asked Questions
Can I have two pivot tables on the same sheet using the same data source?
Yes, absolutely. Both pivot tables can use the exact same source range or table. Excel will create a separate pivot cache for each, but if the source is identical, it may share the cache automatically (this is an internal optimization). You can build one pivot table for sales by region and another for sales by product, both from the same data.
How do I stop pivot tables from overlapping when I refresh?
Overlap errors happen when a pivot table expands and its range touches another pivot table. To prevent this, leave generous space between them—at least a few rows and columns. Also, in PivotTable Options (right-click > PivotTable Options > Layout & Format), uncheck “Autofit column widths on update” to reduce column width changes that can cause collisions.
Can I connect multiple pivot tables to one slicer?
Yes. Right-click the slicer, choose “Report Connections,” and then check the pivot tables you want it to control. This works for pivot tables on the same sheet or even on different sheets, as long as they use the same source data or share a pivot cache.
Why do my pivot tables show the same data even though I filtered one?
Each pivot table has its own filter settings. If you filter one pivot table, it does not affect others unless they are connected via a slicer or a timeline. If you want all pivot tables to filter together, use a slicer and connect it to all of them.
Is there a limit to how many pivot tables I can put on one sheet?
Excel does not document a specific limit. The practical limit is based on available memory and the size of your data. You can have dozens of small pivot tables on one sheet, but performance may degrade with hundreds. Always keep an eye on file size and recalculation time.
Related
- Excel: How to Add Lines in a Cell (3 Easy Ways)
- Excel Hotel Group Jobs: Hiring, Pay, and How to Apply
- Excel Holiday Schedule: How to Build One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Template: Create and CustomizeAn Excel Gantt chart template lays out project tasks against dates, using horizontal bars to show when each…
- Excel Pivot Table Not Pulling All Data? Fix ItIf an Excel PivotTable is missing rows, first refresh it, then check whether its data source includes those…
- Excel Speedometer Chart With Two NeedlesYes. In Excel for Microsoft 365 on Windows, you can make a two-needle speedometer by building a doughnut-chart…
- Excel Pivot Chart Scatter Plot: Limits and WorkaroundsNo—Excel for Microsoft 365 on Windows does not offer an XY (scatter) chart as a PivotChart type. You…
- Pivot Table Error Messages: Causes and FixesA PivotTable error message in Excel is not one single error: the wording points to the cause, such…
- Excel Pivot Table Spill Error: Meaning, Causes and FixesIn Excel, a spill error is the standard #SPILL! error: a dynamic-array formula cannot put all of its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.