Excel - How To Group Dates Into Weeks Excel Pivot Table then Sort Recent to Top - Episode 1939
3,585 views · Published 19 November 2014 · 3:30 · Indexed 8 November 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Sort an Excel Pivot Table that is grouped by week to show most recent week at the top. Welcome to the MrExcel podcast! In this episode, we will be tackling a question that came in through my YouTube channel - how to group a Pivot table by week, starting with the most recent week at the top. This may seem like a simple task, but as you will see, it can get quite complicated and ugly. First, we start by copying the date column and formatting it to show the day of the week. Then, we need to group the data by week, starting on a Monday. This requires us to manually select the first Monday of the year, which can be a tedious task. Once the data is grouped, it appears in the earliest-to-latest sequence, which is not ideal for our purpose. The real problem arises when we try to sort the data in descending order, with the most recent week at the top. Due to the data being in text format, the sorting becomes a nightmare. For example, September 9th will appear before December because the number 9 comes before 12 in the alphabet. This is where our solution comes in, although I must admit, it is not the prettiest. To overcome this issue, we will create a custom list that tells Excel the sequence in which we want the data to appear. This involves copying the dates, pasting them in a new column, and converting them to values. Then, we use the ROW function to get sequential numbers and sort them in descending order. Next, we import this list into the Custom Lists section in Excel's Advanced Options. However, we must also uncheck the "Sort automatically" box and manually select our custom list as the first key sort order. While this solution may work, it is not ideal for those who receive new data every week. In that case, the custom list would need to be constantly updated, which can be frustrating. So, if you know of an easier way to achieve this task, please let me know in the YouTube comments. Thank you for tuning in to this episode of the MrExcel podcast, and I hope to see you again for more Excel tips and tricks. 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) Grouping Pivot Table by Week (00:37) Adjusting Start Date and Grouping Options (00:57) Sorting Challenge (01:09) Sorting Solution (01:27) Ugly Solution (02:05) Creating Custom List (02:23) Sorting Pivot Table (02:58) Updating Custom List (03:09) 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 #pivottable #pivot_table #excelpivot #excelpivottablestutorial #excelcustomlist This video answers these common search terms: how to group weeks in excel pivot how to group dates into weeks excel pivot table how to sort by week in excel how to sort by week in excel pivot table After grouping a pivot table by week, you want to show the weeks with the most recent at the top. This does not work because the grouped weeks are text and September (9) sorts before December (12). Today, a workaround. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/learn-excel-pivot-group-by-week-descending-podcast-1939.1153763/ About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.
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