Grouping Dates Add Extra Items in Pivot Table Filter

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.

extra items when grouping by date

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.

timeline in Excel 2013

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.

_______________________

This entry was posted in Group and Total. Bookmark the permalink.

One Response to Grouping Dates Add Extra Items in Pivot Table Filter

  1. Pingback: Spreadsheet Roundup 20150107 – Spreadsheet Day

Leave a Reply

Your email address will not be published. Required fields are marked *