Select a table to sort, or select just specific rows in a column to sort. In the Organize sidebar, click the Sort tab. Click the pop-up menu in the sidebar and choose Sort Entire Table or Sort Selected Rows. Click Add a Column. Choose a column to sort by, then click the pop-up menu in the rule and choose Ascending or Descending. To have Excel return the current date, select the cell you want Excel to show the current date in and type the cell formula =TODAY and then click the green Enter button. The selected cell displays today’s date, and Excel automatically changes the format of the cell to Date. Getting today’s serial number in Excel 2011 for Mac.

Today we'll take a close look at the syntax and typical uses of the new dynamic array SORTBY function. You will learn how to custom sort in Excel with a formula, randomly sort a list, arrange cells by text length, and more.

Microsoft Excel provides a number of ways to arrange text data alphabetically, dates chronologically, and numbers from smallest to largest or from highest to lowest. There is also a way to sort by your own custom lists. In addition to the conventional Sort functionality, Excel 365 introduces a brand-new way to sort data with formulas - very convenient and incredibly simple to use!

How to use SORTBY function in Excel - formula examples

Excel SORTBY function

The SORTBY function in Excel is designed to sort one range or array based on the values in another range or array. Sorting can be done by one or multiple columns.

SORTBY is one of six new dynamic array functions available in Microsoft Excel 365. Its result is a dynamic array that spills to neighboring cells and updates automatically when the source data changes.

The SORTBY function has a variable number of arguments - the first two are required and the other are optional:

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

Array (required) - the range of cells or array of values to be sorted.

How To Sort By Date In Excel 2011 For Mac

By_array1 (required) - the range or array to sort by.

Sort_order1 (optional) - the sorting order:

1 or omitted (default) - ascending
-1 - descending

By_array2 / Sort_order2, … (optional) - additional array / order pairs to use for sorting.

Important note! Currently the SORTBY function is only available with Microsoft 365 subscriptions. In Excel 2019, Excel 2016 and earlier versions the SORTBY function is not available.

SORTBY function - 4 things to remember

For an Excel SORTBY formula to work correctly, there are a few important points to take notice of:

By_array arguments should be either one row high or one column wide.
The array and all by_array arguments must have compatible dimensions. For example, when sorting by two columns, array, by_array1 and by_array2 should have the same number of rows; otherwise a #VALUE error will occur.
If the array returned by SORTBY is the final result (output in a cell and not passed to another function), Excel creates a dynamic spill range and populates it with the results. So, be sure you have enough empty cells down and/or to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
The results of SORTBY formulas update automatically whenever the source data changes. However, new entries that are added outside of the array referenced in the formula are not included in the results unless you update the array reference. For the referenced array to expand automatically, convert the source range to an Excel table or create a dynamic named range.

Basic SORTBY formula in Excel

Here's a typical scenario of using a SORTBY formula in Excel:

Supposing, you have a list of projects with the Value field. You want to sort the projects by their value on a separate sheet. As other users do not need to see the numbers, you'd rather not include the Value column in the results.

The task can be easily accomplished with the SORTBY function, for which you supply the following arguments:

Array is A2:A10 - since you do not wish the Value column to be displayed in the results, you leave it out of the array.
By_array1 is B2:B10 - sort by Value.
Sort_order1 is -1 - descending, i.e. from highest to lowest.

Putting the arguments together, we get this formula:

=SORTBY(A2:B10, B2:B10, -1)

For simplicity, we use the formula on the same sheet - enter it in D2 and press the Enter key. The results 'spill' automatically to as many cells as needed (D2:D10 in our case). But technically, the formula is only in the first cell, and deleting it from D2 will delete all the results.

When used on another sheet, the formula takes the following shape:

=SORTBY(Sheet1!A2:A10, Sheet1!B2:B10, -1)

Where Sheet1 is the worksheet containing the original data.

Using SORTBY function in Excel - formula examples

Below you will find a few more examples of using SORTBY in Excel 365, which will hopefully prove useful and insightful.

Sort by multiple columns

The basic formula discussed above sorts data by one column. But what if you need to add one more level of sorting?

Assuming our sample table has two fields, Status (column B) and Value (column C), we wish to sort first by Status alphabetically, and then by Value descending.

To sort by two columns, we just add one more pair of the by_array / sort_order arguments:

Array is A2:C10 - this time, we want to include all three columns in the results.
By_array1 is B2:B10 - first, sort by Status.
Sort_order1 is 1 - sort alphabetically from A to Z.
By_array2 is C2:C10 - then, sort by Value.
Sort_order2 is -1 - sort from largest to smallest.

As the result, we get the following formula:

=SORTBY(A2:B10, B2:B10, 1, C2:C10, -1)

Which rearranges our data exactly as we instructed it:

Custom sort in Excel with a formula

To sort data in a custom order, you can either use Excel's Custom Sort feature or build a SORTBY MATCH formula in this way:

SORTBY(array, MATCH(range_to_sort, custom_list, 0))

Taking a closer look at our data set, you will probably find it more convenient to sort the projects by their status 'logically', e.g. by importance, rather than alphabetically.

To have it done, we first create a custom list in the desired sort order (In progress, Completed, On hold) typing each value in a separate cell in the range E2:E4.

And then, using the generic formula above, we supply the source range for array (A2:C10), the Status column for range_to_sort (B2:B10), and the custom list that we created for custom_list (E2:E4).

=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))

As the result, we've got the projects sorted by their status exactly as needed:

To sort by custom list in the reverse order, put -1 for the sort_order1 argument:

=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)

And you'll have the projects sorted in the opposite direction:

Want to additionally sort records within each status? No problem. Simply, add one more sort level to the formula, say by Value (C2:C10), and define the desired order of sorting, ascending in our case:

=SO

How To Sort By Date In Excel 2011 For Mac