Ever encountered needing a quick report of total sales numbers by each quarter, of each year?

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, which is quick, easy and reduces the risks of making any errors….and it makes updating the report easily with any new additional data!

In the example below I show you how to get the Sales Grouped by Quarters and Years:

 

DOWNLOAD EXCEL WORKBOOK

 

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-01

 

STEP 2: In the ROWS section put in the Order Date field.

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

group-by-quarters-and-years-02

 

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-03

 

STEP 4: In the Grouping dialogue box, Excel was 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 our dates by the Quarters and Years.  Click OK.

group-by-quarters-and-years-04

 

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

group-by-quarters-and-years-05

 

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

group-by-quarters-and-years-06

 

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

Notice that we can improve the formatting:

group-by-quarters-and-years-07

 

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

group-by-quarters-and-years-08

 

STEP 7: Select Number Format

group-by-quarters-and-years-09

 

STEP 8: Select Currency. Click OK.

group-by-quarters-and-years-10

 

You now have your total sales for each quarterly period!

group-by-quarters-and-years-11

group-by-quarters-and-years

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Show The Percent of Difference From Previous Years...   I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer. One is using Formulas, but that will take time to set up and you are exposed to errors! The other method is t...
Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation. This is a new calculation in Excel 2010 and onwards. This option will immediately calculate the percentages f...
Refresh All for Excel Pivot Tables When the information in your data source gets updated or changed, you need to Refresh your Pivot Table to see those changes in your Pivot Table. How about if you have multiple Pivot Tables from the same data source in the same workbook? Or; Pivot Tables that are created fro...
Use An External Data Source To Import Data Into An... When creating an Excel Pivot Table, what happens if your data source is in another location? Would you have to copy your data into the same spreadsheet? Well, NO!  You can simply use the External Data Sources feature in your Pivot Table and Excel will magically import the d...