• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort data in excel using formulae

Hi Team,

I just want to understand what is the formula to sort data in ascending and descending order.

I know we can use shortcuts ALT D S S or through navigating from Home screen in excel.

Please do post the formulae to sort data in ascending and descending order

Peter Bartholomew

Well-Known Member
It is worth at least being aware that under Office 365 monthly channel some new functions have appeared under the mantle of dynamic arrays.
The function to sort a range in ascending order by the first column is
= SORT( Table1 )
Descending it becomes
= SORT( Table1, ,-1 )
where the missing parameter specifies the column to sort by. A further parameter allows sorting across columns. More complicated multicolumn sorts are possible using the function SORTBY. To sort the table by column 2 and return only the first entry from column 1 use
= @SORTBY(Table1,Table1[Column2],-1)

Peter Bartholomew

Well-Known Member
Then you are left with some variation COUNTIFs for ranking and INDEX for the lookup as shown in Salim Hasan's suggested video from Mike Girvin. Implementing such formulas can get very frustrating when you know that far simpler approaches are available.