Written by Allen Wyatt (last updated May 7, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
When Katara creates a PivotTable, Excel automatically sorts the data that appears in the PivotTable. She would rather not have the data sorted at all—it is just fine in the data range on which she's basing the PivotTable. Katara wonders if there is any way to turn off the automatic sorting.
Automatic sorting can be turned off by taking advantage of a setting that is (honestly) not that easy to find. Follow these steps:
Figure 1. The More Sort Options dialog box.
If you would like to explore additional PivotTable sorting capabilities, you might find this web page, by Debra Dalgleish, to be helpful:
https://www.contextures.com/excel-pivot-table-sorting.html
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12880) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is ...
Discover MoreAre you attached to the classic PivotTable layout? Looking for a way to make that layout the default for new PivotTables? ...
Discover MoreWhen you want to include specific records from a source table into a PivotTable, you need to employ some sort of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-06-18 14:18:04
ProblemChild
I have many pivot tables in a sheet. How to disable auto-sort option for columns for all the pivot tables using VBA. I don't want the columns to auto-sort and move left or right from the order in which I've set it up.
(see Figure 1 below)
Figure 1.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments