Sorting in Pivot Table in Excel
Sorting in Pivot Table in Excel is a fundamental feature that allows you to arrange and analyze large sets of data more effectively. It enables you to rearrange and prioritize the data according to specific criteria, making it easier to identify patterns, trends, and insights.
The Normal Sort option does not apply to Pivot Tables, and Pivot Tables are not normal tables. That’s why sorting done from PivotTable is known as Pivot Table Sort.
There are many ways to sort the data in the Pivot Table. By following below way to reveal the sorting options:
- Select the row or column on which you want to apply sorting:
Go to the Data tab in the Excel ribbon then go to the Sort & Filter group or
Go to the Home tab in the Excel ribbon then go to the Editing group. - Click the small arrow next to Row Labels and Column Labels cells in the Pivot Table.
- Right-click on the row or column field, and select the Sort.
Sorting Options in Pivot Table Sort
- Now you can choose one of the sorting options below, these sorting options will appear or change depending on your data type. So don’t be confused while using:
- Use “Sort A to Z”, “Sort Smallest to Largest”, or “Sort Oldest to Newest” to sort data in ascending order.
- Use “Sort Z to A”, “Sort Largest to Smallest”, or “Sort Newest to Oldest” to sort data in descending order.
- “More Sort Options” to access advanced sorting features like sorting by specific values, labels, or manual settings.
Note:- Pivot Table sorting options appear depend on the Data Type or Format of data as below:
- The Text/String will be sorted in alphabetical order from “Sort A to Z” (or vice versa)
- The Numbers will be sorted from “Sort Smallest to Largest” (or vice versa) and
- The Dates or Times will be sorted from “Sort Oldest to Newest” (or vice versa).
Sorting on a column doesn’t have an Arrow
If don’t show sorting sign (Arrow) in pivot table then also you can sorting the data by following steps:
- You can sort individual values or subtotals by right-clicking a cell, selecting Sort, and then choosing a sort method.
- The sort order applies to all cells at the same level in the column that contains the cell.
Sorting in Pivot Table on multiple fields or sub-categories
- If you have multiple category fields in a pivot table, the data will be sorted only within and after the subcategories you selected for sorting.
- Sorting will not be applied to the entire pivot table. Therefore, it is important to pay attention to not only the field you are choosing for sorting but also the sub-category you are trying to sort.
Sorting in Pivot Table horizontally
Labels and values are typically sorted vertically, but if you are using the column labels area of a pivot table, you may have a need to sort data horizontally.
- Right-click on any cell in Grand Total or in any row on which you want to sort horizontally.
- Select Sort and then choose a sort method that you want to apply i.e. Sort A to Z, Sort Smallest to Largest, or Sort Oldest to Newest, or vice versa.
Manual or custom sorting in Pivot Table
Manually or custom sorting in Pivot Table on specific items or change the sort order, you can set your own sort options:
- Click the Filter drop-down arrow next to the Row Labels or Column Labels, and then click More Sort Options.
- A Sort dialog box will open
- Click Manual (you can drag items to rearrange them) to rearrange items by dragging them.
- Hover the cursor over the border of the item until you see the mouse arrow cursor change to the move cursor (four black arrows facing away from each other), then drag it to the desired position.
Note: You can not drag the items that are shown in the Values area of the PivotTable Field List.
Set Pivot Table Sort Options
- For additional options, click More Options…, and then pick the option you want in the More Sort Options dialog box:
- In AutoSort, check or uncheck the box for “Sort automatically every time the report is updated” – to allow or prevent automatic sorting whenever PivotTable data is updated or refreshed.
- Under “First key sort order”, select the custom order you want to use. This option is only available if there is no check in the box for “Sort automatically every time the report is updated”.
- In Sort By, click Grand Total or Values in selected columns to sort by these values. Note: This option is not available when you set sorting to Manual.
- Excel has some built-in custom lists that you can use for custom sort options.
- Full month names – January to December
- Short month names – Jan to Dec
- Full weekday names – Sunday to Saturday
- Short weekday names – Sun to Sat
- You can also create new custom lists and use them from here.
- Go to File Tab in the Excel ribbon then Options
- In the Excel Options dialog box, click on Advanced and browse to General.
- You will find the Edit Custom Lists… button, click on this button.
- The Custom Lists dialog box will open where you can Create new lists for use in sort and fill sequences.
- Select NEW LIST in the Custom List box, place your cursor in the List Entries: box, and type your field list, one item per line.
- Once you have your list completed, click the Add button
- To apply this list, select the list from the Custom Lists box
- Click OK then
- click OK on the Excel Options dialog box
- Now list will appear in the sort order, select it and apply.
Newly added items in the Pivot table are not sorted, how to fix it?
Sometimes, if you add new rows of data to the pivot table source, they will not appear in the correct order in the pivot table. If this persists after refreshing the pivot table, you can try the below steps:
- Right-click on any cell in the Pivot Table field on which you want to sort.
- Choose the sort method by following the steps of the Pivot Table Sorting above.
- This will change the sorting mode from Manual to AutoSort of the Pivot table. Thereafter, all the newly added items will be sorted automatically based on the sorting method.
Things to Consider When Sorting PivotTables
When you sort data in a PivotTable, remember below points −
- Data that contains leading spaces will affect the sort results. So remove all leading spaces before sorting the data.
- You can’t sort case-sensitive text.
- You cannot sort data by a specific format like cell or font color.
- You cannot sort data based on conditional formatting indicators like icon sets.
Hence, Sorting is an essential tool for data analysis and reporting.
FAQs
In some cases, pivot table items might not be sorted as you want. So follow the below steps to solve this sorting problem:
- Right-click a cell in the pivot table, and click PivotTable Options or go through Analyze tab.
- In the PivotTable Options dialog box, click the Totals and Filters tab.
- In the Sorting section, remove the check mark from “Use custom lists when sorting.”
- To quickly sort a pivot table field, use the shortcut keys ALT + A + SS. This will open the Sort Field dialog box, where you can choose how to sort the field that is already discussed above.
- You can use ALT + A + SA for Ascending and ALT + A + SD for descending.
The data in the month column is not like actual months so it cannot be sorted in chronological order. The best way to deal with this problem is to enter an actual date for each month. For example, enter 1/1/2020 for each cell for January. For February enter 2/1/2020 etc.
- Click the small arrow next to Row Labels and Column Labels cells in which date data is available in the Pivot Table
- select Sort method Sort Oldest to Newest or Sort Newest to Oldest as per need.