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.
-
In a PivotTable, select the small arrow next to Row Labels and Column Labels cells.
-
Select a field in the row or column you want to sort.
-
Select the arrow
-
To sort data in ascending or descending order, select Sort A to Z or Sort Z to A.
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.
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.
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:
-
Select a field in the row or column you want to sort.
-
Select the arrow
-
In the Sort dialog box, pick the type of sort you want:
-
Select 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.
-
Select Ascending (A to Z) by or Descending (A to Z) by and then choose the field you want to sort.
-
For additional options, select More Options, and then pick the option you want in the 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, select Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.
Tip: Select 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.
-
Select a field in the row or column you want to sort.
-
Select the arrow
-
To sort data in ascending or descending order, select Sort Ascending or Sort Descending.
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.
-
You can also right-click row or column labels, and choose any of the Move options:
Note: You can't drag items that are shown in the Values area of the PivotTable Field List.
-
Select the arrow
-
In the Sort by Value box, in Select value, choose a value to sort by.
-
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.
-
In a PivotTable, select the small arrow next to Row Labels and Column Labels cells.
-
Select a field in the row or column you want to sort.
-
Select the arrow
-
To sort data in ascending or descending order, select Sort A to Z or Sort Z to A.
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.
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.
Tip: To quickly find what you need, you can group, filter, or apply conditional formatting to the PivotTable or PivotChart.
Important: 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.
-
In a PivotTable, tap the small arrow next to Row Labels and Column Labels cells.
-
Select a field you want to sort, and then tap the sort option you want.
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.
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.
