I am trying to sort data in a pivot table. I am hoping to end up with a table that has row labels with my data types, and then 12 monthly columns with a count of each type in each column. So far I have 12 columns that have a full year total in each column. I now need to filter that down by month. I have a field that has date and time in it, so I would want to filter on that field. I don't see a date option to filter by - rather I have a value filter option. I can only filter on the count of the incident type, rather than on the dates. If I try to manually filter I will be checking hundreds of boxes to get the count for each date in the month. How do I filter this by date?

  • Filter it out of the raw data before the pivot see's it. I believe if you filter the data then refresh the pivot the date will fall off or be just that date... – xQbert Feb 22 '13 at 18:27
  • It's really hard to understand the case - could you please provide a sample? Screen is fine. – Peter L. Feb 22 '13 at 18:27
  • Likely you'll need to create an extra "month" column in your source data - you can then filter/group on that. – Tim Williams Feb 22 '13 at 18:46
  • I would love to add some screenshots here to help clarify my question but can't seem to find how to add them. – dmwesq Feb 23 '13 at 14:56

Add a formula that marks the record for exclusion on column F and then use that as the filter in the pivot table.

Excel Screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.