Excel 2013 PIVOT Limitations

[kalikj]

Can anyone please help. Our company is in the process of moving over from Excel 2007 to 2013. In Excel 2007 it was possible to have a maximum of 65,534 items in a pivot field before the software would apply a cut-off and display a limited list of items. I have noticed that in Excel 2013 when opening that same workbook (originally created in 2007) then the same pivot table will only display 10,000 items in a field before applying a cut-off. Is my observation correct? Is this to do with the version of 2013 (i.e. home / office Vs professional)? Is there any way around this - i.e. can I as a user specify / increase this maximum? I am finding this limitation severly restricting and really need to find away around it. Best regards, Jason

 

[Andrew Poulsom]

In my version of Excel 2013 I can create a pivot table with over 1 million unique row fields. I have Office Professional Plus.

 

[kalikj]

Andrew, thank you - 1 million would be truly wonderful. I assume therefore that the 10,000 maximum is related to the fact that the version is Microsoft Office Home and Business 2013. Is there anywhere I can verify whether this is the case - or is there anywhere within the programme where these maxima can be set.

Best regards,

Jason

 

[Andrew Poulsom]

In what way are you experiencing a limit of 10000?

 

[kalikj]

I should have clarified - I have been looking at the Excel 2013 specifications, whihc state that there is a limit of 10,000 items displayed in filter drop-down lists within a pivot table. This was not the case in 2007 where I believe up to 65,534 could be displayed. For example, the pivot tables we are using may have ~20,000 items in a filter list within a drop-down filter - these could all be displayed in 2007 so that any items could be selected. In 2013, only the first 10,000 are displayed, limiting the options available for selection. This ii turn makes it difficult to work with as an item that I may wish to select from the drop-down may not be displayed.

Best regards,

Jason

 

[Andrew Poulsom]

That limit was introduced in Excel 2010. You can use the Search box above the list to display items beyond the bottom of the list.

 

[kalikj]

Andrew,

Thank you, and sorry to have troubled you with something so basic!!! I was not familair with this feature, and in my "bewilderment", completely missed the search box!!! That single feature completely resolves any issues that I have been experiencing!

Once again, thank you for your help.

Best regards,

Jason

 

[stiksandstones]

Google search led me to this thread, but doesnt work for me. Using excel 365 for mac....I have a filtered list in a pivot table, when I open it, I do not see ALL of my customers (looking to select ALL, minus one big customer), the list only goes down to about the "G" letters, any tips? Search bar doesnt reveal the big customer with an R, I have tried typing in some J customers too, no hits, I can't imagine I have more than 10000 in there?