Pinterest Pixel

Group By Half Years With Excel Pivot Tables

Bryan
 .
Group By Half Years With Excel Pivot Tables | MyExcelOnline Group By Half Years With Excel Pivot Tables | MyExcelOnline

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

 

Group By Half Years With Excel Pivot Tables | MyExcelOnline

download excel workbook Group-by-Half-Years.xlsx

 

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 With Excel Pivot Tables

 

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

Group By Half Years With Excel Pivot Tables

 

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 With Excel Pivot Tables

 

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 With Excel Pivot Tables

 

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 With Excel Pivot Tables

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

Group By Half Years With Excel Pivot Tables

 

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 With Excel Pivot Tables

 

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 With Excel Pivot Tables

 

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

Group By Half Years With Excel Pivot Tables

 

STEP 9: Select Number Format

Group By Half Years With Excel Pivot Tables

 

STEP 10: Select Currency. Click OK.

Group By Half Years With Excel Pivot Tables

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

Group By Half Years With Excel Pivot Tables

 

PIVOT BANNER

If you like this Excel tip, please share it
Group By Half Years With Excel Pivot Tables | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Show The Percent of Parent Row Total With Excel Pivot Tables

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...