Group Pivot Table Report Filter Fields

image 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 dropdown 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.

_________

You may also like...

16 Responses

  1. Khushnood Viccaji says:

    So _that_ explains the reappearance of 10 old posts in my RSS reader yesterday ;o)

      • Khushnood Viccaji says:

        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 :)

  2. Bob Ryan says:

    Debra – Glad you made it back!

  3. Vinod Gaikwad says:

    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.

  4. Excel Addin says:

    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?

  5. Yug says:

    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

  6. Patt says:

    Excellent workaround! It was exactly what I was look for. Thank you for sharing your knowledge.

  7. Elnur Isayev says:

    Thank you. Worked perfectly. Exactly what I needed :)

  8. SherryD says:

    Awesome thank you thank you!

  9. Raj says:

    thank you very much!!!! love u man

  10. Chris says:

    I have Microsoft Excel 2013, and your solution is not working anymore… Any clue how we can do this now ?

  11. Usman says:

    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.

Leave a Reply to Raj Cancel reply

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