You can then manipulate that field further, but doing so often has undesirable results.

The technical side of things is simple -- you insert a PivotTable and add the appropriate fields.

If youre grouping by dates, youll have lots of options, but all of the possibilities arent as obvious as you might like.

Group In Excel On For Months How To Group A

In this article, Ill show you how to group a PivotTable by date components.

Ill also show you a few of the less-than-obvious possibilities.

Group In Excel On For Months Download The Example

You can work with almost any dated data or download the example 2010.xlsx, 2013.xlsx, or.xls workbook.

Im using Excel 2010 but noting significant differences between 2003 and 2013.

By default, the PivotTable gives you a good start toward grouping your data without much effort on your part: The PivotTable sorts the dates in a meaningful way The PivotTable sums sold values for the same date and region (sold values that dont share a region arent summed) The PivotTable displays a grand total for each date and region Figure A Our example data and initial PivotTable.

Click PivotTable in the Tables group and click OK (dont change any of the default settings).

In the resulting field list, drag the Date field to the Row Labels list, drag Region to Column Labels, and drag Sold to Values ( Figure B ).

By default, the PivotTable will total the Sold values, but you can change the function.

Then, use the PivotTable Field List to add the fields to the frame as noted in step 4 (Excel 2003 uses the term Data Area instead of Values).

Group by date components Initially, theres a bit of grouping going on already.

Specifically, the PivotTable summarizes by date, which is what you might expect.

To get started, right-click the date column in the PivotTable.

Then, choose Group from the resulting sub-menu to display the dialog shown in Figure C (you can also sort by time values, but we wont explore those options in this article).

Figure C Because youre grouping dates, Excel offers a number of types of date groups.

First, click the selected option to unselect it (you can select multiple options).

Then, select Days and click OK to get the results shown in Figure D.

As you can see, this group ignores the year and combines both April 19 dates.

If you want to sort by days and the year, select both Days and Years in the dialog as shown in Figure E.

You can even add Months to the mix if you want to expand and collapse more details in the resulting table.

To group by months, as shown in Figure F, choose Month from the dialog.

Similarly to the last example, group by Month and Year to consider the year, as shown in Figure G.

Its worth noting that when you add a second (third or more) group using the dialog, Excel adds that group to the PivotTables Field List (see Figure B ).

Group In Excel On Mac For Months