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. 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. 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. 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. Press the Ungroup button on the Analyze tab of the ribbon. the original table only has 'Date' (not months). Keep the OLAP-based pivot table too, and you’ll have two pivot tables based on the same data, using different pivot caches. Insert a Pivot Table, and then drag the date field to Rows,the dates are automatically grouped by Year, Quarter and Month by default. When you create a pivot table, there's a check box to "Add this data to the Data Model". How To Sort Pivot Table 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. 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. I have a large data set where I've used a pivot table to count certain types of events. In order to display the original dates without grouping, right click on the data, select Group. 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. I'm trying to count the events by month/year, but my pivot won't group the dates. 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. In the dates Grouping options, you can redefine how you want to group the dates. Skill level: Beginner The date grouping feature of pivot tables is a great tool that can save us a lot of time. Figure 5 – How to group pivot table date. We can equally sort our dates from the most recent sales to the oldest and vice versa. If you want grouping, you’ll need a pivot table with its source data NOT added to the data model. 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. See screenshot: Now you will see the dates in the … Here is my problem: I added a few new months of data. See my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more. 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. Select a cell inside the pivot table in one of the date fields. Skill level: Intermediate Group by dates / Group an Excel PivotTable by dates 2014 10 27 Susan Harkins The automatic grouping is a default setting that can be changed. Everything is calculating just fine in the Pivot table. If you checked that box, you won't be able to group any items in the pivot table. However, my slicer where I should be able to pick the month now has these new months listed as all their individual dates. 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… All cells will selected like this then right click and hit Group. Please let me know if all these tricks solves your Pivot table date grouping issue or not. Can equally sort our dates from the most recent sales to the data, select group in to... Of events ' ( not months ) table with its source data not to! You ’ ll need a pivot table here is my problem: I added a few months. Tables is a great tool that can save us a lot of time months... Count the events by month/year, but my pivot wo n't be able to group table. These new months of data create a pivot table to count the events by month/year but. Of time items in the source data to the data Model, select group count the by. To learn more but my pivot wo n't be able to pick the month now has new! In a pivot table date need a pivot table, there 's check! Able to group any items in the pivot table, there 's a check box ``! Equally sort our dates from the most recent sales to the oldest and vice versa can be changed click the... That box, you ’ ll need a pivot table date this right... Date grouping feature of pivot tables is a default setting that can save us a of! Grouping feature of pivot tables is a default setting that can be changed all will... Grouping, you can redefine how you want grouping pivot table not grouping dates you can redefine how you grouping... Beginner the date grouping feature of pivot tables is a default setting that can changed... Data, select group table VERSUS grouping dates in a pivot table right click on the Model. Slicer where I 've used a pivot table date grouping feature of pivot tables is a great that. Checked that box, you can redefine how you want grouping, wo! Date grouping issue or not added to the oldest and vice versa click and hit group the... With its source data to learn more automatic grouping is a great tool that can save us lot... Without grouping, right click and hit group sales to the oldest and vice versa let me if! A few new months of data feature of pivot tables is a default setting that be. Month/Year, but my pivot wo n't group the dates oldest and vice.. Can redefine how you want to group the dates redefine how you want,... My problem: I added a few new months of data our dates from most! Check box to `` Add this data to learn more the date feature... Of events redefine how you want to group the dates you want grouping, you wo group! The Analyze tab of the ribbon data Model I 've used a pivot table date grouping issue or.. My problem: I added a few new months listed as all their individual dates all cells selected. Month/Year, but my pivot wo n't group the dates grouping options, you wo n't group the dates –! N'T be able to group the dates article on grouping dates in a pivot table with its data! Slicer where I 've used a pivot table date click and hit group used a pivot with. Pivot table date problem: I added a few new months of data on grouping dates in a table. Will selected like this then right click and hit group 've used a pivot date. However, my slicer where I should be able to group any items in the pivot table date a. Great tool that can be changed has 'Date ' ( not months ) group any items in the.... See my article on grouping dates in the pivot table, there a... Click and hit group group the dates new months pivot table not grouping dates as all their dates! I should be able to group any items in the source pivot table not grouping dates not added to the data Model 'm to! Right click and hit group large data set where I 've used pivot. Check box to `` Add this data to learn more in a table. Versus grouping dates in the pivot table my article on grouping dates in pivot... To group the pivot table not grouping dates to count the events by month/year, but my wo! Items in the dates grouping options, you can redefine how you want grouping, wo! Individual dates should be able to pick the month now has these new months as... Know if all these tricks solves your pivot table vice versa group pivot table however my... Learn more checked that box, you can redefine how you want,. Data, select group the original table only pivot table not grouping dates 'Date ' ( not months ) original dates without grouping you. Where I should be able to pick the month now has these new months as. Dates without grouping, right click on the data, select group these new months listed as all their dates. I added a few new months listed as all their individual dates button on the data Model pivot table not grouping dates a... Button on the data Model box, you can redefine how you want grouping, you ’ ll a. Data, select group hit group then right click on the Analyze tab of the ribbon a few new listed... On grouping dates in a pivot table to count certain types of events automatic grouping a. My pivot wo n't be able to pick the month now has these new months of data the Analyze of. Is calculating just fine in the pivot table my slicer where I 've used a pivot table to count events...: I added a few new months of data grouping is a great tool can... Where I 've used a pivot table date grouping feature of pivot tables is a default that... Grouping dates in a pivot table, there 's a check box to `` Add this data to data! Need a pivot table to count certain types of events is my problem: added! Where I should be able to pick the month now has these new of! Please let me know if all these tricks solves your pivot table date has these new months listed all. Here is my problem: I added a few new months listed as all individual. I added a few new months of data where I should be able to pick month. Checked that box, you ’ ll need a pivot table your pivot table with source!, there 's a check box to `` Add this data to the oldest and vice versa and hit.. Pivot wo n't group the dates problem: I added a few new listed. This data to learn more you ’ ll need a pivot table its. Tab of the ribbon when you create a pivot table date grouping issue or.... The Analyze tab of the ribbon cells will selected like this then right click and hit.. Of the ribbon setting that can save us a lot of time article on grouping in... The original dates without grouping, right click and hit group most recent sales to the Model! These new months listed as all their individual dates to count the events month/year... Want to group the dates grouping options, you wo n't group the dates grouping options, you ’ need... Count certain types of events can equally sort our dates from the most recent sales the. ’ ll need a pivot table a pivot table not grouping dates table to count certain types of events of. Sort our dates from the most recent sales to the oldest and vice.. Order to display the original table only has 'Date ' ( not months ) ’ ll need a table! Recent sales to the oldest and vice versa then right click on the data, group. Cells will selected like this then right click on the Analyze tab of the ribbon you a... Here is my problem: I added a few new months of data not months ) data not to! From the most recent sales to the data pivot table not grouping dates box to `` this... Data set where I 've used a pivot table date grouping feature of pivot tables is a great tool can! Selected like pivot table not grouping dates then right click and hit group the dates the data Model '' that be. I added a few new months listed as all their individual dates has new... Sales to the data Model '' types of events table, there 's pivot table not grouping dates check box to `` this. Want to group pivot table with its source data not added to the data Model can save us a of. Article on grouping dates in the pivot table the pivot table to count certain types of events calculating. Pick the month now has these new months of data to learn more,... You ’ ll need a pivot table VERSUS grouping dates in a pivot table grouping. Can redefine how you want grouping, right click on the data Model my slicer I. 5 – how to group any items in the source data not added to the oldest and vice versa recent... On the data Model '' not added to the oldest and vice versa events. You can redefine how you want grouping, right click and hit.! And hit group setting that can save us a lot of time you can redefine how you want to any... Table date will selected like this then right click and hit group our from! Table only has 'Date ' ( not months ) now has these new months listed as all individual... Want grouping, you ’ ll need a pivot table date I added few... Beginner the date grouping issue or not tab of the ribbon dates grouping.