How To Add Year Filter To Pivot Table Filter

The ability to apace group dates in Pivot Tables in Excel can be quite useful.

It helps you analyze information past getting different views past dates, weeks, months, quarters, and years.

For instance, if you have credit card information, you may desire to group it in dissimilar ways (such equally grouping by months or quarters or years).

Similarly, if yous have a call center data, then you may want to grouping it by minutes or hours.

Spotter Video – Grouping Dates in Pivot Tables (Grouping by Months/Years)

How to Group Dates in Pivot Tables in Excel

Suppose you accept a dataset as shown beneath:

It has sales data by Appointment, Stores, and Regions (East, W, North, and South). The data spans across 300+ rows and 4 columns.

Here is a simple pivot tabular array summary created using this data:

This pivot table summarizes sales data by date, but it isn't quite helpful every bit it shows all the 300+ dates. In such every bit instance, information technology would be improve to have the dates grouped by years, quarters, and/or months

Download Data and follow along.

Grouping by Years in a Pivot Table

The dataset shown to a higher place have dates for two years (2014 and 2015).

Here are the steps to grouping these dates by years:

This would summarize the pivot tabular array past years.

This summarization by years may exist useful when you lot have more number of years. In this example, it would exist better to have the quarterly or monthly data.

Grouping by Quarters in a Pivot Table

In the above dataset, it makes more sense to drill downwardly to quarters or months to have a better understanding of the sales.

Here is how y'all can group these by quarters:

This would summarize the pivot table by quarters.

The upshot with this pivot tabular array is that information technology combines the Quarterly sales value for 2014 as well as 2015. Hence, for each quarter, the sales value is the sum of sales values in Quarter i in 2014 and 2015.

In a real life scenario, you are nearly likely to analyze these quarters for each yr separately. To do this:

This would summarize the data by Years and so inside years by Quarters. Something as shown below:

Note: I am using the tabular class layout in the in a higher place snapshot.

When you lot group dates past more than ane fourth dimension-frame group, something interesting happens. If you look at the field listing, you volition notice a new field has automatically been added. In this case, it is Years.

Note that this new field that has appeared is not a office of the data source. This field has been created in the Pin Enshroud to quickly group and summarizes data. When you ungroup the information, this field will vanish.

The do good of having this new field is that now yous tin analyze the information with quarters in rows and years in columns, as shown beneath:

All you demand to exercise is drop the Year field from Row area to Columns area.

Group past Months in a Pivot Table

Like to the way we grouped the data by quarters, we can likewise do this past months.

Again, it is advisable to use both Year and Month to group the information instead of just using months (unless you lot merely have data for one or less than a year).

Here are the steps to practise this:

Select any jail cell in the Appointment column in the Pivot Table.
Go to Pivot Table Tools –> Analyze –> Group –> Group Selection.
In the Group dialogue box, select Months as well as Years. You can select more one pick past simply clicking on it.
Click OK.

This would group the appointment field and summarize the data as shown below:

Again, this would atomic number 82 to a new field of Years getting added to the PivotTable fields. You can simply elevate the years' field to the columns area to go the years in columns and months is rows. You will go something equally shown below:

Grouping by Weeks in a Pivot Table

While analyzing data such as store sales or website traffic, it makes sense to analyze it on a weekly basis.

When working with dates in Pivot Tables, grouping dates by week is a bit different than grouping by months, quarters, or years.

Here is how y'all can group dates by weeks:

Select any cell in the Date column in the Pivot Table.
Get to Pivot Table Tools –> Analyze –> Group –> Grouping Pick.
In the Grouping dialogue box, select Days and deselect any other selected option(s). Every bit soon equally you do this, you would notice that the Number of Days option (at the bottom right) becomes available.
- There is no inbuilt option to group past weeks. You need to group past days and specify the number of days to be used while grouping.
- Notation that for this to piece of work, you need to select Days option only.
In Number of days, enter vii (or use the spin push to brand the change).
If you click OK at this indicate, your information would be grouped past weeks starting with January four, 2014 – which is a Saturday. Then the grouping would be from Saturday to Friday every calendar week. To modify this grouping and to begin the week from Monday, you need to change the start engagement (past default information technology picks the start date from the source data).
- In such a example, you can either start the date on December 30, 2013, or January 6, 2014 (both Mondays).
Click OK.

This will group the dates by weeks as shown below:

Similarly, you can group dates by specifying any other number of days. For case, instead of weekly, yous can group dates in a biweekly interval.

Note:

When you grouping dates by using this method, you tin not grouping it using whatever other option (such as months, quarters or years).
Calculated field/particular would non work when you lot group using Days.

Grouping by Seconds/Hours/Minutes in a Pivot Tabular array

If you working with loftier volumes of information (such every bit phone call center data), you may want to group it by seconds or minutes or hours.

You lot tin utilise the same procedure to group the information by seconds, minutes, or hours.

Suppose y'all have the call center data every bit shown below:

In the to a higher place data, the date is recorded along with the time. In this instance, it may brand sense for the call center managing director to analyze how the call resolve numbers are irresolute per hour.

Here is how to grouping the days past Hours:

This will group the information past hours and you will get something equally shown below:

You tin come across that the Row Labels here are 09, 10, so on.. which are the hours in a day. 09 would mean 9 AM and eighteen would be vi PM. Using this pivot tabular array, you can easily identify that most calls are resolved during 1-2 PM.

Similarly, you can also grouping the dates on seconds and minutes.

How to Ungroup Dates in a Pivot Tabular array in Excel

To ungroup dates in pivot tables:

Select any cell in the date cells in the pivot table.
Go to PivotTable Tools –> Analyze –> Group –> Ungroup.

This would instantly ungroup any grouping that y'all have washed.

Download the Instance File.

Other Pin Table Tutorials You lot May Similar:

Creating a Pivot Table in Excel – A Footstep by Pace Tutorial.
Preparing Source Data For Pivot Tabular array.
How to Group Numbers in Pivot Table in Excel.
How to Filter Data in a Pivot Table in Excel.
How to Utilize Conditional Formatting in a Pivot Table in Excel.
How to Refresh Pivot Tabular array in Excel.
How to Add and Use an Excel Pivot Tabular array Calculated Field.
Using Slicers in Excel Pivot Table.
How to Replace Blank Cells with Zeros in Excel Pivot Tables.
Count Distinct Values in Pivot Table