Sunday, April 28, 2013

Microsoft Excel 2013 Sort Pivot Table By Calculated Field



Want your data to really pop?

Check out my latest project: Sizzle


drag-and-drop interactive data visualization




Sorting a Calculated field (or Value field) in Excel 2013 Pivot Tables may not be very clear.  Unlike the Row fields there is no option button in the column header allowing you to easily sort the column, but it is possible.

In the example below, I have a Pivot Table with "ListItemAbv" as the Row and "Count of ListItemAbv" as a calculated field (there is also a filter on "RankOrder", but that is not relevant to the example)



Notice that, by default, the Pivot Table is sorted by "ListItemAbv."  There is also an option button visible in the header column (A:3).  You can use that option button to easily sort the Pivot Table by "ListItemAbv."

However, there is no option button available to sort the Pivot Table by the Calculated field "Count of ListItemAbv".  To sort by that calculated field, select the entire column and use the "Sort & Filter" section of the Data tab in the Ribbon menu.  The row labels will follow their associated calculated values even though they are not currently selected.



Hope this helps,
Aaron

Post a Comment