Excel - Why Does Pivot Table Date Grouping Get Removed after a Refresh - Episode 1201
1,465 views · Published 9 April 2010 · 2:58 · Indexed 30 October 2026
Channel: MrExcel.com · 2010 · Education
Microsoft Excel Tutorial: Deb from Wisconsin noticed that her date grouping was wiped out after refreshing a pivot table. Episode 1201 takes a look at how to find the bad dates and solve the problem. Welcome back to the MrExcel netcast, where we bring you the latest tips and tricks for mastering Excel. In this episode, we'll be discussing a common issue that many users face when working with Pivot tables - the dreaded invalid date. This question was sent in by Deb from Wisconsin, and I'm excited to share the solution with all of you. Deb's Pivot table was working perfectly, grouping daily dates into years and months. However, when she received new data for the next month, her group dates went back to daily dates. This can be frustrating and time-consuming, but fear not, there is a simple solution. By using the "Go To Special" function and sorting by cell color, we can easily identify and fix any invalid dates in our data. In this video, I'll walk you through the steps to identify and fix invalid dates in your Pivot table. First, we'll use the "Go To Special" function to select all the cells that contain text constants. Then, we'll change the color of these cells to make them stand out. Next, we'll use the sorting function to bring these cells to the top, making it easier to spot any invalid dates. In Deb's case, we found a February 31st date, which is obviously not a valid date. We'll fix this by changing it to February 28th. But what if you have a large data set and manually checking for invalid dates is not feasible? Don't worry, I have another trick up my sleeve. By holding down the Ctrl key and pressing the ` (grave accent) key, we can view all the dates as the number of days. This makes it easier to spot any text dates, which will show up as text instead of numbers. Once we've identified and fixed all the invalid dates, we can refresh our Pivot table and voila, our group dates are back to normal. I want to thank Deb for sending in this question and giving me the opportunity to share this solution with all of you. If you've ever encountered this issue or have had trouble grouping dates in your Pivot table, this video is a must-watch. Don't forget to subscribe to our channel for more Excel tips and tricks, and I'll see you in the next episode of the MrExcel netcast. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Issue with Pivot table and new data (00:30) Solution using Go To Special and sorting (01:38) Alternative solution using Ctrl + ` shortcut (02:03) Refreshing and removing/re-adding Date field (02:26) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Fixing invalid dates Go To Special Grouping dates in Pivot table Invalid date in new data New data in Pivot table Pivot table Refresh Pivot table Remove and re-add Date field Roll up dates Sort by Cell Color Thanking Dan for the question Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152810/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920