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

group-by-half-years

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

Share on Google+

Related Posts

Pivot Table Styles The default Pivot Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!You can give some life to your Excel Pivot Table by simply changing the Pivot Table Style!For our example, this is our Pivot Table:...
The Ultimate Guide to Excel Pivot Table Slicers Slicers were first introduced in Pivot Tables in Excel 2010 for Windows and Excel 2016 for Mac.Pivot Table Slicers are a visual filter in the form of an interactive button.  There are several cool things that you can do with Pivot Table Slicers, like customize them, filter th...
Show The Percent of Parent Column Total With Excel...  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 COLUMN TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers suc...
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...