Excel - Pivot Table Data Crunching 10 - PowerPivot Sort - Episode 1211.310

2,736 views · Published 15 July 2010 · 2:13 · Indexed 22 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Pivot Table Data Crunching 10 - PowerPivot Sort.

Welcome to the MrExcel netcast. In this episode, we will be discussing Pivot Table Data Crunching Chapter 10 and specifically, Power Pivot Sorting. As I was writing my books on Excel 2010, this topic came up and it's one that really frustrates me. Let's dive in and see how we can make this process a little easier.

So, here's the issue. I have a Pivot Table based on Power Pivot and when I add in the month field, the months show up alphabetically. Now, every Pivot Table that we have created in the last 15 years based on Excel data respects the custom list for sorting. But for some reason, the Power Pivot people didn't include this feature and it takes 8 clicks to fix it. 8 clicks that I hate.

To fix this issue, we need to go to the row labels drop-down and click on "More Sort Options". Then, we need to select "Ascending" and choose to sort by month. But, we're not done yet. We have to click on "More Options" and uncheck "Sort Automatically". Then, we need to manually specify the first key sort order to be Jan, Feb, Mar, etc. That's 6 clicks so far. Then, we have to click "OK" twice to get the data to show up in the proper sequence. That's a total of 9 clicks just to sort our data. 

I'm not sure why the Power Pivot people didn't include this feature, maybe it's because the data overlaps. But those 9 clicks are just unnecessary and frustrating. So, there you have it, a simple solution to get your days of the week or months of the year sorted properly in your Power Pivot Pivot Table. Thanks for tuning in and we'll see you next time for another netcast from MrExcel. Don't forget to subscribe to our channel 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) Power Pivot Sorting
(00:21) Frustration with Power Pivot sorting
(00:32) Issue with sorting months in Power Pivot
(00:42) Difference between Power Pivot and regular Pivot Table sorting
(00:58) 9 clicks to fix sorting issue in Power Pivot
(01:38) Reason for Power Pivot sorting issue
(01:53) How to sort days of the week or months of the year in Power Pivot
(02:03) 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:
Ascending order
Custom list
Pivot Table
Pivot Table Data Crunching Chapter 10
Power pivot book
Power Pivot Sorting
Sort options
Sorting months


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152874/


Sort a pivot table field using a custom list. This requires too many steps in PowerPivot.

More from this channel