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 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.

How to Sort column data.

Assuming we have a list of 7 names in a column. We can use pivot tables to sort the list and arrange it depending on your preferences. For example from A-Z. In the example below we will sort the column from the smallest to the largest

Before we start sorting we need to insert a pivot table. We select the data we want to create in the pivot table.

With the selected data select the Insert tab and then press Pivot Tables. Then select From Table/Range. This will create the pivot table from the existing table you highlighted.

Having created the PivotTable, the first step in sorting column data. We 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. To finish, click anywhere outside the custom sort dialog box.