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

Watch on YouTube

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