Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Sorting data is helpful when you have large amounts of data in a PivotTable or PivotChart. You can sort in alphabetical order, from highest to lowest values, or from lowest to highest values. Sorting is one way of organizing your data so it’s easier to find specific items that need more scrutiny.

Here are a few things to remember about sorting data:

  • Sort orders vary by locale setting. Ensure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.

  • You can’t sort case-sensitive text entries.

  • You can’t sort data by a specific format, such as cell color or font color. Nor can you sort by conditional formatting indicators, such as icon sets.

  1. In a PivotTable, click the small arrow next to Row Labels and Column Labels cells.

  2. Click a field in the row or column you want to sort.

  3. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click the sort option you want.

    Row Label filter

  4. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

    Sort buttons

Text entries will sort in alphabetical order, numbers will sort from smallest to largest (or vice versa), and dates or times will sort from oldest to newest (or vice versa).

You can sort on individual values or on subtotals by right-clicking a cell, choosing Sort, and then choosing a sort method. The sort order applies to all the cells at the same level in the column that contains the cell.

In the example shown below, the data under the category level (Helmet, Travel bag) has an alphabetical sort from A to Z.

Default sort on Row Labels

To see a sort of the grand totals for products—from largest to smallest—choose any number in the Grand Total column, and sort on it.

Largest to smallest sort on Grand Total column values

Tip: To quickly find what you need, you can group, filter, or apply conditional formatting to the PivotTable or PivotChart.

To sort specific items manually or change the sort order, you can set your own sort options:

  1. Click a field in the row or column you want to sort.

  2. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click More Sort Options.

    Row Label filter

  3. In the Sort dialog box, pick the type of sort you want:

    Sort dialog box

  • Click Manual to rearrange items by dragging them. Hover the cursor over the item's border until you see the four-pointed arrow, then drag.

  • You can't drag items that are shown in the Values area of the PivotTable Field List.

  • Click Ascending (A to Z) by or Descending (A to Z) by, and then choose the field you want to sort.

  • For additional options, click More Options, and then pick the option you want in the More Sort Options dialog box:

    More Sort Options dialog box

  • In AutoSort, check or uncheck the box for Sort automatically every time the report is update— either to permit or stop automatic sorting whenever the PivotTable data updates.

  • Under First key sort order, choose the custom order you want to use. This option is available only when there is no check in the box for Sort automatically every time the report is updated.

    Excel has day-of-the-week and month-of-the year custom lists, but you can also create your own custom list for sorting.
     

    Note: A custom list sort order is not retained when you update (refresh) data in your PivotTable.

  • In Sort By, click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

    Tip: Click Data source order to return items to their original order. This option is available for Online Analytical Processing (OLAP) source data only.

Here are a few things to remember about sorting data:

  • Sort orders vary by locale setting. Ensure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.

  • You can’t sort case-sensitive text entries.

  • You can’t sort data by a specific format, such as cell color or font color. Nor can you sort by conditional formatting indicators, such as icon sets.

  1. Click a field in the row or column you want to sort.

  2. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click the sort option you want.

    Row Label filter

    If you click the Column Labels arrow, choose the field you want to sort first, and then the sort option you want.

  3. To sort data in ascending or descending order, click Sort Ascending or Sort Descending.

    Ascending and Descending sort options

Text entries will sort in alphabetical order, numbers will sort from smallest to largest (or vice versa), and dates or times will sort from oldest to newest (or vice versa).

In addition to sorting ascending or descending, you can manually sort each of the row or column labels. 

  • Hover the cursor over the item's border until you see the move cursor, then drag.

Manual Sort

  • You can also right-click row or column labels, and choose any of the Move options:

  Manual Sort - Context Menu

Note: You can't drag items that are shown in the Values area of the PivotTable Field List. 

  1. Click the arrow Filter drop-down arrow on Row Labels, and pick Sort by Value.

    Row Label filter

    If you click the Column Labels arrow, choose the field you want to sort first, followed by the sort option you want.

  2. In the Sort by Value box, in Select value, choose a value to sort by.

    Sort By Value box

  3. In Sort options, pick the sort order you want.

Here are a few things to remember about sorting data:

  • Sort orders vary by locale setting. Ensure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.

  • You can’t sort case-sensitive text entries.

  • You can’t sort data by a specific format, such as cell color or font color. Nor can you sort by conditional formatting indicators, such as icon sets.

  1. In a PivotTable, click the small arrow next to Row Labels and Column Labels cells.

  2. Click a field in the row or column you want to sort.

  3. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click the sort option you want.

    Row Label filter

  4. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

    Sort buttons

Text entries will sort in alphabetical order, numbers will sort from smallest to largest (or vice versa), and dates or times will sort from oldest to newest (or vice versa).

You can sort on individual values or on subtotals by right-clicking a cell, choosing Sort, and then choosing a sort method. The sort order applies to all the cells at the same level in the column that contains the cell.

In the example shown below, the data under the category level (Helmet, Travel bag) has an alphabetical sort from A to Z.

Default sort on Row Labels

To see a sort of the grand totals for products—from largest to smallest—choose any number in the Grand Total column, and sort on it.

Largest to smallest sort on Grand Total column values

Tip: To quickly find what you need, you can group, filter, or apply conditional formatting to the PivotTable or PivotChart.

PivotTable on iPad is available to customers running Excel on iPad version 2.82.205.0 and above. To access this feature, please ensure your app is updated to the latest version through the App Store.

Here are a few things to remember about sorting data: 

  • Sort orders vary by locale setting. Ensure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.

  • You can’t sort case-sensitive text entries.

  • You can’t sort data by a specific format, such as cell color or font color. Nor can you sort by conditional formatting indicators, such as icon sets.

1.   In a PivotTable, tap the small arrow next to Row Labels and Column Labels cells. 

PivotTable sort arrow on row labels

2.   Select a field you want to sort, and then tap the sort option you want. 

PivotTable sort filter on iPad

Tip: Scroll up to see the full list of  sorting options and fields available. 

Text entries will sort in alphabetical order, numbers will sort from smallest to largest (or vice versa), and dates or times will sort from oldest to newest (or vice versa). 

You can sort on individual values or on subtotals by pressing and holding a cell, and then choosing a sort method. The sort order applies to all the cells at the same level in the column that contains the cell. 

In the example shown below, the data under the category level (Accessories, Bikes) has an alphabetical descending Sort. 

PivotTable sorting levels on iPad

To see a sort of thegrand totals for products—from largest to smallest—choose any number in the Grand Total column, and sort on it. 

Sort PivotTable Grand Totals on iPad.

See Also

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×