Last week, someone asked me how they could get rid of the extra items that appear in the filter, when you group a date field. Maybe you’ve seen them too – they start with a less than or greater than symbol, and show the first and last dates in your pivot table data.
You can see an example in the screen shot below.
No Way to Remove Them
The bad news is that there isn’t a way to remove those items, when you group a date field. However, if you want to make them a little less confusing, you can clear the labels, so the dates don’t show — you’ll just see blank labels. Click this link for instructions on how to replace the captions with blank spaces.
Another solution is to add new fields in the source data, to calculate the year and quarter or month, for each date. Then add those fields to the pivot table.
Use a Timeline in Excel 2013
If you’re using Excel 2013, you can use a timeline to filter by year, quarter, month or day, without grouping. It’s one of the new features in this version, and makes it very easy to filter for a date range.
Video: Grouping Dates in Excel Pivot Table Shows Extra Items
Watch this video to see the steps for grouping the dates and hiding the extra labels, or adding a timeline filter.
Download the Sample File
To download the sample file used in this video tutorial, please visit the Pivot Table Grouping page on my Contextures website.