Group Pivot Table Report Filter Fields

Group Pivot Table Report Filter Fields

Welcome back! The Contextures Blog was out of commission for a couple of weeks, and it’s nice to be up and running again. A few of the shingles blew off during the reconstruction, so if you notice anything missing or broken, please let me know!

Focus on Data with Report Filter Fields

Now that we’re back in business, let’s take a look at the Report Filter fields in a pivot table. In older versions of Excel, these were called Page Fields, and they help you focus on specific data in an Excel pivot table.

Drop fields into the Report Filter area of the pivot table layout. Then, select one or more items from that pivot table field, to see the summarized data for the selected items.

In this example, the Order Date field is in the Report Filter area, and you can select a specific date, to see its orders.

PivotFilterGroup06

Group Dates in the Date Field

If you put a date field in the Report Filter area, there might be a long list of dates in the drop down list. Instead of seeing the individual dates, you might prefer to group them, by year or month.

However, if you right-click on the Report Filter field, there isn’t a command that lets you group the data.

Are you doomed to a miserable existence of scrolling through the date list?

PivotFilterGroup02

The Date Grouping Workaround

Fortunately, there is a workaround that solves the Report Filter grouping problem. It’s not pretty, but it works!

To enable the grouping command, you’ll temporarily move the Report Filter field to the Row Labels area. In the screen shot below, the OrderDate field is being dragged to the Row Labels area.

PivotFilterGroup03

Then, right-click on the field in the pivot table, and click Group. Select the Grouping options that you want, and click OK.

PivotFilterGroup07

Back to the Report Filters Area

Move the grouped fields back to the Report Filter area. In this example, the OrderDate field was grouped by Year and Month, and that created a new field – Years.

Both the Years field and the OrderDate field are dragged back to the Report Filter area.

PivotFilterGroup08

Now, the pivot table can be filtered by year and/or month.

PivotFilterGroup09

Maybe in the next version of Excel you’ll be able to group the fields, without moving them from the Report Filter area.

_________

20 thoughts on “Group Pivot Table Report Filter Fields”

      1. No problem at all, Debra !
        In fact, it was good to re-read a couple of the older posts.
        I sometimes used to face this with Reuters’ feed “ODDLY ENOUGH”.
        At one point, there would be upto 5 ‘re-feeds’ of all the feeds (about 5-6 such feeds) on a given day!
        Now I know why – they must’ve been doing some heavy-duty maintenance work 🙂

  1. Dear Sir,
    I would be grateful if any body can help me to develop a excel report. I have database (Inward Register with date of application,name, amount, date of receipt, date of clarifications & date of sanction) in excel containing various applications received from customers. I want to prepare a another sheet which will contain the list of applications which are not sanctioned. Can you help me out.

  2. I never thought of doing this. Nice tip!
    you could do this with a macro though , and group by pivotfield.pivotitem(index) and then move it back up to the report filter section.
    havent tested this though, but cant see a reason why it wouldnt work? any ideas?

  3. Hi,
    Thanks for interesting note.
    Wondering if anyone knows a good way (using filter or pivot or other) to create something like a queue. For example, say my data set is a few years of time series data graduated at one-minute intervals (eg more than 1mn rows), and i want to create a table which shows a window of 24hours on this data set (ie 1440 rows), such that i can then advance the window forward through time, minute by minute (ie pick up the next datetime stamp and drop off the oldest). Is there a way to do this using filter/pivot which isn’t too computationally intensive? I tried using various index/lookup/match funcitos but it’s too slow given the large size of the data set.
    Any suggestions greatly appreciated!
    Thanks,
    Yug

  4. Hi Debra,
    Thanks, it helped me to resolve a query that i struggled for 3 days. It was to subdivide a a very long list of generator speeds in pivot Table.

  5. Funny that dates are often grouped by default in the dropdown fashion when using regular filters in Excel, but this is the only solution I’ve come across for grouping dates for Pivot Table filters. This is what prompted me to start with Power BI. I know it’s off topic but if you are developing reporting tools and find Excel to be limited and/or tedious I would recommend taking it up. I now have a countless pages of BI reports which require only very limited maintenance and I haven’t encountered any significant limitations.

  6. Thanks for the tip! 1 question: This new field [Date (Year)] is now listed in the Fields area. How to remove it?

    1. It’s only been 10 years, Doug – these things take time! 😉
      Or, as Bill Gates once said, “Plus ça change, plus c’est la même chose”
      P.S. It might have been someone else who said that.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.