Sorting data in Excel

January 24, 2020 Excel tips,

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.

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.

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

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1