Excel 2013 PIVOT Limitations

kalikj

Board Regular
Joined
Sep 4, 2009
Messages
108
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
In my version of Excel 2013 I can create a pivot table with over 1 million unique row fields. I have Office Professional Plus.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top