Google Sheets Pivot Table: Sort by Value (3 Easy Ways)

The pivot table is perhaps the most powerful data summarization tool in Google Sheets. However, we can organize it further by sorting the data by the values.

In this article, we will look at just how to do that. How to sort a Google Sheets pivot table by value.

How to Sort by Value in a Google Sheets Pivot Table

For our example, we will use the following worksheet as our source data for the pivot table:

example dataset - google sheets pivot table sort by value

The dataset contains various data types that we will use to sort the pivot table eventually.

After creating a pivot table in a separate worksheet, we’ll have the default layout presented like this:

base layout of the pivot table after it is created

All that is left is to populate the table and show how we can sort the data in it.

Let’s get started.

1. Sort by the Value of Data in the Pivot Table

We have populated the pivot table to show the sums of Sales and Revenues for each Product:

a google sheets pivot table that contains the sums of the sales and revenues of each product

These two columns of values are important as they will be the basis of the sort. And as you can see, none of the values are sorted.

To begin, we must first decide which of these values we will use to sort the pivot table.

For this example, we have chosen the sum of Revenue.

Step 1: Go to the Rows panel and click on the ‘Sort by’ drop-down:

the sort by drop down contains all the value headers you want the pivot table to sort by

This presents all the options that are available for the user to sort the Google Sheets pivot table by.

Step 2: Select the ‘SUM by Revenue’ option to sort the pivot table by that value.

sort by sum of revenue value in a google sheets pivot table ascending

Step 3 (Optional): You can also set the order by which the table will be sorted. By default, it is set to ‘Ascending’ where the table will be sorted from the lowest to the highest value. Setting the order to ‘Descending’ will wort the pivot table from the highest value to the lowest.

sorting by the descending order of values in a pivot table

2. Sort By the Value of Headers of a Pivot Table (Sort by Month)

One of the most common values used to sort any data is the Date, especially sorting data by months. The pivot table is no exception.

Here, we have created a pivot table with the primary column containing Dates that have been grouped by months. This pivot table shows the sum of sales and revenue for each month.

google sheets pivot table grouped by months

As you can see, the pivot table is already sorted in the ascending order of the month values:

the pivot table is sorted in the ascending order of the month values

A Google Sheets pivot table automatically sorts itself according to the values it is grouped by.

Which, in this case, was the month value.

Note that Google Sheets automatically prioritizes the order of sort according to the value. In this case, month values start with January and end with December in ascending order.

Other examples include directions and other names where the ascending order is in the alphabetical order: East > North > South > West.

the region values are sorted in the alphabetical order by default

3. How to Custom Sort a Pivot Table

However, if you are not satisfied with how a Google Sheets pivot table sorts your data, you can always settle for a custom sort.

For example, we know that when it comes to directions or names, the spreadsheet sorts the data in alphabetical order.

What if you wanted a custom order for this sort? Like North > South > East > West?

You can if you follow these steps:

Step 1: Create a helper column in the source dataset and input this formula in it:


creating a helper column to set the sorting priority

The formula looks for matching values in the Region column using the MATCH function. The function also assigns a value (1-4) to each match. This value will act as the priority of the pivot table sort.

Step 2: Update the data range of the pivot table to include the Helper column.

updating the data range of the pivot table to accommodate the helper column

If you are creating a brand-new pivot table, just remember to include the Helper column.

Step 3: Add these conditions to the pivot table in order:

  1. Rows > Add > Helper > Deselect ‘Show totals’ checkbox
  2. Rows > Add > Region > Deselect ‘Show totals’ checkbox (optional)
  3. Values > Add > Sales
  4. (Optional) Filters > Add > Region > Deselect ‘Blanks’

conditions to set a custom sort google sheets pivot table

This will create a pivot table with the custom order of the Helper column, which in turn corresponds to the Region values.

Step 4: Hide the helper column from the pivot table.

hiding the helper column from the pivot table

And we are done!

We have successfully sorted a Google Sheets pivot table in a custom order of values.

Final Words

That concludes all the ways we can use to sort a Google Sheets pivot table by different types of values. Thanks to the pivot table editor, that task becomes that much easier. We can also take advantage of a helper column to set up a custom sort for the pivot table.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply