PivotTable Filter

I use a spreadsheet to extract data from an access database. When the data is extracted it has about 10k line items and one of the columns displays the date. I formatted the cell to simply display the month (Jan, Feb, Mar, etc). When I create a PivotTable with the 10K line items each line displays the month. When I use the date as a filter it displays every single lines month in the filter box. Is there any way I can combine the months in the filter box so that each month would only display once collectively instead of for each line item?

PT.jpg

Thanks for your assistance

Hi Aeroice,

Did you tried copy and paste the data in a new excel workbook. If you are using the extracted copy from the access file, just try this.

Thanks,
aganesan99

You can't really do it in the filter box itself.

Because, what you've basically done is tell the spreadsheet to decieve you about the cell contents. Even if you format the cell to only show the month, it still has the the year and day as well, so all those different dates will be considered distinct by excel.

The easiest thing to do would be create a helper column that has only the month info instead.

For example, if A1:A100 has date values,

Then create something like
B1 = MONTH(A1)
pull down to B100

Then filter on B instead of A. Because this helper column only has the month instead of the full date, 2014-Jan-01 and 2014-Jan-02 would both return the same value in the helper column, while the date columns would be different.