6

I have a pivot table and I would like to sort some columns. But the "sort" function is not working at all. When I click "sort from A to Z" nothing happens. I am just able to sort the first column of the table, but not the others.

  • No hidden rows or columns
  • The data into the same column have the same type
  • No formulas at all
2
  • Can you provide some example data?
    – Burgi
    Aug 4, 2017 at 15:47
  • 2
    Mariangela...it's not a good look to ask a question and subsequently not to respond to comments or answers. Please reply to Burgi's comment or mark my answer as accepted if it helps, or otherwise leave a comment below my answer in the event that you still need further assistance. Aug 7, 2017 at 20:25

2 Answers 2

5

This question highlights one of the major differences between say a Table and a PivotTable. PivotTables work by grouping data together into categories, and then doing some kind of arithmetic on the data associated with each category. Whereas Tables simply serve up the underlying data for each line. And this explains the difference that you're observing when trying to filter anything but the first column of a PivotTable:

  • Because Tables don’t need to do any kind of grouping, you can sort them alphabetically by any column you want.
  • But PivotTables are based on the underlying relationships in your data, with the display hierarchy being set by the order in which your fields appear in the PivotTable itself. And that order determines just how much you can sort any one field by. Because unlike Table columns–which can be sorted by an entire column–PivotFields get sorted within the confines of the current hierarchy level.

Hard to explain in words, so lets look at a classic example: US States and Cities.

If we were to view a list of say US cities and their associated populations in PivotTable, then by default Excel would list those cities in strict alphabetical order as shown below left. If we then added a State field to the left of that City field, then the PivotTable would say…

“Okay, I’ve added the State field, and because it’s to the left of that Cities field, I’m guessing that now you want these cities population figures broken down by State. Okay, that’s cool…I’ll group them by State. But I’ll still list them alphabetically within those state grouping, to make it easy for you to find any particular city in any particular state”.

enter image description here

As we can see, while the original City layout went from A to Z, now the cities are ordered from A to W (Anchorage to Wasilla) for the state of Alaska (AK), and then start over at A again (Adamsville) for Alabama (AL).

Now, if this was a mere Table we were dealing with–and if you wanted that strict alphabetical city layout back again–you could simply click on that triangle filter icon by the City header, sort A to Z, and have those cities back in the original order again. But try it, and as shown below left, all that would happen is that the triange filter itew would display an arrow to tell you the field is now sorted alphabetically, but the actual sort order wouldn’t change one bit from what we had before. And a good thing too, because otherwise that would break the PivotTable paradigm of always displaying items sorted from the innermost (left) field to the outermost (right). In fact, the only way to sort those population figures by city is to make the City field the right-most field in the PivotTable, as shown below right.

enter image description here

Given this, then what good is that triangle filter icon, if it doesn’t change anything? Well, it does change the sort order, but only within the groupings defined by the fields to the left. To illustrate, if you sort City by Z to A, you get the following:

enter image description here

1

I found an alternative solution here: Sorting problem.
Under "pivot table options" -> "totals & filters" un-check the box for using custom filter.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .