ExcelInsider Team
If you have multiple pivot tables in the same worksheet, you might be familiar with the error that says “A pivot table report cannot overlap another pivot table report”. Microsoft Excel does not allow one pivot table to cover another pivot table. As a result, the program shows an error while refreshing or expanding a pivot table. However, there is a simple fix to this problem. We will learn about that in this article. If you follow all the steps mentioned in this article, you won’t face such issues anymore.
➤ Select one of the pivot tables to move.
➤ Go to the PivotTable Analyze tab on the ribbon.
➤ Select Actions > Move PivotTable.
➤ Enter the new location, and press OK.
This article explains the reasoning behind the error and offers reasonable fixes. To learn how to resolve the error while preserving your data, read the tutorial and follow the steps provided.
Visualization of the "A PivotTable Report Cannot Overlap Another PivotTable Report" Error
We have two pivot tables in our sheet. The first one includes some sales performance data. There are names of the salespersons, the regions they sold the products in, the products, the number of units they sold, and the revenue they earned.
The second pivot table includes stock information. There are item names, the categories the items belong to, the stock amount, the reorder level, and the supplier names.
➤ Both of the pivot tables are semi-collapsed. Let’s try to expand the first pivot table.
➤ Right-click on the A5 cell, and select Expand/Collapse > Expand Entire Field.
➤ If you are on Microsoft 365 and using the latest version, you will see the following error.
➤ If you are on an older version of Microsoft Excel, like Microsoft Office 2016, you will see an error like the following:
➤ The issue happens because the pivot table does not have enough space to expand. There is another pivot table right below that one, which blocks the cells that the first pivot table needs. We need to provide space for the first one to expand.
Solution 1: Move the Pivot Table
The easiest way to make space for the pivot tables and make sure they don’t overlap is just to move a table to another place. We need to make sure that both of the tables have enough space to grow. Follow the steps to do so:
➤ Here, we can move either of the tables, but let’s move the second pivot table to make the worksheet look better.
➤ Click on a cell of the second pivot table to select the table. We are going for A27.
➤ Clicking that cell enables the tabs related to the pivot table on the ribbon. Head to the PivotTable Analyze tab, and find the Actions group.
➤ Click on Move PivotTable
➤ A new window will open that will assist you in moving the pivot table. In the Location section, you can write a new cell reference or click on the target cell.
➤ Press OK to confirm.
➤ Now we can expand the first pivot table without any errors.
Solution 2: Insert Rows/Columns for More Space
In some cases, you might not want to move the pivot table because that changes the layout of the worksheet. Instead, you can add more rows/columns to the worksheet to add more space.
➤ In this worksheet, we need to add only some rows.
➤ Right-click on row number 26, and select Insert.
➤ We have to do this five more times because, as we noticed in the first method, the first table requires 32 rows. This is a drawback of this method because we don’t usually know how much space we need for a pivot table.
➤ Now we can expand the first pivot table with no errors.
➤ Although not needed for this worksheet, if the tables were side by side, we would need to add more columns by right-clicking on the column and selecting Insert.
Frequently Asked Questions
Why is my PivotTable not displaying correctly?
If the pivot table does not display all the data, the first thing you should do is refresh the pivot table by pressing Alt + F5 . The pivot table should refresh the values from the data source and show the data properly. If the pivot table still does not show all the data, select the proper data source by going to PivotTable Analyze > Change Data Source.
Is there a limit to a pivot table?
Pivot tables that are built using a data model can only show 1000 rows at a time. That is a limit to the pivot table that cannot really be bypassed.
How to fix the pivot table layout?
First, select a cell of the pivot table and go to the Design tab on the ribbon. In the Layout group, go to Report Layout. From that icon, you can select Show in Compact/Outline/Tabular Form to select that layout for the pivot table.
Why is my pivot table not grouping correctly?
When you check the boxes in the PivotTable Fields, Excel should move the fields to the suitable areas at the bottom. However, if the groups aren’t the ones you wanted, you can always drag and drop the fields to other areas. You can even change the value types by clicking on the field and selecting Value Field Settings.
How do I reset my PivotTable?
Usually, a better option for resetting a pivot table is to delete the table and recreate it. However, if you don’t want to do that, you can go to the PivotTable Analyze tab when you have the pivot table selected. Then, go to Actions > Clear > Clear All to reset the pivot table.
Wrapping Up
Today, we have learned how to fix the error that says “A PivotTable report cannot overlap another PivotTable report”. The fixes should work for every worksheet in Microsoft Excel. Leave your feedback in the comment section below. Download the Excel file to practice the methods yourself.
