Excel SORT Function – How to Automatically Sort DataLet's explore Excel's SORT function

  |

For as long as I know, you can always sort data in Excel.

After all, sorting data is important when you’re working with a dataset.

For example, you might work with a dataset that has dates in it.

With Excel’s sorting feature, you can arrange this dataset in chronological order, which can make working with it easier.

The thing about Excel’s sorting feature is that it does not automatically update when you add data.

This means that you have to use this sorting feature whenever new data is added.

That’s not to discredit the sorting feature though. It’s still plenty helpful and works fine as it is.

However, with the introduction of dynamic arrays in Excel 365, it’s now possible to automatically sort data in Excel whenever changes to it are made.

And it’s through the new SORT function (not to be confused with the always present sorting feature).

In this article, we will be exploring what the SORT function can do.

You’ll be learning how to use it so that you can easily sort data whenever you have to.

Let’s get started.

Excel’s SORT Function

Along with the introduction of dynamic arrays came the SORT function.

It sorts an array or range in ascending or descending order. It can sort by columns or rows depending on the parameters that you specify.

Since SORT is a dynamic function, it will result in a dynamic array that spills to adjacent cells horizontally or vertically.

Its shape will depend on the shape of the source array. If the source array has two columns and six rows, the resulting dynamic array will also have two columns and six rows.

Any changes you make in the source array will reflect in the resulting dynamic array.

Unlike the sorting feature, the SORT function does not modify the source array.

The formula for using the SORT function is as follows:

=SORT(array,[sort_index],[sort_order],[by_col])

Where

array – refers to an array of values or a range of cells that you’ll be sorting via the SORT function

[sort_index] – this specifies which column or row the SORT function will sort by; this parameter is optional and has a default value of 1 (if omitted, the SORT function will sort by the first column or row)

[sort_order] – this specifies the sorting order and can have a value of 1 or -1. If its value is 1, the SORT function will sort in ascending order. If its value is -1, it will sort in descending order. This parameter is optional and has a default value of 1.

[by_col] – this specifies whether the SORT function sorts by column or row. It can have one of these two values: FALSE or TRUE. If its value is FALSE, the SORT function will sort by row. If its value is TRUE, the function will sort by column. This parameter is optional and has a default FALSE value.

Things to note about the SORT function

  • The SORT function is only currently available in the Office 365 and 2021 versions of Excel; older versions, such as Excel 2016, do not have this function.
  • Make sure to always have enough empty cells (horizontally and vertically) adjacent to the cell where you enter the formula for the SORT function; if there are not enough empty cells, the function will return a #SPILL error.
  • The resulting dynamic array will automatically update if there are any changes made to the source array. However, it will not reflect any additions made outside of the referenced array.
  • To include such additions, you can either (1) convert the source array into a table, (2) update the array reference to include the addition in the SORT formula, or (3) create a dynamic named range.

How to use the SORT Function

Suppose we have the following dataset:

We want to make a dynamic array that automatically sorts the data by state in alphabetical order.

To do so, we’ll use the SORT function.

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell E2.
  • In the selected cell, enter the formula for the SORT function. For our illustration, the formula will be =SORT(A2:C11,1,1). [sort_index] is set to 1 because we want to sort by State, which is the first column of the referenced array. [sort_order] is set to 1 because we want to sort by State in alphabetical order. [by_col] is omitted since we will be sorting by rows anyways, which is the default setting.

  • Press the Enter key. The SORT function will automatically create a dynamic array based on the shape of the referenced array.

  • We have successfully created a dynamic array that automatically sorts the referenced array. Let’s format this new array to look like the reference array.

Sort Horizontally With the Sort Function (Sort by Column)

Most of the time, you’ll be sorting by row in Excel. But if the data you’re working on is arranged horizontally, you will have to sort by columns.

This is made easy with the SORT function. Just set the [by_col] to TRUE to sort by columns.

For example, let’s say that you have to work with this dataset:

You want to sort the data by State in alphabetical order. To do so, you’ll be using the SORT function.

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell B5.
  • In the selected cell, enter the formula for the SORT function. For our illustration, the formula will be =SORT(B1:K3,1,1,TRUE). [sort_index] is set to 1 because we want to sort by State, which is the first row of the referenced array. [sort_order] is set to 1 because we want to sort by State in alphabetical order. [by_col] is set to TRUE since we’ll be sorting horizontally (by column).

  • Press the Enter The SORT function will automatically create a dynamic array based on the shape of the referenced array.

  • You have successfully created a dynamic array that automatically sorts the referenced array. You may format this new array to look like the reference array.

Sort by Multiple Columns with the SORT Function (Multi-Level Sort)

Suppose you want to sort by multiple columns.

For example, let’s use this dataset:

First, you want to sort by State in alphabetical order.

Then, you want to sort by Donuts Sold from largest to smallest (descending order).

Can this be done with a formula? Let’s find out.

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell E2.
  • In the selected cell, enter the formula for the SORT function. For our illustration, the formula will be =SORT(A2:C11,{1,3},{1,-1). [sort_index] is set to {1,3} because we want to sort by State first, which is the first column of the referenced array, and then by Donuts Sold, which is the 3rd column of the array. [sort_order] is set to {1,-1} because we want to sort by State in alphabetical order, and then by Donuts Sold in descending order. [by_col] is omitted since we will be sorting by rows anyways, which is the default setting.

  • Press the Enter key. The SORT function will automatically create a dynamic array based on the shape of the referenced array.

  • You have successfully created a dynamic array that automatically sorts by multiple columns. You may format this new array to look like the reference array.

Use Excel Table to Make the Resulting SORT Array Expand Automatically

One of the limitations of dynamic arrays is that they don’t automatically expand to accommodate any changes or additions made outside of the reference array.

For example, let’s say that the reference array is A2:C11.

Any additions made outside of these cells will not be reflected in the dynamic array.

To fix this, you may convert your reference array into a table.

To do so, select the range of cells that you want to include in the table (include the column or row headers).

Then, press the keyboard shortcut Ctrl + T.

This will open the Create Table window.

In the textbox, the cells you selected will be automatically entered.

Double-check just to make sure.

Make sure that the box beside “My table has headers” is checked.

Finally, click the OK button.

You have successfully converted your selection into an Excel table.

You’ll also notice that the Table Design tab is automatically opened.

On the left side of the ribbon, you’ll see the Table Name.

Remember it as you’ll be needing it when using the SORT function.

Sorting Excel Tables

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell E2.
  • In the selected cell, enter the formula for the SORT function. For our illustration, we want to reference the table we created. As such, the formula will be =SORT(Table2,1,1). [sort_index] is set to 1 because we want to sort by State, which is the first column of the referenced array. [sort_order] is set to 1 because we want to sort by State in alphabetical order. [by_col] is omitted since we will be sorting by rows anyways, which is the default setting.

  • Press the Enter key. The SORT function will automatically create a dynamic array based on the shape of the referenced array.

  • You have successfully created a dynamic array that automatically sorts the referenced array. You may format this new array to look like the reference array.

Any changes made to the table, including new rows and columns, will automatically reflect in the dynamic array (you may have to manually format any new rows or columns).

Conclusion

And those are the ways you can sort data in Excel with the SORT function.

You should now be able to sort any data anytime you want.