Pinterest Pixel

Group By Quarters and Years With Excel Pivot Tables

Ever encountered a needing to add quarters to pivot table? I was faced with this same scenario and... read more

Download Excel Workbook
Bryan
Posted on

Overview

Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline

Ever encountered a needing to add quarters to pivot table?

I was faced with this same scenario and looking at my data on hand, I only had sales numbers for each individual day.

Grouping these would take a ton of effort & complex formulas!

Thankfully there is the Pivot Table way to add quarters to pivot table, which is quick, easy, and reduces the risks of making any errors….and it makes updating the report easy with any new additional data!

In the example below I show you how to add quarters to pivot table in Excel:

download excel workbookGroup-by-Quarters-and-Years.xlsx

 

STEP 1:Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

Group By Quarters and Years With Excel Pivot Tables

 

STEP 2: In the ROWS section put in the Order Datefield.

Notice that in Excel 2016 (the version that I am using) it will automatically Group the Order Date into Years & Quarters:

See also  Cannot group that selection in an Excel Pivot Table - SOLUTION!

Group By Quarters and Years With Excel Pivot Tables

 

STEP 3:If you do not have Excel 2016, right-click on any Row value in your Pivot Table and select Group 

Group By Quarters and Years With Excel Pivot Tables

 

STEP 4:In the Grouping dialogue box,Excelwas able to determine our date range (minimum date and maximum date).

Make sure only Quarters and Years are selected (which will be highlighted in blue).

This will group Excel pivot table quarters. Click OK. 

Group By Quarters and Years With Excel Pivot Tables

 

Notice that a Yearsfieldhas been automatically added to our PivotTable Fields List.  This is cool, as we can use this field for further Pivot Table analysis:

Group By Quarters and Years With Excel Pivot Tables

 

STEP 5: In the VALUES area put in the Salesfield. This will get the total of the Sales for each Quarter-Year date range:

Group By Quarters and Years With Excel Pivot Tables

 

Now we have our sales numbers grouped by Years & Quarters!

Notice that we can improve the formatting:

Group By Quarters and Years With Excel Pivot Tables

 

STEP 6: Click the Sum of SALES and select Value Field Settings

Group By Quarters and Years With Excel Pivot Tables

 

STEP 7: Select Number Format

See also  Show The Percent of Parent Column Total With Excel Pivot Tables

Group By Quarters and Years With Excel Pivot Tables

 

STEP 8: Select Currency. Click OK.

Group By Quarters and Years With Excel Pivot Tables

This is how to add years and quarters to Pivot Table. You now have your total sales for each quarterly period!

Group By Quarters and Years With Excel Pivot Tables

Learn more about Excel with our Free Microsoft Excel Online Course! 

If you like this Excel tip, please share it
Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...