Excel for Mac: PivotTable Sorting

This week in our series about Microsoft Excel for Mac, we cover PivotTable sorting, showing a few differences between Mac and Windows.

If you use Excel for Mac, there are some differences you need to be aware of when it comes to sorting PivotTables.  If you want to sort by the first column (the column where the filter button appears), then Mac and Windows are essentially the same.  For example, in the PivotTable below, the sort/filter button is in the Item column, so the sort options in the dialog affect that column.  If you sort Ascending, it will sort by the Item name.  There's no difference between Mac and Windows.

Excel for Windows is shown on the left, Excel for Mac is shown on the right.

Where they differ is when you want to sort by one of the other columns.  On Windows, you need to choose More Sort Options…, which opens a secondary dialog, shown below.

In this dialog, you can choose Manual, Ascending or Descending and then pick which of the other columns you want to sort by.

On Windows, there are even More Options… (notice the button). Click this, and you have even greater control over how your PivotTable gets sorted.  Below is a screenshot of the 'More Sort Options' dialog on Windows, showing that you can sort automatically every time the report is refreshed, and you can choose whether to sort by a 'Grand Total' or another values column.  The current sorting choice is shown in the summary section of the dialog.  In our example, we're sorting the Item column by the Amt Invoiced values that appear in the 2020 column.

The sort and filter dialog on Mac does not have a secondary dialog.  There are no more options.  All the options are in the first dialog.  To sort by one of the other fields in your PivotTable, you can choose that field in the "Sort By" drop-down, as shown below. We've chosen to sort by Amt Invoiced.

There are a few options missing from Mac.  In our example, sorting by Amt Invoiced means that you're sorting by the values in the Grand Total column.  If you wanted to sort by one of the Years in the table shown, there's no way to do that.  You also can't disable the option to 'Sort automatically every time the report is updated', nor can you pick a 'Custom List' for your first key sort order, as you can on Windows.

We hope you find this topic helpful. Check back for more details about Excel for Mac and how it’s different than Excel for Windows.