Excel - Order of Pivot Table Items - Episode 1784
1,059 views · Published 5 September 2013 · 6:14 · Indexed 23 October 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Controlling the Order of Items in a Pivot Table. Welcome back to the MrExcel netcast! In this episode, we will be discussing a common question from our viewers: what controls the order of items in a pivot table? This question was brought to us by Justin on Twitter, and I believe I have the exact solution to his problem. So, let's dive right in. We have some store locations, dates, and sales data that we want to create a pivot table from. We go to INSERT, PIVOT TABLE, and select EXISTING WORKSHEET. We place the pivot table next to our original data and add dates to the columns, sales to the values, and location to the row labels. However, we notice that the items in the location column are not appearing in alphabetical order. It seems to be a random sequence of Orlando, Jacksonville, and Daytona. What's going on? Well, the last few items are in alphabetical order, but the others are not. This even happens if we use the location as a report filter or insert a slicer for location. This is unusual, as pivot tables usually sort items alphabetically. But fear not, I will show you how to fix this issue and even control the order of items in your pivot table. So, what's causing this strange sequence? It turns out that during a seminar in Orlando, I asked attendees to give me a list of names and showed them how to create a custom list. This list included Orlando, Jacksonville, Miami, Tampa, Gainesville, Daytona, Cocoa Beach, and Apopka. Although this list is completely unrelated to our pivot table, because it shares a subset of items with a custom list, it is appearing in that order. This is why we see Orlando, Jacksonville, and then Daytona and Cocoa Beach, followed by the remaining items in alphabetical order. To fix this issue, we need to go to FILE, EXCEL OPTIONS, ADVANCED, and scroll down to EDIT CUSTOM LISTS. Here, we can see the custom list that we created during the seminar. If we no longer need this list, we can simply delete it. However, if we need to keep this list for other purposes, we can import it as a new list and then delete the original list. This will ensure that our pivot table items appear in alphabetical order. But what if we want to control the order of items in our pivot table? Well, we can do that too! We can import our original data as a new list and then choose that list as the custom list for our pivot table. This way, we can have our items appear in the exact order that we want. Just remember, this method only works for regular Excel pivot tables, not OLAP or Power Pivot pivot tables. Another solution is to turn our original data into a table and run it through Power Pivot. This will automatically sort the items alphabetically. This may seem like an annoying feature in Power Pivot, but it can actually work to our advantage in this situation. So, there you have it, a few different ways to control the order of items in your pivot table. I hope this helps you, Justin, and anyone else who may encounter this issue. Thanks for tuning in to the MrExcel netcast, and we'll see you next time for another helpful tip. 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) Introduction and Sponsorship (00:11) Question from Justin on Twitter (00:21) Creating a Pivot Table (00:33) Issue with Item Order in Pivot Table (00:49) Explanation of Strange Item Order (01:24) Solution for Fixing Item Order (02:45) Alternative Solution for Keeping Custom List (04:47) Another Alternative Solution (05:31) Reminder and Additional Information (06:04) 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: Alphabetical order in pivot table Creating a pivot table Custom lists in Excel Editing custom lists in Excel Importing custom lists in Excel MrExcel podcast Pivot table order Removing duplicate data in Excel Sorting in Power Pivot Sorting items in a pivot table Sorting options in pivot table Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153605/ Justin on Twitter asked what determines the order of the drop down for a selection list in a pivot table. In Episode 1784, Bill shows a pivot table that ends up in a seemingly random sequence - three large cities at the top, followed by alpha after that. This episode is full of accidental discoveries - things that can help Justin solve the problem, but also plenty of useful tricks that can let you control the order of items in a pivot table.
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