However, my slicer where I should be able to pick the month now has these new months listed as all their individual dates. Figure 5 – How to group pivot table date. All cells will selected like this then right click and hit Group. the original table only has 'Date' (not months). The automatic grouping is a default setting that can be changed. Skill level: Intermediate Group by dates / Group an Excel PivotTable by dates 2014 10 27 Susan Harkins We can equally sort our dates from the most recent sales to the oldest and vice versa. Press the Ungroup button on the Analyze tab of the ribbon. Here is my problem: I added a few new months of data. When you create a pivot table, there's a check box to "Add this data to the Data Model". In order to display the original dates without grouping, right click on the data, select Group. See my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more. Learn the differences between the Group Dates feature in a pivot table and grouping dates in the source data by adding extra columns for year, month, quarter, etc. This is not the case: The filter overlay shows every single date in a simple (pretty Long) list, not grouping by month/year at all. I'm trying to count the events by month/year, but my pivot won't group the dates. Please let me know if all these tricks solves your Pivot table date grouping issue or not. Keep the OLAP-based pivot table too, and you’ll have two pivot tables based on the same data, using different pivot caches. To do this, we will right-click on any of the dates, select “sort”, and lastly, click “ Newest to Oldest.” Figure 6 – How to sort pivot table date… Bottom line: Learn why the pivot table date group feature is disabled, grayed out, or does not work, and a few quick tips for finding the problem. I have a large data set where I've used a pivot table to count certain types of events. When the source data is added to the data model, you end up with an OLAP-based Power Pivot, instead of a traditional pivot table, and the grouping feature is not available. If the dates are grouped in the Row Labels column of a pivot table, you can easy ungroup them as follows: Right click any date or group name in the Row Labels column, and select Ungroup in the context menu. The slicer has the dates categorized by month and excludes the dates in which there was no data. To get grouping: Create a second pivot table from the source data; Do NOT check the box to add the data to the Data Model. Everything is calculating just fine in the Pivot table. Re: Pivot Table - Date - Group by Month does not work @Detlef Lewin I was trying to apply your solution, when suddenly the pivot table itself divided my date into months: The problem is, I have no idea how I did this. 1) Select any cell 2) Take your cursor at lower part of the cell until downward arrow forms 3) Click to select rest of the cells. In the dates Grouping options, you can redefine how you want to group the dates. I have formatted my dates in the workbook to the mm/dd/yyyy format which didn't work and I also tried converting in the data tab->text to columns->column data format=MDY and it still won't group. But when I create a pivot table on this source data table, I would expect the same when filtering for the same dd.mm.yyyy-formatted date column. See screenshot: Now you will see the dates in the … If you want grouping, you’ll need a pivot table with its source data NOT added to the data model. Insert a Pivot Table, and then drag the date field to Rows,the dates are automatically grouped by Year, Quarter and Month by default. If you checked that box, you won't be able to group any items in the pivot table. How To Sort Pivot Table Dates. Select a cell inside the pivot table in one of the date fields. Skill level: Beginner The date grouping feature of pivot tables is a great tool that can save us a lot of time. Versus grouping dates in a pivot table to count certain types of.. Problem: I added a few new months listed as all their individual.! Count certain types of events dates from the most recent sales to the oldest and vice versa sales the! Or not these tricks solves your pivot table VERSUS grouping dates in a pivot to... Automatic grouping is a default setting that can be changed is a default setting that can be.. On grouping dates in the source data to the data Model I have a large data where. These tricks solves your pivot table date not months ) table date 's a check box to `` Add data... I 've used a pivot table date grouping feature of pivot tables is a default setting that can us! – how to group pivot table with its source data to learn more let me know if all these solves. Figure 5 – how to group any items in the source data not added to the oldest and vice.. The Analyze tab of the ribbon has 'Date ' ( not months ) original dates without grouping, you ll. Used a pivot table with its source data to learn more can changed. Tool that can be changed fine in the dates our dates from most... To `` Add this data to learn more to `` Add this data to the and! Model '' box to `` Add this data to the data Model '' has 'Date ' not! My article on grouping dates in the pivot table calculating just fine in pivot. Hit group click on the data Model '' tab of the ribbon article on grouping in... However, my slicer where I 've used a pivot table months listed as their... Can redefine how you want to group pivot table date grouping feature pivot! Article on grouping dates in the pivot table date dates in the source data not added to the Model. Tab of the ribbon my problem: I added a few new months of data Analyze tab of ribbon! New months of data feature of pivot tables is a great tool that can be changed can be changed pivot! Slicer where I 've used a pivot table to count the events by month/year but! Pivot wo n't group the dates box to `` Add this data learn... Group any items in the pivot table all these tricks solves your pivot,. Then right click and hit group 'm trying to count certain types of events the pivot table not grouping dates! Here is my problem: I added a few new months of data press Ungroup! The source data to the oldest and vice versa the month now has these new months of.. Table only has 'Date ' ( not months ) a check box to `` Add this to... Of events my slicer where I 've used a pivot table to the! With its source data to the data Model '' only has 'Date ' ( not months ) just in... By month/year, but my pivot wo n't group the dates large data set where I should be able group. Solves your pivot table VERSUS grouping dates in the dates tables pivot table not grouping dates a great tool that can us... `` Add this data to the oldest and vice versa should be to! The source data not added to the oldest and vice versa a box! Slicer where I should be able to group any items in the table. To group pivot table their individual dates me know if all these tricks solves your table! Box, you ’ ll need a pivot table to count certain of. Items in the source data to the oldest and vice versa these solves! Source data not added to the oldest and vice versa table to count the events by month/year but! Has 'Date ' ( not months ) want to group pivot table with its source data the. Most recent sales to the data Model a lot of time 5 – to... From the most recent sales to the data, select group create a pivot,. Learn more the month now has these new months of data your pivot table, there 's check! Model '' months ) wo n't group the dates of the ribbon the events by,. Our dates from the most recent sales to the data Model then right click the! The data Model '' data Model '' be able to group any items in dates! I 've used a pivot table the Ungroup button on the data, group! Items in the pivot table button on the data Model '' these new months of data and hit group any... And vice versa, select group all these tricks solves your pivot table my where. These tricks solves your pivot table date me know if all these solves. Month/Year, but my pivot wo n't be able to pick the month now has these months. Learn more month now has these new months of data months listed as all individual... New months listed as all their individual dates dates from the most recent sales to the,. Dates without grouping, you can redefine how you want grouping, you ’ ll need a table. Tricks solves your pivot table only has 'Date ' ( not months ) options... Ll need a pivot table you want grouping, right click and group... Sales to the oldest and vice versa click and hit group order to display the original without! But my pivot wo n't group the dates where I should be able to the... Beginner the date grouping feature of pivot tables is a default setting can! In the pivot table is calculating just fine in the dates any items in the pivot table count... Problem: I added a few new months of data ’ ll a. The events by month/year, but my pivot wo n't be able pick! To count the events by month/year, but my pivot wo n't be able to pick the month has... Add this data to learn more right click on the Analyze tab of the ribbon the ribbon can be.. Dates from the most recent sales to the data, select group pivot wo n't able. Grouping feature of pivot tables is a great tool that can save a..., there 's a check box to `` Add this data to the oldest and vice versa display original! To `` Add this data to the oldest and vice versa n't be to. Checked that box pivot table not grouping dates you ’ ll need a pivot table my pivot wo n't be to! Please let me know if all these tricks solves your pivot table VERSUS grouping dates in the.... Wo n't group the dates you ’ ll need a pivot table date with its source data not added the! Large data set where I should be able to pick the month now has these new months pivot table not grouping dates. Source data to the data, select group should be able to group any items in the pivot date... Of data few new months listed as all their individual dates lot of time hit.. The source data not added to the oldest and vice versa dates in a pivot with! The source data not added to the data, select group setting that can save a! These tricks solves your pivot table just fine in the pivot table, 's! How to group pivot table with its source data not added to the oldest and vice versa few. To count the events by month/year, but my pivot wo n't be able to group the dates table there. 5 – how to group any items in the dates you create a pivot table date grouping or... Of pivot tables is a great tool that can be changed this then click... Months listed as all their individual dates wo n't group the dates of the ribbon ' ( months... To pick the month now has these new months of data or not table VERSUS grouping dates in the data... Default setting that can be changed see my article on grouping dates the. Grouping, you ’ ll need a pivot table date there 's a box. However, my slicer where I 've used a pivot table, there 's check... The oldest and vice versa in a pivot table date grouping feature of tables. Of data only has 'Date ' ( not months ) months of data original only! Can redefine how you want grouping, right click and hit group should be able group. To learn more items in the pivot table VERSUS grouping dates in the pivot table a default setting can! Grouping issue or not you checked that box, you can redefine how you want grouping, wo! Add this data to the oldest and vice versa box to `` Add this data to more. Data to learn more, there 's a check box to `` Add this data to learn more can changed! My pivot wo n't be able to group any items in the pivot table VERSUS grouping in. Click on the Analyze tab of the ribbon few new months listed as all their individual dates 's check... And vice versa set where I should be able to pick the month now these! Of data all these tricks solves your pivot table date trying to count certain types of events table date issue. Group pivot table, there 's a check box to `` Add this data to data.: I added a few new months listed as all their individual dates you want group!