How To Sort Pivot Table In Google Sheets

In Pivot Table reports, other than sorting columns and rows in ascending/descending order, I think there must be a custom sort order. At present, there is no option to sort Pivot Table columns in the custom order in Google Sheets.

Honestly, I am not happy with the sort order of columns in a Pivot Table report. Because even if we are not satisfied with the ascending or descending sort order, we can't manually change the column order by drag and drop.

If you try to manually drag the columns to get your desired sort order, you will end up seeing #REF! error, with the tooltip saying "array result was not expanded…".

I forgot to say one thing! Other than sorting pivot table columns in ascending or descending order, there is one more option available. That is sorting the columns by the Grand Total – How to Sort Pivot Table Grand Total Columns in Google Sheets.

Here I am going to provide you a workaround to sort Pivot table columns in a custom order, I mean a custom list order, in Google Sheets.

Pivot Table Default Output Without Custom Sorting of Columns:

Pivot Table report with default column/field sorting

In the above Pivot Table, I have entered the custom order list (in blue ink) and in that order I want the columns to appear. See that customized table below.

Pivot Table Fields Sorted by Custom Order:

Example - Sort Pivot Table Columns in the Custom Order in Google Sheets

Steps to Sort Pivot Table Columns in Custom Order in Google Sheets

My work around uses an additional column (helper column).

In my above example, the source data is in the range A3:B. Please refer to the first screenshot to see the sample data.

In that, the first column contains task names and the second column contains its current status.

See the Pivot Table report (in the first screenshot). You can see that the status column (B3:B) in the source data pivoted (arranged in columns). The field labels in the Pivot Table are the unique values from this column.

Now let me begin with the steps to Sort Pivot Table Columns in Custom Order in Google Sheets.

Step # 1:

Helper Column for Pivot Table that Contains Sort Order Numbering

In cell C3, I am going to use the below formula. The range C3:C13 is going to act as the helper column in the report.

          =ArrayFormula({"Custom Order";IFERROR(match(B4:B,{"Withheld";"Finished";"Under Progress";"Not Yet Started"},0))})

Helper column for Pivot contains sort order

The Formula Logic:

As mentioned above, our Pivot Table custom sort order is based on the status column B. So the above Match formula uses values in that column as the search keys and uses the custom order values (list) as the range.

I know, this can make you confuse. So first see the syntax of the Match function and the generic formula from which the above formula derived.

Syntax:

          MATCH(search_key, range, [search_type])

Generic Formula:

          MATCH(status column B, custom sort order values, 0 (data is unsorted))

Now please check my above Match formula. I hope you can understand that better now. If you face any issue in understanding the formula, please drop a line in the comment below.

Step # 2:

Settings in Pivot Table Editor to Sort Pivot Table Columns in the Custom Order

First, select the range A3:C13. Then go to;

Data > Pivot table.

Use the cell E1 in the existing Sheet to create the table.

The settings inside the Pivot Editor:

Rows > Add > Task.
Columns > Add > Custom Sort Order (the Helper column).
Columns > Add > Status.
Values > Add > Status > Summarise by > COUNTA.

Refer the below image to understand these Pivot Editor settings better.

Detailed Pivot Editor Setting for custom sort order

Step # 3:

Additional Manual Settings in the Pivot Table Report

First, hide the row # 2 (see the image above) in the report containing the sort order label. Then use the Insert > Drawing tool to create a Text Box contain the text "Grand Total". Change the font color to white and place it in cell J3 as below.