Hi,

I currently have a requirement to expose 150+ columns within a Pivot Table using Excel 2013 64 Bit. The data source of the Pivot Table is a PowerPivot model. The PowerPivot model itself seems to cope well with the large number of calculated columns.

I've already briefed the client in that you need to slice the records within a Pivot Table when your PowerPivot data source has multi million rows.

However is there a practical limit on how many columns you can expose to a single Pivot Table. Since my Pivot Table with 150 columns and only a few rows (with a test data set) is very sluggish.

Resource utilisation on the server such as CPU, disk and memory usage are all around 10% when the Pivot Table appears to freeze.

I am thinking of recommending to my client that the Pivot Table should be split into two or more Pivot Tables over different Excel sheets.

I am aware of the following link; -

https://support.office.com/en-za/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f

Thanks in advance,

Kieran.

Kind Regards,

K

Ive just been given a useful steer by a mentor; -
To identify with more confidence that the issue is with Pivot Table as opposed to PowerPivot; -
1) Create a new Pivot Table with the PowerPivot model as a data source.
2) Expose 10 columns from the PowerPivot model to the Pivot Table model resulting in a distinct set of rows.
3) Repeat step 2 exposing the same PowerPivot model columns to the same Pivot Table say 15 times resulting in 150 columns in total.
I understand that the above is an important exercise to go through since; -
Even though each table individually within PowerPivot performs well. Pivot Table uses a given combination of relationships between the PowerPivot tables which cant be recreated by browsing within the PowerPivot mo