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

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.

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

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