Depending on your Control Panel settings on your device, valid dates may be entered as month/day/year, day/month/year or year/month/day (although they can be formatted to appear in other ways). The key to grouping by month and/or year in a pivot table is a source field with valid dates (such as OrderDate). Grouping by month and year in a pivot table Source data is typically entered vertically with data in columns and field names at the top of each column of data. Recommended article: How to Delete Blank Rows in Excel Worksheets (Great Strategies, Tricks and Shortcuts)ĭo you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses > 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. 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)
