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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
You can configure Excel so that it displays special text within blank PivotTable cells. Here's how to make the changes ...
Discover MoreIf you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...
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