Friday, 19 September 2014

Excel - A PivotTable report cannot overlap another PivotTable report

In Excel 2013 click on an existing pivot table and click Refresh to refresh the data in the pivot table. The following error message may appear:
A PivotTable report cannot overlap another PivotTable report.



Reason
Somewhere in your workbook there are two pivot tables on one sheet that are close together. When you refresh one of those pivot tables 'grows' in size (because of a change in data perhaps).


Solution
The pivot table you clicked refresh on may not be the one that's causing the trouble. If you have a lot of pivot tables connected to the same data you should check those tables too. Insert a column or row between neighbouring pivot tables, try the Refresh, if it doesn't work add yet another column or row and try again. You will find a neighbouring pivot table that was causing the trouble.

For me, I found that I had a Grand Total column off on a pivot table but when I refreshed it turned on, causing it to bump up against another pivot table.

To avoid this issue altogether make sure there is at least a couple of columns/rows between pivot tables or place them on separate sheets if you can.

IMPORTANT: If you click Refresh on a pivot table that is on its own on one sheet and it gives the above error it is because clicking the Refresh button tries to update all pivot tables connected to that one data source. Therefore somewhere else, on another sheet perhaps, you must have two pivot tables side by side and one is growing in size with the refresh and 'hitting' the other one. Therefore if you get this error, look through all your sheets, not just the one you are working with.


Excel Version?
I experienced this issue with Excel 2013 but I presume it will be the same with Excel 2010 and maybe even earlier versions. If anyone has additional information please feel free to add your comments below.


Post a Comment