Let’s take a look. When you add a label field to a pivot table, the items in that field are sorted alphabetically. For example, if we add Product as a row label to this pivot table. We get sales by Product, with all products listed alphabetically. If we remove the Product field and add the City field, we see sales by City, with cities in alphabetical order. This sorting is automatic for column labels as well. If we move Cities to the Column Labels area, and add Products as a row label, we see that the pivot table shows both products and cities in alphabetical order. You can easily reverse the sorting order for either of the two fields by using the buttons on the Options tab of the PivotTables tools ribbon. To sort Products in reverse order, just select any item in the Products field, and click the Z-A button. To re-sort in alphabetical order, click the A-Z button. You can do the same thing with Cities. Just select any city, and sort. You can also sort row and column labels by more than one level. To illustrate, let’s remove the City field. Products are now sorted in simple alphabetical order. Now, let’s add the Category field above Products. Note the Category field is now dominant - products are listed first in alphabetical order by Category, then in alphabetical order by Product. If we select a category and sort, category labels are sorted but products inside the category stay in the same order. If we select a product and sort, the products are sorted, but the categories remain unchanged. Notice that after you’ve sorted a pivot table, the field filter drop-down displays a special sorting icon. If you hover over this icon, Excel will show the current sort options. To access these options, either click the Sort button on the ribbon, or, open the drop-down and select More Sort Options. Also note that a pivot table remembers Sort Options for a given field, even when it’s been removed. If we remove Product, then add it back again, it’s still sorted using the options we set earlier.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
