Sort calculated field in a pivot table - an example

[DataFreakFromUtah]

No question here, just a small procedure for the archive.

Sort a calculated field in a Pivot Table.
Sort calculated values in a pivottable.
Sort by ascending or descending items in a pivot table

Goal:
You want to sort the calculated items of a particular field in a pivot
table.
Note, we talking about sorting a field in the
data section (not the row or column section) found in a pivot table's
layout.

Solution:
Select the calculated field and select a value within the calculated
field.
The active cell must be on a true value (the body of the pivot table),
it cannot be on the column heading
of the calculated values.

Go to Menu>Data>Sort. You will have the option to sort by ascending or
descending for the values
in the field you have selected.

Below is some code that will perform a descending sort on a calculated
field
whose first value resides in cell R3C2

Sub SortCalculatedFieldInPT()

Selection.Sort Key1:="R3C2", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=7, Orientation:=xlTopToBottom

End Sub

 

[DataFreakFromUtah]

Here's another small VBA procedure example that also allows
you to sort a calculated field in a pivot table:



Sub SortACalculatedFieldInPT()
'In this example the pivot table is named: PivotTable1
'The target field (a row field) is named: DEPT
'The calculated field is named: Sum of Revenue


With ActiveSheet.PivotTables("PivotTable1").PivotFields("DEPT")
' note: xlDescending can be changed to xlAscending per preference
.AutoSort xlDescending, "Sum of Revenue"
End With
End Sub