Look Who it Was Random video

Excel - Mastering Pivot Table Sorting: Control and Customize Your Data - Episode 1059

947 views · Published 20 July 2009 · 3:17 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Pivot Table Sorting: Control and Customize Your Data.

Welcome back to the MrExcel netcast! In today's video, we will be discussing a question that was sent in by Lisa Biel, who I had the pleasure of meeting at our Chicago boot camp. Lisa's question is about Pivot Tables and how to control the sorting of data within them. So, let's dive into some cool sorting techniques for Pivot Tables.

First, let's take a look at the sorting options for the rows and columns in a Pivot Table. As you may have noticed, the regions in our example are not in alphabetical order. This is because I have a custom list defined with a specific order. This is a great way to automatically control the sorting of data in your Pivot Table. However, when it comes to the products, they are appearing alphabetically and we want to change that. To do so, simply right-click on the product column, go to "sort", then "more sort options" and choose to sort in descending order based on a specific criteria, such as revenue.

But what about the sorting options for the filter area? This is where things can get frustrating. By default, the filter area does not allow us to control the sorting of data. However, there is a workaround for this. Simply move the field you want to sort back to the row label area, where we do have sorting options. Once you have sorted the data in the row label area, you can then move it back to the filter area and it will respect the sorting you have applied. This is a bit of a bizarre solution, but it does the trick!

Another cool feature is that you can even customize the order of items in the filter area. For example, if you have multiple items and you want to have the most common ones at the top, you can simply drag and drop them into the desired order in the row label area, then move them back to the filter area. This gives you more control over how your data appears in the filter area.

I want to thank Lisa for sending in her question and for attending our Chicago boot camp. I hope this video has helped you and others who may have had the same question. Thank you for watching and be sure to tune in for more netcasts from MrExcel. See you next time!

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
(00:18) Question about Pivot Tables from Lisa Biel
(00:28) Sorting options in Pivot Tables
(00:38) Controlling order with custom lists
(00:48) Sorting by revenue
(01:10) Frustration with filter area sorting
(01:25) Solution: moving field to row label area
(02:02) Customizing filter order
(02:45) Thanking Lisa for her question
(02:55) 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:
Controlling sort order in Pivot Tables
Custom list in Pivot Tables
Customizing appearance in filter area of Pivot Tables
Customizing appearance in report filter of Pivot Tables
Drag and drop in Pivot Tables
Moving fields in Pivot Tables
Pivot Tables
Sorting by revenue in Pivot Tables
Sorting in Pivot Tables
Sorting in report filter of Pivot Tables
Sorting in row label area of Pivot Tables
Sorting in the filter area of Pivot Tables

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



Lisa from Chicago sends in today's question. Why can't you sort the page field in a pivot table? Episode 1059 talks about several ways to sort the row fields in a pivot table and then a method for sorting the filter field.

This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!

More from this channel