Alternatively, you can also create calculations in source data to extract the month name and the year from a date field and use the fields in your pivot table. You can group by date periods in a pivot table using the Grouping feature (this may occur automatically depending on your version of Excel). There are two common approaches to grouping by date. If you have valid dates entered in your source data, you can group by month, year or other date period in a pivot table in Excel.

If you ungroup a group of selected items, only the selected items will be ungrouped.Group Dates in an Excel Pivot Table by Month and Yearīy Avantix Learning Team | Updated March 7, 2021Īpplies to: Microsoft ® Excel ® 2013, 2016, 2019 and 365 (Windows) Note that ungrouping a numeric or date and time field will remove all groups for that field. Select any cell in the grouped field and on the Pivot Table Tools | Analyze tab, in the Group group, click the Ungroup button. Right-click the grouped field and select Ungroup. To ungroup data in a pivot table, do one of the following. The resulting report is shown in the image below. To do that, right-click the group header and select the Subtotal 'Field Name' item. You can also enable or disable displaying the subtotal for the created group.

To rename the group, select the group header, press F2 and type the required name. or on the PivotTable Tools | Analyze tab, in the Group group, click the Group Selection button.Īs a result, the selected range will be combined into a single group. Right-click the selected range and select the Group item from the context menu. Select the items that you want to group.Type in start value, end value, interval and click OK. Right-click a cell within a row or column field containing numeric values and select Group. Type the first and last date or time you want to group by, select one or more date or time intervals for grouping and click OK.Īs a result, the date field will be grouped as shown in the image below. or on the PivotTable Tools | Analyze tab, in the Group group, click the Group Field button.

Right-click a cell within a row or column field containing dates and select Group. The following topic describes how to group dates, numbers or selected items in a pivot table. Grouping can help you get a clearer view of data and show only data you want to analyze.

Excel mac pivot table group date