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.
_______________________
Hi Debra, came cross your article as the highest search result for this issue, but your article is actually a little misleading. The “less than” and “greater than” fields show in this filter because the Pivot Table in question has had manual limits applied to the grouped dates. Everything outside of the user-specified dates will be grouped into the “less than” and “greater than” categories.
If the user modifies their grouping to tick the ‘auto’ box by the start and end date, these two categories disappear, and the user can then filter out whichever dates they like, and the grouping will act more predictably.
Hope that’s of use to someone – it was a pain for me to work out!
Thanks Amos, and in my video, those Auto boxes for Start and End are checked. No manual limits were applied.
However, the “less than” and “greater than” items still appear in the drop down list for the date field.