How do I filter the source data after an Excel Pivot Table is created ?
Closing Date: 18 October 14
Started on: 18 August 14
Participants: 2
I have been struggling with this for a while now.
I am able to create a Pivot Table, no worries there. The data is a list of "user actions" over time. There is a timestamp kind of ID that groups multiple user action line-items.
So the pivot table does not use the ID mentioned above as either row or column headings. But, since I know that some ID-s are false data (e.g. created by a non-human script) - I need to exclude some ID-s from the final pivot-table rendered.
One way to do this is to somehow remove all the ID-s from the source data and re-do the pivot table. For some strange reason, excel does not support re-rendering the pivot table if you have "filter data" switched on - on the source data. At least I could not find a way.
Any elegant solutions out there ?
I am able to create a Pivot Table, no worries there. The data is a list of "user actions" over time. There is a timestamp kind of ID that groups multiple user action line-items.
So the pivot table does not use the ID mentioned above as either row or column headings. But, since I know that some ID-s are false data (e.g. created by a non-human script) - I need to exclude some ID-s from the final pivot-table rendered.
One way to do this is to somehow remove all the ID-s from the source data and re-do the pivot table. For some strange reason, excel does not support re-rendering the pivot table if you have "filter data" switched on - on the source data. At least I could not find a way.
Any elegant solutions out there ?
This challenge is listed under
Development & Implementations
and Data & Information Management
Community
Related Posts:
2 Suggestions
Report Filter is one way to go, Peter!
As you already know that some IDs are wrong, change the vlaue of the wrong entries by entering a alphabet or make them blank in the source data (do the changes only in the IDs column)
Then vist the pivot table and add the ID to the Report filter section.
Now you should be seeing a combo (dropdown) with option to select multiple items. Click on the option and select all and uncheck the alphabet/blank field (should be showing at the bottom of the list).
Click Ok to Apply the filter.
Now you should be able to see the filtered results in the pivot table.
Hope your objective is achieved without creating a new pivot table.