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.

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?

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.

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

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.

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

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

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

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.

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.