Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to sort with custom list order in pivot table?

As we all known, Excel supports a feature to sort data by custom list as you need. And this feature is also applied to pivot table when you want to sort the data with custom list order. Here, I will introduce you that how to sort data by custom list in pivot table?

Sort data with custom list order in pivot table

arrow blue right bubble Sort data with custom list order in pivot table

Look at the following pivot table, now I want to sort the regions with this order: East, West, South then North as show as below screenshot. In pivot table, we can easily sort these regions alphabetically in ascending or descending order. And here, I can show you to sort these regions by custom lists you like.


1. Firstly, you should create a custom list by clicking File > Options, in the Excel Options dialog, click Advanced from the left pane, and then scroll down to click Edit Custom Lists button under General section, see screenshot:


Tip: If you are using Excel 2007, please click Office button > Excel Options, and then click Popular in the left pane, and click Edit Custom Lists button under Top options for working with Excel section, see screenshot:


2. Then in the popped out Custom Lists dialog, click NEW LIST from the Custom lists: box, and enter the custom list values that you need to sort with this order into the List entries: box, at last, click Add button to add the new lists to the Custom Lists box.


3. And then click OK > OK to close the dialogs. Now, go back to your pivot table, right click any cell in your pivot table, and choose PivotTable Options from the context menu, see screenshot:


4. In the PivotTable Options dialog box, go to click the Total & Filters tab, and then make sure to check Use Custom Lists when sorting option under Sorting section, see screenshot:


5. Then click OK to close the dialog, and now, you can sort the data with custom lists that you are created just now with following operations:

A: If the field is set for automatic sort, please right click any cell in your pivot table, and choose Refresh, and then the field will be sorted with your custom lists order;

B: If the field is currently set for manual sorting, you should right click a cell in the Region field of your pivot table, and click Sort > Sort A to Z.


Note: If you want to disable this custom list sort order in the pivot table, you just need to uncheck Use Custom Lists when sorting option in the PivotTable Options dialog box.

Related articles:

How to hide subtotals in pivot table?

How to show multiple grand totals in pivot table?

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rrradassse · 2 months ago
    je ne comprend pas ... cette fois-ci ca ne marche pas. Pourtant en copiant des cellules du meme type, la copie incrémente bien dans l'ordre de la liste prédéfinie; mais arrivée dans le TCD c'est le bordel ... une idée d'à quoi c'est du?
  • To post as a guest, your comment is unpublished.
    neeraj · 11 months ago
    thank you so much