---

Excel Pivot Tables :: Sort Data in a Pivot Table

---

Excel Pivot Tables :: Sort Data in a Pivot Table | Overview

Overview

It is possible to sort various sections of an Excel Pivot Table.

There are several ways to sort depending upon how you want to sort and the type of data you are sorting.

For example: If you have a list of Months in your Pivot Table, usually you would want to sort them in a sequential Month order rather than in Alphabetical order.

You can also choose to change Excel Pivot Table options so that it orders automatically in a specific way regardless of how you re-arrange your Excel Pivot Table.

---

Sort Automatically :: Auto Sort

Sort Automatically: Auto-Sort Options – Automatically sorts the values either ascending or descending based upon the Pivot Table field selected.

Top 10 AutoShow:

Sort Manually:

Ascending: Sorts the field in ascending order when the Ascending option is selected.

Descending: Sorts the field in descending order when the Ascending option is selected.

Move Manually: You can manually move fields to specify your own order.

Microsoft Excel uses the following order for an ascending sort , descending order being the reverse:

(n.b. Blank cells are always sorted last..)

1. Numbers
2. Text
3. Logical Values
4. Error values such as #REF! and #VALUE!
5. Blank Cells

Example 1:

1. Click the field with the items you want to sort.

In this example we want to sort the Year

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

2. Do one of the following

Sort the items ascending or descending

1. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
2. Under AutoSort options, click Ascending or Descending.
3. In the Using field list, do one of the following:

To sort the items by their labels, click the same field that you're sorting.

To sort the items by their values in the data area, click the data field that supplies the values you want to sort on.

Note: When you refresh the report or change its layout, Excel resorts the field in the order you specified.

Sort the items by a particular data value

1. Click the cell in the data area that contains the value you want to sort by. For example, if you want to sort your products by a particular month's sales value, click the value for that month in the data area.
2. On the Data menu, click Sort.
3. Select any options you want, and then click OK.

Note: You will need to repeat the sort if you refresh or update the report.

Sort the items in a custom order

The custom order is not retained if you refresh the report.

1. On the Data menu, click Sort.
2. Click 'Options'
3. In the First key sort order box, click an option.

If you don't see the option you want, you can create your own custom sort order by clicking Options on the Tools menu and using the Custom Lists tab.

Note: You can organize items in a custom order manually by selecting and dragging them.

Stop sorting or return the items to their original order

1. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
2. Under AutoSort options, click Manual to stop automatic sorting or Data source order to return the items to their original order.

Note: After you sort a PivotChart report or its associated PivotTable report, some chart formatting may be lost.

---

>>>Next Section :: Autosort your Pivot Table >>>

---