Link to home
Start Free TrialLog in
Avatar of PLA_LTM
PLA_LTM

asked on

Pivot Table Filter: Not showing all items

Hello,
I have a pivot table linked to a data source with over 65K records.
The Filter allows the user to select from the first 10,000 entries.
The user needs to be able to select multiple items beyond the this limit.

How can I make it possible to for the user to select multiple items?

I am open to VBA suggestions.
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

that is a hard limit in excel.

See "Filter Limit" referenced on this page:

http://office.microsoft.com/en-gb/help/excel-specifications-and-limits-HP010073849.aspx

One solution is to create or add some column of data that will allow you to group your items into smaller chunks so that they can be displayed.  For example, let's say that you had a timestamp on every row that included date and time.   You could add another column that was formatted (based on the timestamp) to display only day or only month or only year.

Then you could add that to the filter section of the pivot table.  That sort of divides up your pivot table data into smaller more manageable chunks.

Another solution (similar idea) is to add a slicer.  

Here is a you-tube that clearly demonstrates how to use it.

http://youtu.be/zgt7SdrYJqg
Avatar of PLA_LTM
PLA_LTM

ASKER

The Slicer is a Great idea, I've actually used it. the only issue with it, is speed.
it seems to take much longer to refresh the screen using the slicer vs.  the filter.
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PLA_LTM

ASKER

This is a good read.  thanks for your input.