Written by Allen Wyatt (last updated May 7, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If 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 create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is ...
Discover MoreOne of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...
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 © 2024 Sharon Parq Associates, Inc.
Comments