Sort data by excel formula


Excel provides SMALL and LARGE formula to get sorted numeric values , but sometime we need our string/text data to be sorted by formula only.

Though, there is an inbuilt sorting feature available in excel, but here I am going to demonstrate a formula.

So, Lets say you have your data list in Range “A1:A10”, Better to create a name instead of using direct reference into the formula. so I have create a name range that is “myList”



It might be looking a long formula to some of you,but believe me it is not,  because red part is same. Ok,lets evaluate this.

The base inside this formula is  this ,


this formula compares each value with each value in the list and create a two-dimensional array


 if you look at the 0 in B2 it mean that 10 in A2 is not greater than 10 in B1, and if you look at 1 in B3 , it means 12 in A2 is greater than B1, and so on. the next step is to sum this array by rows. So we will MMULT here with another array. See the  formula down below to get that second array, this should have the same number of row element as our first array but all the element would be 1

=ROW(MyList)*0+1   = {1;1;1;1;1;1;1;1;1}

and now we have two matrix to multiply , so here we use MMULT() function


After multiply that two matrix it will give a result like this table. 


Now we just need to sort these numbers, and we will use SMALL() function to do that. So here is the next formula


this will return these sorted numbers


Next , we need to match the correct index for all these elements from the above list.


this will return these index numbers


Now we got the index and we just need to get the Text from our original list


and here is your sorted list



Thanks for reading

Rajan verma


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s