MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Sorting with a Formula

July 31, 2024 - by

Excel 2024: Sorting with a Formula

Sorting data in Excel is easy. Unless, you are building a dashboard for your manager's manager. You can't ask that person to select C3, go to the Data tab and click the AZ button every time they want an updated report. The new SORT and SORTBY functions allow you to easily sort with a formula.

You can pass three arguments to the SORT function. The first is the range to be sorted. Leave the headings out of this argument. Next, which column do you want to sort by. If your data is in B:D and you want to sort by column D, you would specify column 3 as the sort column. The third argument is a 1 for ascending or -1 for descending.

In this figure, the data is sorted by Amount descending:

Headings are in B2:D2. Data in B3:D9. You want to use a formula to sort the data by the Amount in column D. This is the 3rd column in the data. Off to the right, enter =SORT(B3:D9,3,-1). The 3 means 3rd column. The -1 means descending. You would use 1 for ascending. Note that the headings do not come over - you would have to use a different formula to copy those.
Headings are in B2:D2. Data in B3:D9. You want to use a formula to sort the data by the Amount in column D. This is the 3rd column in the data. Off to the right, enter =SORT(B3:D9,3,-1). The 3 means 3rd column. The -1 means descending. You would use 1 for ascending. Note that the headings do not come over - you would have to use a different formula to copy those.

What if you want to do a two-level sort? You can specify an array constant for both the second and third argument. In this case, the data is sorted by Team ascending and Amount descending. For the sort column, specify {2;3}. For the sort order, specify {1;-1}.

If you want to sort by 2 columns, use array constants: =SORT(B3:D9,{2;3},{1,-1}). This sorts by Team in C ascending and then Amount in D descending.
If you want to sort by 2 columns, use array constants: =SORT(B3:D9,{2;3},{1,-1}). This sorts by Team in C ascending and then Amount in D descending.

The Excel Calc team also gave you the SORTBY function. Say you want to return a list of products but not the associated amounts. You want the products to be sorted by the amount. The formula below says to return the products from B3:B9 sorted descending by the amounts in D3:D9.

You want the products from B sorted by the amounts in D. But you don't want the amounts in the resulting formula. Use =SORTBY(B3:B9,D3:D9,-1).
You want the products from B sorted by the amounts in D. But you don't want the amounts in the resulting formula. Use =SORTBY(B3:B9,D3:D9,-1).

Bonus Tip: Sort into a Random Sequence

Why would you need to sort randomly? Perhaps you are picking the lucky winner of a free Thanksgiving turkey. Or the unlucky winner of the quarterly drug screening. Use SORTBY and specify the list of employees as the first argument. For the second argument, use RANDARRAY(N) where N is the number of employees. Every time you press F9, the results in column D will sort again.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Kier in Sight Archives on Unsplash