HomeGoogle DocsSpreadsheetDynamic Sort Column and Sort Order in Google Sheets

Dynamic Sort Column and Sort Order in Google Sheets

Published on

Using dynamic sort column and sort order in Google Sheets SORT formula you can avoid using multiple SORT formulas. Using a drop-down, it’s easy to control the sort column. We can use a Tick box to control the sort order.

To help you sort a dataset, Google Sheets offers 3 functions. The SORT function is one among them and I am using that for our purpose. Regarding other functions related to sorting, you can read my following guide – Sort Data in Google Sheets – Different Functions and Sort Types.

First, let me explain to you about the dynamic sort column and dynamic sort order concept in Sheets SORT function.

For that, we can use the following sample data of 4 players and their scores in 3 grames.

Sample Data to Test the Dynamic SORT Formula in Sheets

PlayersGame 1Game 2Game 3
Phillip15002200500
Kathy14002800450
Harry15752000650
Irene14502025700

There are 4 columns. So I want 4 types of different sorting in this data. For this, I may need to use 8 Sort formulas or change one Sort formula 8 times! Didn’t get what I am talking about? Please, read-on.

To sort the ‘Players’ column we can use two formulas. I mean 1 formula to sort the “Players” in ascending order and the other for sorting it in descending order.

Similar to the to “Players” column, you can use two formulas each in the remaining columns.

That means you can sort the Game 1 column too in ascending or descending order. The same is applicable to Game 2 and Game 3 columns.

So there will be a total of 8 formulas four sorting four columns (4 columns x 2 formulas each).

If you follow my dynamic sort column and sort order method, you can limit the formulas to one!

Yes! The idea is dynamically controlling the sort column and sort order using a data validation drop-down and a tick box. The tick box will act as the sort order and the drop-down will act as the sort column.

How to Dynamically Control Sort Column and Order in Sort Function

See this live screenshot to understand what we are discussing.

Dynamic Sort Column and Sort Order in Sheets

See how I am sorting the data on the left dynamically with the help of a drop-down menu and a tick box.

There are three major steps involved in simplifying the SORT formula or you can say making the SORT formula dynamic. They are;

  1. Creating a one cell simple drop-down menu in cell F1.
  2. Inserting one Tick box in cell G1.
  3. The Dynamic Sort Formula in cell F3.

See the syntax of the SORT function for your quick reference.

SORT(range, sort_column, is_ascending)

As mentioned above, the drop-down replaces the sort_column and the Tick box replaces is_ascending.

Drop-Down to Control Sort_Column

First, we should create a drop-down list that contains the field labels (column names).

I have my drop-down menu in cell F1. To create a drop-down list with field labels, follow the steps below.

  1. Click on cell F1 (no double click).
  2. Then click on the “Data” menu and select “Data validation”.
  3. Refer to the image below. The field labels are in the range A2:D2. Use it in the field against “List from a range”.
Drop-Down to Control Sort_Column in Google Sheets

Tick Box to Control Is_Ascending (Sort Order)

This is the simplest step. Just click on cell G1 and insert a Tick box from the Insert menu. For your information, when ticked, the vale in the cell G1 will be TRUE else FALSE.

If the value is TRUE, in sorting, it can be used to sort the data in ascending (A->Z) order else descending (Z->A) order.

I have customized the Tick box to change its color. See that additional tips here – Change the Tick Box Color While Toggling in Google Sheets.

SORT Formula – Drop-Down Value and Tick Box Value to Control Sort Output

In cell F3, use this formula.

=sort(A3:D6,match(F1,A2:D2,0),G1)

The Match formula returns the column index based on the selected value in cell F1. This formula replaces the sort_column. Let me make it clearer to you.

The Match formula returns;

  • One – if the selected item in cell F1 is ‘Name’.
  • Two – if it is Game 1.
  • Three – if it is Game 2.
  • and Four if the selected item is Game 3.

That means the Match formula enables dynamic sort column in Sort. Regarding the Tick box, it returns TRUE or FALSE, that controls the sort order.

Hope you will try this dynamic sort column and sort order formula in your Google Sheets file. Enjoy!

Related Reading:

  1. Sort by Custom Order in Google Sheets [How to Guide].
  2. Formula to Sort By Month Name in Google Sheets.
  3. How to Sort Horizontally in Google Sheets.
  4. Sort By Sort_Column Name Instead of Sort_Column Header in Google Sheets.
  5. Custom Sort Order in Google Sheets Query [Workaroud].
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

  1. Hi!

    I can not get it to work.

    The function is not working together with MATCH. It’s not connecting the strings. Any ideas?

LEAVE A REPLY

Please enter your comment!
Please enter your name here