To sort a PivotTable with VBA, use the target row field’s AutoSort method: for example, set its order to xlDescending and name the value field to sort by. In Excel for Microsoft 365 on Windows, a typical line is pt.PivotFields("Region").AutoSort Order:=xlDescending, Field:=pt.DataFields(1).Name; the PivotTable, row field, and data field must exist and use the captions in your workbook.
What a PivotTable sort changes
A PivotTable sort changes the displayed order of its items; it does not rearrange or edit the source records. This distinction matters because a refresh can recalculate the summary and reorder items according to the PivotTable’s sort settings, while the underlying source data remains as it was.
There are two common goals. You might sort row or column labels alphabetically, such as putting regions in A-to-Z order, or sort those labels by a summarized value, such as ranking regions by total sales. A third goal is a deliberate custom sequence, such as placing “High,” “Medium,” and “Low” in that order rather than alphabetically. Each calls for a different approach.
The examples below use a conventional PivotTable with “Region” in the Rows area and a measure such as “Sum of Sales” in Values. Names such as “Region,” “Report,” and “PivotTable1” are examples, not built-in names. Replace them with the actual worksheet, PivotTable, field, and caption names in your file.
Check the PivotTable and field names first
VBA refers to PivotTables and fields by their names or captions. A worksheet tab named “Report” does not guarantee that its PivotTable is named “PivotTable1,” and a source column called “Sales” may appear in the Values area as “Sum of Sales” or a custom caption such as “Revenue.” A mismatch is a frequent cause of “subscript out of range” errors or a sort that uses the wrong measure.
- Click inside the PivotTable and use PivotTable Analyze > PivotTable Name to check its name.
- Inspect the PivotTable Fields pane to confirm which field is in Rows and which measure is in Values.
- Check the displayed caption of the value field; use that caption when naming a specific measure in code.
- Confirm that the field has items to sort and that the PivotTable is not protected in a way that blocks changes.
For a macro, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the code into the module. On a Mac, Option+F11 commonly opens the editor; depending on the keyboard, you may also need Fn. Excel for the web can display workbooks containing macros, but it cannot create, edit, or run VBA macros. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sort row labels by a value field
To rank regions by their total in descending order, call AutoSort on the row field and identify the value field used for ranking. This example uses the first data field in the PivotTable, so it does not depend on the caption being exactly “Sum of Sales.” If the first Values field is not the one you want, select the appropriate index or use its exact caption instead.
Sub SortRegionsByFirstValueDescending()
' Sort Region items by the first Values field, largest to smallest.
Dim ws As Worksheet
Dim pt As PivotTable
Dim regionField As PivotField
Dim sortBy As String
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("PivotTable1")
Set regionField = pt.PivotFields("Region")
sortBy = pt.DataFields(1).Name
regionField.AutoSort Order:=xlDescending, Field:=sortBy
End Sub
Use xlAscending instead of xlDescending to put the smallest summarized value first. If the PivotTable has multiple Values fields, pt.DataFields(1).Name refers to the first one in its Values collection, not necessarily the measure you intended. You can inspect the DataFields collection or specify a known caption, for example Field:="Sum of Sales", after confirming that this is the caption shown in the PivotTable.
This is a value sort: the regions are ranked according to the measure, not according to the spelling of their names. If two regions have equal totals, their relative order may not be a meaningful ranking; do not assume this one-field sort supplies a secondary tie-breaker.
Sort labels alphabetically
For A-to-Z sorting by the row labels themselves, use the row field as the sort field. The following example sorts “Region” labels in ascending order. Change xlAscending to xlDescending for Z-to-A order.
Sub SortRegionsAlphabetically()
' Sort the Region labels rather than a summarized value.
Dim ws As Worksheet
Dim pt As PivotTable
Dim regionField As PivotField
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("PivotTable1")
Set regionField = pt.PivotFields("Region")
regionField.AutoSort Order:=xlAscending, Field:=regionField.Name
End Sub
Alphabetical order can still look unexpected when the field contains numbers stored as text, leading or trailing spaces, or inconsistent labels. For example, text labels “1,” “10,” and “2” may not appear in the numeric sequence a reader expects. Clean or standardize the source values when the ordering should represent numbers or dates, then refresh the PivotTable.
Set a custom item order
Use manual item positioning when the required sequence is neither alphabetical nor a ranking by values. For example, a status field might need “High,” “Medium,” and “Low” order. Set the field to manual sorting, then assign positions to its PivotItems. The item captions must match the PivotTable items exactly, and all named items must be present.
Sub SetPriorityOrder()
' Apply a custom order to the Priority row field.
Dim ws As Worksheet
Dim pt As PivotTable
Dim priorityField As PivotField
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("PivotTable1")
Set priorityField = pt.PivotFields("Priority")
priorityField.AutoSort Order:=xlManual, Field:=priorityField.Name
priorityField.PivotItems("High").Position = 1
priorityField.PivotItems("Medium").Position = 2
priorityField.PivotItems("Low").Position = 3
End Sub
This example assumes those three items exist and that the field is laid out in a way that permits item positioning. A missing item, a spelling difference, or a PivotTable based on an OLAP source can prevent this pattern from working as expected. If there are other items, they remain in the field; the three assignments place the named items at the specified positions, but the final sequence should be checked in the actual PivotTable.
When to refresh and where the sort applies
A sort does not automatically mean that the PivotTable needs a refresh. If you only change the display order of the current items, call AutoSort after the PivotTable is available and its fields have been set up. If the source data has changed, refresh first so the PivotTable includes the latest records and items, then apply the desired sort.
For example, the following refresh line can be placed before the sorting line in a macro when the source data is already updated:
pt.RefreshTable
Refreshing is not a substitute for updating a source range that excludes newly added records. If the PivotTable uses a fixed source range, new rows outside that range may not appear until the source is expanded or converted to an Excel table and the PivotTable source is adjusted. Also consider whether the sort field is in the outer or inner position of a multi-level Rows area. Sorting one field does not necessarily produce one global ranking across every nested group; the sort applies within the structure of that PivotTable.
Common VBA sorting problems
- Wrong PivotTable name: The visible worksheet name and the PivotTable object name are separate. Verify both before running the macro.
- Wrong sort-field caption: Value fields often have captions such as “Sum of Sales.” If the caption differs, use the actual DataField name or the correct collection item.
- Sorting the wrong direction:
xlAscendingmeans low-to-high for values and A-to-Z for labels;xlDescendingreverses those orders. - Field is not in the layout: A field may exist in the source but not be available in the expected PivotTable context. Confirm that it is placed in Rows or Columns as needed.
- Source has not been refreshed: Newly added categories will not necessarily be available as PivotItems until the source range includes them and the PivotTable is refreshed.
- Manual order is blocked or inconsistent: OLAP-based PivotTables and certain field arrangements can behave differently from ordinary PivotTables. Test on a copy and verify the resulting layout.
When debugging, run a small macro against one known PivotTable and one known field before adding loops or applying the sort to many sheets. If the code stops on a line, check the exact object name and caption on that line first. Avoid suppressing every error with On Error Resume Next; it can hide a failed sort and make the workbook appear successful when nothing changed.
Choosing between VBA and other sorting methods
For a one-time sort, the PivotTable interface is usually quicker: use the field’s sort options or right-click an item and choose the relevant Sort option. The exact choices depend on the selected cell and PivotTable layout. VBA is more useful when the sort must run repeatedly, follow a refresh, or be applied consistently to a set of reports.
Excel’s built-in custom lists can help with recurring sequences such as weekdays or months, but they are not a universal replacement for assigning item positions. A worksheet formula such as SORT sorts a range of cells; it does not directly rearrange the items inside a PivotTable. For a reusable PivotTable process, keep the source data, field names, refresh step, and sort logic explicit so another user can understand what the macro changes.
Excel for Windows, Mac, and the web
The VBA object model examples here target Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, although the Visual Basic Editor shortcut and some editor details can differ; use the Developer tab or the Mac shortcut if available. The same PivotTable sorting approach is generally applicable, but workbook connections and data sources may have platform-specific limitations.
Excel for the web does not run VBA. To sort a PivotTable there, use the available PivotTable interface, or open the workbook in the desktop application to run the macro. If a workbook is shared with people who use the web version, provide a manual sorting option or explain that the macro requires desktop Excel.
Frequently Asked Questions
How do I sort a PivotTable from largest to smallest with VBA?
Call AutoSort on the row field with xlDescending and the name of the value field used for ranking. Confirm that the field caption identifies the intended measure.
How do I sort PivotTable row labels A to Z?
Call AutoSort on the row field with xlAscending and that row field’s name as the sort field. This sorts labels, not totals.
Can VBA sort a PivotTable after every refresh?
Yes. Put the refresh and sorting statements in the same macro, refreshing before sorting when the source data changed. For automatic sorting after refresh events, use an appropriate PivotTable event procedure and test it carefully; event code is more complex than a one-time macro.
Why does AutoSort say the item or field cannot be found?
Usually the PivotTable name, field name, item caption, or value-field caption does not exactly match the workbook. Check the names in Excel and whether the field is actually in the PivotTable layout.
Can I sort PivotTable items in a custom order?
Yes, for supported PivotTables, set the field to manual sorting and assign each item’s Position. The items must exist and the PivotTable’s source type and layout must support manual positioning.
Can I run a VBA PivotTable sort in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use the macro, or sort the PivotTable through the web interface.
Related
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Simple Macro Recorder and Auto Clicker: How They WorkA simple macro recorder captures a sequence of actions—such as clicks, keystrokes, and pauses—and plays them back; an…
- Loop Through a Named Range in VBA: Practical ExamplesTo loop through every cell in an Excel named range in VBA, assign the range to a Range…
- VBA AutoFilter Field 1: What It MeansIn Excel VBA, Field:=1 tells AutoFilter to filter the first column in the filter range—not necessarily worksheet column…
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- Updating Items in a VBA Dictionary: Examples and TipsTo update a value in a VBA Scripting.Dictionary, assign a new value to its existing key: dict("name") =…
- Macro Recorder Best Options for Excel and Windows PCsThe best macro recorder depends on what you want to automate: for repeated tasks inside Excel, start with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.