Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order or compile a list of product inventory levels from highest to lowest. Sorting data enables you to quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

Note To find the top or bottom values in a set of data, such as top 10 grades or bottom 5 sales amounts, use the Top 10 Filter dialog box or conditional formatting. For more information, see and .

What do you want to do?

Learn more about sorting

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest). When sorting text, in some cases, data might have leading spaces inserted before data. For best results, remove the leading spaces before you sort text data. Unlike sorting a cell range or Excel table, you cannot do a case-sensitive sort of text, or sort by format, whether by cell color, font color, or icon set.

Sort orders vary by locale setting. Make sure 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.

Note In a field that is organized in levels, you can sort all of the items for a lower level together by collapsing the upper levels before you sort. For more information, see .

Sort data in column or row labels

  1. Select a column or row field in a PivotTable report or PivotChart report.

    In a PivotTable report, click a column or row label.

    In a PivotChart report, click the field in the associated PivotTable report . To sort items in a series field, click the corresponding column label. To sort items in a category field, click the corresponding row label.

  2. On the Options tab, in the Sort group, do one of the following:

    Text

    • To sort in ascending alphanumeric order, click Sort A to Z.
    • To sort in descending alphanumeric order, click Sort Z to A.

    Numbers

    • To sort in ascending alphanumeric order, click Sort Smallest to Largest.
    • To sort in descending alphanumeric order, click Sort Largest to Smallest.

    Dates and times

    • To sort in ascending alphanumeric order, click Sort Oldest to Newest.
    • To sort in descending alphanumeric order, click Sort Newest to Oldest.
  3. Optionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort.

    In the Sort <Field name> dialog box, select the type of sort that you want by doing one of the following:

    • To return items to their original order, click Data source order. This option is only available for OLAP source data.
    • To drag and arrange items the way that you want, click Manual.
    • To select a field to sort by in ascending sort order, select Ascending (A to Z), and then select the field from the drop-down list.
    • To select a field to sort by in descending sort order, select Descending (A to Z), and then select the field from the drop-down list.

    Tip Read the Summary section at the bottom of the dialog box to verify your choices.

  4. Optionally, to continue customizing the sort operation, click More Options.

    In the More Sort Options dialog box, do one or more of the following:

    • To enable or disable the sort operation each time that the PivotTable report is updated, under the AutoSort section, select or clear the Sort automatically every time the report is updated check box.
    • To sort in a user-defined sort order by using a custom list, under the First key sort order section, select the custom list from the drop-down list. This option is only available if you have not selected the check box under the AutoSort section.

      Microsoft Office Excel provides built-in, day-of-the-week, and month-of-the year custom lists, and you can also create your own custom list. For more information, see .

      Notes:

      • To enable or disable custom lists, set or clear the Use Custom Lists when sorting check box on the Filters & Totals tab in the PivotTable Options dialog box. Clearing this check box may also improve performance when you sort large amounts of data. For more information, see .
      • The custom list sort order is not retained if you refresh the report.
    • To change the type of sort, do one of the following:
      • To sort by grand total values, click Grand Total.
      • To change the row or column, select Values in selected row or Values in selected column, and then enter a reference to a cell in the row or column with the values to sort by in the box.

        Alternatively, to select a row or column, click Collapse Dialog button image to temporarily hide the dialog box, select the row or column, and then press Expand Dialog button image.

    • Tip Read the Summary section at the bottom of the dialog box to verify your choices.

Sort data in the values area

  1. Select a value field in a PivotTable report.
  2. On the Options tab, in the Sort group, do one of the following:
    • To sort in ascending alphanumeric order, click Sort Smallest to Largest.
    • To sort in descending alphanumeric order, click Sort Largest to Smallest.
  3. Optionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort.

    In the Sort By Value dialog box, do one or more of the following:

    • Under Sort options, do one of the following:
      • To sort numbers in ascending sort order, select Smallest to Largest.
      • To sort numbers in descending sort order, select Largest to Smallest.
    • Under Sort direction, do one of the following:
      • To sort values vertically, select Top to bottom.
      • To sort values horizontally, select Left to right.

    • Tip Read the Summary section at the bottom of the dialog box to verify your choices.

See also: