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!





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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Group By Custom Dates With Excel Pivot Tables In my previous Group By Month post, we grouped our Sales by each month.However the cool thing with Excel is that we can take that a step further and customize our grouped date range!In the example below I show you how to get the Sales Grouped by Custom Dates:  ...
Show The Percent of Row Total With Excel Pivot Tab... 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 ROW TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...
Sort an Excel Pivot Table Manually You have your Pivot Table ready, all sorted nicely both from a row and column perspective.  However you just need that one minor sorting tweak or two.Well, Excel seemingly has a lot of tricks and you can even sort an Excel Pivot Table manually!For our example, let's see t...
Sort Largest to Smallest Grand Totals With Excel P...  I knew that I could sort virtually anywhere with Excel Pivot Tables, but I was surprised that I could even sort from largest to smallest with Grand Totals!Below I have an Excel Pivot Table that consists of Sales Numbers over a three year period.Make sure to down...