You are currently viewing How to sort columns in pivot table in Excel

How to sort columns in pivot table in Excel

When the amount of data in a pivot chart or PivotTable is large, sorting it is quite important. Sorting is a way of keeping your data organized to make it easier when searching for specific items or information that requires more scrutiny.

There are different ways to sort data, including using the alphabetical order, organizing from the highest to lowest values, and from the lowest to highest values. Time and dates can be used either from newest to oldest and vice versa. With your data organized in your preferred way, retrieving key details is effective and less time-consuming.

Sort orders are not generally the same due to differences in locale settings which one can confirm by checking the Windows Help system. Sort results are affected by data entered containing leading spaces. The leading spaces should be removed before sorting the data to achieve optimal results. However, text entries that are case sensitive cannot be Sort. Also, indicators like icon sets for conditional formatting cannot be used to sort. There's no specific format that can be used to sort the data, such as font color and cell color.

When sorting columns in a pivot table, some challenges may arise whereby some items are not in the preferred order, especially A to Z. This can happen, for instance, when sorting names and Jan is at the top of the list, yet there are names like Abigael. It often happens because of the built-in custom list in Excel.

You can still create a custom list depending on the items you're required to sort. To avoid the custom list taking precedence, you have to make changes. In the PivotTable, right-click a cell and get to the options. Then click the Filters and Tools tab, and in the Sorting section, click to remove the mark from Use Custom Lists When Sorting. At this point, you can sort your data as planned.

 

1. Sort column data.

The first step in sorting column data in PivotTable is to click the field with the data or items that need Sort. The option is at the Pivot table.

Click Sort on the data tab, then choose the type of order you wish to sort your data. If your option is not on the displayed options, additional options are available on the Click Options.

Times or dates are sorted from newest to oldest, and vice versa, numbers from lowest to highest or vice versa, and text entries are planned in alphabetical order.

Set custom options

One can change sort order or even sort specific data manually by setting sort options.

2. In the column you plan to sort, click a field there.

3. Next to the column labels, click the drop-down arrow with Filter.

4. In the options provided at the Sort by list under Sort, choose to descend or ascend. At this point, the options vary according to the selections made in the preceding steps.

5. Under Filter, choose any criteria you might prefer to add. For instance, if the data that needs to be presented is for a specific month, select Equals in the list by label and type June in the box that appears. In case there's a need to undo the selection, the Clear option Filter is used.

6. To finish, click anywhere outside the custom sort dialog box.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.