Sorting data in alphabetical order or numerical order is helpful when you have large amounts of data in the PivotTable you created. Sorting lets you organize the data so it is easier to find the items you want to analyze.
1. Within the PivotTable, click a field in the row or column you want to sort.
2. Click the arrow on Row Labels or Column Labels, and then click the sort option you want. Note: There could be a column or row name associated.
3. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.
Text entries will be sorted in alphabetical order. Numbers will be sorted from smallest to largest (or vice versa), and dates or times will be sorted from oldest to newest (or vice versa).
Tip: You can also right-click on a field, select Sort and then select a sorting option.
Setting custom sort options
If you wish to sort certain items manually or change the sort order you can set your own sort options.
1. Within your PivotTable, click a field in the row or column you want to sort.
2. Click the arrow on Row Labels or Column Labels then click the sort option you want then click More Sort Options.
3. In the Sort dialog box, pick the type of sort you want:
- Click Manual to rearrange items by dragging them.
- Click Ascending (A to Z) by or Descending (A to Z) by and then choose the field you want to sort.
- For Additional options, click More Options then pick the option you want in the More Sort Options dialog box:
- Under AutoSort, check or uncheck the Sort automatically every time the report is updated box to allow or stop automatic sorting whenever the PivotTable data is updated.
- Under First key sort order, pick the custom order you want to use. This option is available only when Sort automatically every time the report is updated box under AutoSort has been unchecked.
- Under Sort By, click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.
Note:
- Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.
- You cannot sort case-sensitive text entries
- In a PivotTable, you cannot sort data by a specific format like cell or font color or by conditional formatting indicators such as icon sets.
If you do not know how to create a PivotTable we have several videos and webcasts to help you learn. Additional PivotTable tips are available at SageIntelligence.com. Go to Resources/Tips & Tricks then search on PivotTable.
The post Sorting data in a PivotTable with Microsoft Excel appeared first on Sage Intelligence.
Source: Excel on Steroids
