In our previous post, we were able to Group by Month using Pivot Tables.  Let us take it up another notch, and group it by Half Years!

In the example below I show you how to get the Sales Grouped by Half Years: January to June and July to December

 

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

 

STEP 2: In the ROWS section put in the Sales Month field.

group-by-half-years-02

 

STEP 3: Highlight the first 6 months (January – June). Right click on a row in your Pivot Table and select Group

group-by-half-years-03

 

STEP 4: Highlight the next 6 months (July – December). Right click on a row in your Pivot Table and select Group

group-by-half-years-04

 

STEP 5: Rename the groups to make it more readable.

Rename “Group1”to “1st Half”, then rename “Group2” to “2nd Half”.

group-by-half-years-05

Notice that a new field Sales Month2 was introduced. This contains our new half-year groupings.

group-by-half-years-06

 

STEP 6: We do not need the Sales Month anymore. Delete Sales Month by dragging it back to the Field List. 

group-by-half-years-07

 

STEP 7: In the VALUES area put in the Sales field. This will get the total of the Sales for each half-year range you have defined.

In the ROWS area put in the Financial Year field on top of the Sales Month2 field:

group-by-half-years-08

 

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

group-by-half-years-09

 

STEP 9: Select Number Format

group-by-half-years-10

 

STEP 10: Select Currency. Click OK.

group-by-half-years-11

You now have your total sales for each half-year period!

group-by-half-years-12

 

HELPFUL RESOURCE:

PIVOT BANNER

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn