Sort data in a PivotTable or a PivotChart report

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 Filter data in a PivotTable or PivotChart report and Add, change, or clear conditional formats.

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 Expand or collapse details in a PivotTable or PivotChart report.

Top of Page Top of Page

Sort data in labels

  1. Do one of the following:
    • In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels.
    • In the PivotChart Filter Pane, click the arrow Filter drop-down arrow on Axis Fields (Categories) or Legend Fields (Series).
  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.
  1. 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) by, 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) by, 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.

  1. 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 Sort data in a range or table.

 Notes 

  • 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.

Top of Page Top of Page

Sort data in the values area

  1. Select a value field in a PivotTable report or associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) of a PivotChart 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.

Top of Page Top of Page

 
 
Applies to:
Excel 2007