Auto Sort an Excel Pivot Table Report |
Excel Pivot Table Report :: Autosort |
- Autosort
- Manually Sort
- Custom Order Sort
- Stop sorting or return the items to their original order (Manually Sort)
- Sort Top 10 or Top (n)
- Sort Bottom 10 or Bottom (n)
As mentioned in the overview there are several different ways to sort.
In this section we will be using the Autosort option to sort an Excel Pivot Table. The Autosort option will sort values in an Excel Pivot Table automatically based upon a selected field. Even when an Excel Pivot Table has been updated the specified sort order will remain.
In this example we will look at the Autosort option and how it works when we have rearranged an Excel Pivot Table.
Microsoft Excel uses the following order for an ascending sort, descending order being the reverse:
(n.b. Blank cells are always sorted last..)
| Sort Ascending | Sort Descending |
| Numbers | Error Values (Such as #REF! and #VALUE!) |
| Text | Logical Values |
| Logical Values | Text |
| Error Values (Such as #REF! and #VALUE!) | Numbers|
| Blank Cells | Blank Cells |
Example 1:
The Excel Pivot Table below has been created. As you can see the ‘Year’ and ‘Month’ sections are not in any order. We want to order the ‘Year’ and ‘Month’ section automatically when we re-arrange it later.
 |
In this example we want to sort the ‘Year’ in ascending order.
1. Click the ‘Year’ label as you would select any cell.
Sort the items ascending:
a) On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10. Alternatively you can right-click your mouse button to get to Sort and top 10 options.
 |
b) Click ‘Ascending’ under the ‘AutoSort Options’ section in the ‘Pivot Table Sort and Top 10’ dialogue box. The ‘Using field:’ drop down list will become available and show ‘Year’ by default. If the ‘Using field:’ list does not default to ‘Year’ then manually select it from the list
|
Note: If you select the ‘Using Field:’ list you can also sort the items by their values in the data area, in our case the ‘Sum of Sales’ data field.
c) Select 'OK'
|
Note: When you refresh the report or change its layout, Excel Pivot Tables re-sorts the field in the order you specified.
Note: Once you have selected ‘Ascending’ or ‘Descending’, you can no longer manually drag the ‘Year’ categories to re-order. To enable this function you need to select the ‘Manual (you can drag items to rearrange them)’ from the ‘AutoSort Options’ section in the ‘Pivot Table Sort and Top 10’ dialogue box.
Excel Pivot Table Report :: Manually Sort |
Sort the items by a particular data value. Using ‘Data’ | ‘Sort’
In this example we will sort March’s ‘Sum of Sales’ values.
Note: This is different from using the ‘AutoSort Options’ menu in the ‘Pivot Table Sort and Top 10’ dialogue box mentioned earlier.
1. In this example we will sort 2010’s ‘Sum of Sales’ values in Ascending order. Click the cell in the data area that contains the value you want to sort by. In the example below we will select the cell below the ‘2010’ Year label.
|
2. On the main menu select Data | Sort to get to the ‘Sort’ menu.
|
3. Select the ‘Ascending’ option in the ‘Sort by’ section of the Sort dialogue box and then click OK. N.b. The ‘Summary’ section of the Sort dialogue box displays a text based description of what the Sort will do.
Note: The ‘Values’ option in the Sort section of the Sort dialogue box is automatically selected as we are in the data area of the Excel Pivot Table. If we had selected the ‘Month’ label of the Excel Pivot Table the ‘Labels’ option would be selected.
4. As you can see the Sort has sorted the values in the 2010 column of the Excel Pivot Table in ascending order.
|
You'll need to repeat the sort if you refresh or update the report.
Excel Pivot Table Report :: Custom Sort Order |
The custom order is not retained if you refresh the report.
Sort the items by a particular data value. Using ‘Data’ | ‘Sort’
Note: This is different from using the ‘AutoSort Options’ menu in the ‘Pivot Table Sort and Top 10’ dialogue box mentioned earlier.
In this example we will sort the ‘Region’ label by defining a custom sort order in the following sequence; North, East, South, West, Central.
|
We need to do 2 things;
a. Define the custom sort order.
b. Apply the custom sort order.
Define the Custom Sort Order
1. Select from the main menu 'Tools' | 'Options' to open up the 'Options' menu.
|
2. Select the ‘Customs List’ tab in the ‘Options’ menu.
|
3. Click the ‘Add’ button under the ‘Customs List’ tab. The cursor will be automatically placed in the ‘List entries:’ section.
4. Type in the ‘List entries:’ section the custom order list for the Region and select OK:
(Ensure that you place a comma after each word)
North, East, South, West, Central
|
Note: If you have a long list of values you want adding as a custom order list then you can use the Import button.
The new Custom list has been added.
|
Apply the Custom Sort Order
1. Click the cell in the data area that contains the value you want to sort by. In the example below we will select the cell below the ‘Region’ label.
|
2. Select from the main menu Data > Sort to open the ‘Sort’ dialogue box.
|
Note: The ‘Labels’ option in the Sort section should be selected automatically.
|
3. Select the ‘Options…’ button at the bottom of the ‘Sort’ dialogue box. The ‘Sort Options’ box will open. Select the new Custom sort order list from the ‘First key sort order’ drop down list and click OK.. N.b. The ‘Summary’ section of the ‘Sort’ dialogue box displays a text based description of what the Sort will do.
|
Note: The ‘Labels’ option in the Sort section of the Sort dialogue box is automatically selected as we are in the data area of the Excel Pivot Table. If we had selected the Data area of the Excel Pivot Table the ‘Values’ option would be selected.
As you can see the Sort has sorted the values in the Region column of the Excel Pivot Table in the Custom sort order we specified.
|
You'll need to repeat the sort if you refresh or update the report.
Note: You can also organize items in a custom order manually by selecting and dragging them. Ensure the Autosort Options are set to ‘Manual’.
Excel Pivot Table Report :: Sort Top 10 or Top (n) |
Excel Pivot Tables have the option to sort or filter the number of results shown to the Top (n) results. For example if you have a long list of records in your Excel Pivot Table you can apply the Sort Top 10 option to filter out the top 10 or indeed the top 5 or (n) results.
Using the example Excel Pivot Table below, we will filter the top 5 ‘Sales’ results.
|
1. Select the column label ‘TransactionID’ or any of the labels under ‘TransactionID’
2. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10. Alternatively you can right-click your mouse button to get to Sort and Top 10 options.
|
3. In the ‘PivotTable Sort and Top 10’ box select the ‘On’ option in the ‘Top 10 AutoShow’ section’. This will unlock the ‘Show;’ and ‘Using Field:’ options in the same section to be changed.
|
Note: The ‘Sum of Sales’ has been chosen in the ‘Using Field:’ automatically.
4. We want the filter to show the Top 5 rather than the default Top 10. Change the ‘Show:’ section to display Top 5 and select OK
|
5. The Excel Pivot Table will now display the top 5 sales results from the list.
Note: The ‘TransactionID’ label has turned blue to identify which section the Top 10 filter has been applied to. This is useful when using multiple sections in your Excel Pivot Table.
|
| >>>Previous Section :: Sort your Pivot Table >>> |