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
STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet
STEP 2: In the ROWS section put in the Sales Month field.
STEP 3: Highlight the first 6 months (January – June). Right click on a row in your Pivot Table and select Group
STEP 4: Highlight the next 6 months (July – December). Right click on a row in your Pivot Table and select Group
STEP 5: Rename the groups to make it more readable.
Rename “Group1”to “1st Half”, then rename “Group2” to “2nd Half”.
Notice that a new field Sales Month2 was introduced. This contains our new half-year groupings.
STEP 6: We do not need the Sales Month anymore. Delete Sales Month by dragging it back to the Field List.
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:
STEP 8: Click the Sum of SALES and select Value Field Settings
STEP 9: Select Number Format
STEP 10: Select Currency. Click OK.
You now have your total sales for each half-year period!
