Limitations of Excel

[wrybel]

Hi Experts,

what experience do you have with analysis large datsets. I mean what are the limits of rows Excel can download from database and analyze smoothly in Pivot table. How this limitations change if I put data to Power Pivot or use 32/64 bit version of Excel.

Thanks for help !

[Sylv!a]

Hi，

As far as I know, row or column fields in a PivotTable report are limited by available memory. See the PivotTable and PivotChart report specifications and limits section in the link

https://support.office.com/en-us/art...7-269d656771c3

Regarding the limitation change in Power Pivot, you may refer to the following article for the relevant information:

https://msdn.microsoft.com/en-us/library/gg413465%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

[wrybel]

I'm not sure but here are some mistakes https://support.office.com/en-us/art...7-269d656771c3 like number of rows for Excel 2007 and 2016/2013 are the same ?

But what are your expeirence ? On what file size Excel can't work smoothly ?

[Kaper]

All Excel versions since 2007 have the same rows limit: 2^20 so 1048576
Older versions (2003-) and of course current versions, but using old file format (xls) had limit of 2^16 rows (65536)

Easy number crunching - just use more POWER :-) so lots of ram, quick i7 processor and 64 bytes operating system + application (to really use RAM)