Dynamic Sort Column and Sort Order in Google Sheets

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

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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

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