Tuesday, November 20, 2012

Power Pivot: The PivotTable report will not fit on sheet.

During the refresh of data in an Excel Power Pivot next error can occur:

The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns.  (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.

Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.