Yesterday morning, I was creating a sweet pivot table to measure our development teams’ velocity over time. I had a track-everything spreadsheet that has all the User Stories and Tasks from our TFS Team Project. When I created my pivot table, I wasn’t getting all the values, though!
I did some quick Googling and quickly learned that Excel has limits to the amount of data that can be crunched in a pivot table. Who knew!?
I’m using Excel 2013, and I wasn’t able to find information about the exact limit. Earlier versions of Excel were limited to 32,000 fields across all rows and columns. The specification for Excel 2010 vaguely states that it’s “limited by available memory.” I’m not sure what that means, but I had 22 fields and over 4,000 rows, so I assumed that was my problem.
In order to test this theory, I created a new, smaller query with just the fields I needed for my pivot table. Sure enough, with the smaller data set, everything showed up. And I guess that’s the moral of this story: if you’re missing data from your pivot table, and you’ve got a pretty big data set, you’re probably exceeding the limits.
adamprescott.net 