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
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
-
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