How to Sort Data in Excel Using Formula

Sorting Whole Data Set by a Text Column

Today I will be showing how you can sort any data using formula in Excel.


Download Practice Workbook


How to Sort Data in Excel Using Formula

Let’s have a look at the data set below.

A Data Set in Excel

We have the employee record of a company named Jupyter Group.

We have the employee names in column B, their joining dates in column C, and their salaries in column D.

Now let’s try to sort this set of data in various ways.


1. Sorting only a Single Column

First of all, we shall learn how to sort a single column of this data set.


Case 1: Sorting Text values (Using INDEX-MATCH-COUNTIF and SORT)

Let’s try to sort the Employee Name column (Column B) in alphabetical order (A-Z).

We will accomplish this in two ways.


Option 1: Using INDEX-MATCH-COUNTIF Functions

We can sort the names alphabetically using a combination of INDEX, MATCH and COUNTIF functions.

The formula is:

=INDEX(B4:D18,MATCH(ROW(A1:A15),COUNTIF(B4:B18,"<="&B4:B18),0),1)

Note: This is an Array Formula. So press Ctrl + Shift + Enter to insert this formula (Not necessary if you are in Office 365).

Single Column Sorted by INDEX-MATCH-COUNTIF

See, we have sorted the names alphabetically (A-Z) starting with Alfred Morris and ending with Usman Malik.

Now for the sake of understanding, let’s break down the formula.

COUNTIF(B4:B18,"<="&B4:B18) is an Array Formula.

It consists of 15 formulas altogether.

  • COUNTIF(B4:B18,"<="&B4)
  • COUNTIF(B4:B18,"<="&B5)
  • COUNTIF(B4:B18,"<="&B6)

       .

       ..

       ...

  • COUNTIF(B4:B18,"<="&B18)

So it returns how many cells are there in the range B4:B18 which have texts alphabetically behind or equal to each of the cells from B4 to B18.

  • The number of cells alphabetically behind or equal to B4 in B4:B18 is 14.
  • The number of cells alphabetically behind or equal to B5 in B4:B18 is 11.

     .

     ..

     ...

  • The number of cells alphabetically behind or equal to B18 in B4:B18 is 13.

COUNTIF Array Formula in Excel

Now, concentrate carefully. These are actually the ranks of alphabetical order of each cell of the range B4 to B18.

For example, there are 4 names in the range B4:B18 alphabetically behind the name “Isha Sharma”. They are “Alfred Morris”, “Angela Austin”, “Graeme Lee” and “Gregory Hick”.

And one name “Isha Sharma” itself is alphabetically equal to “Isha Sharma”.

So the alphabetical rank of “Isha Sharma” is four plus one, five.

Similarly, the alphabetical rank of B4, Steve Johnson is 14, that of Richard Simpson is 13, and so on.

Hope you have understood what the COUNTIF function does here.

Now let’s see what ROW(A1:A15) does.

This is also an Array Formula.

  • ROW(A1)
  • ROW(A2)

       .

       ..

       ...

  • ROW(A15)

The ROW function returns the row number of a cell.

  • ROW(A1) returns the row number of cell A1. It is 1.
  • ROW(A2) returns the row number of cell A2. It is 2.

       .

       ..

       ...

  • ROW(A15) returns the row number of cell A15. It is 15.

So, what we actually get is an array of the numbers from 1 to 15, {1,2,3,...,15}

ROW Array Formula in Excel

Note: I have taken up to A15 because I have a total of 15 names (B4 to B18). You take according to your need. For example, if you have a total of 50 names, take A1:A50.

Now the formula becomes:

=INDEX(B4:D18,MATCH({1,2,3,...,15},{14,11,4,...,13},0),1)

MATCH({1,2,3,...,15},{14,11,4,...,13},0) is again an Array Formula.

  • MATCH(1,{14,11,4,...,13},0)
  • MATCH(2,{14,11,4,...,13},0)

     .

     ..

     ...

  • MATCH(15,{14,11,4,...,13},0)

It returns the position of each of the numbers from 1 to 15 in the array {14,11,4,...,13}.

  • The position of 1 in the array {14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13} is 12.
  • The position of 2 in the array {14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13} is 13.

   .

   ..

   ...

  • The position of 15 in the array {14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13} is 7.

MATCH Array Function in Excel

Now the formula becomes:

=INDEX(B4:D18,{12,13,9,3,8,11,14,4,10,5,2,6,15,1,7},1)

Finally, this is also an Array Formula.

  • INDEX(B4:D18,12,1)
  • INDEX(B4:D18,13,1)
  • INDEX(B4:D18,9,1)

    .

    ..

    ...

  • INDEX(B4:D18,7,1)

This returns the cell content from the range B4:D18 (Our data set) with row number equal to each of the numbers in the array {12,13,9,...,7} and column number equal to 1.

  • The cell in the range B4:D18 with row number 12 and column number 1 is B15. It returns Alfred Morris.
  • The cell in the range B4:D18 with row number 13 and column number 1 is B16. It returns Angela Austin.

    .

   .

   ...

  • The cell in the range B4:D18 with row number 7 and column number 1 is B10. It returns Usman Malik.

Thus we get the names sorted in alphabetical order (A-Z).

Now if you understand the above formula, then can you sort the names in the order Z-A?

Pretty simple. Just replace the less than equal to (“<=”) in the formula with greater than equal to (“>”).

=INDEX(B4:D18,MATCH(ROW(A1:A15),COUNTIF(B4:B18,">="&B4:B18),0),1)

Single Column Sort by INDEX-MATCH-COUNTIF

See we have sorted the names in the order Z-A, from Usman Malik to Alfred Morris.


Option 2: Using SORT Function

You will be surprised to know that the complex work that I did a few minutes ago can be done by using just a single function, SORT.

The formula to sort the names in ascending order (A-Z) will be:

=SORT(B4:B18,1,1,FALSE)

Single Column SORT in Excel

See, we have again sorted the names in ascending order (A-Z).

Now let’s break down the formula.

The syntax of the SORT function is:

=SORT(array,[sort_index],[sort_order],[by_col])
  • The array is the range of cells from which we want to sort any column. Here we have sorted the Employee name column from B4:B18.
  • [sort_index] is the column number of the column that we want to sort in the given array. Here our array has only one column and we want to sort that. So [sort_index] is 1. It is optional. Default is also 1.
  • [sort_order] means ascending or descending order you want. For ascending it is 1, for descending it is -1. Here we want the ascending order (A-Z). So it is 1. It is optional. Default is 1.
  • And finally, [by_col] means whether you want to sort by position in the column or not. We do not want to, so it is FALSE. It is also optional. Default is FALSE.

Thus the complete formula becomes:

=SORT(B4:B18,1,1,FALSE)

Now can you tell me the formula to sort the names in descending order (Z-A)?

Easy. Just use a -1 in place of the 1 in the 3rd argument ([sort_order]).

=SORT(B4:B18,1,-1,FALSE)

Single Column SORT in Descending Order

Hope you have understood. But do not be so pleased.

The SORT function is available in only Office 365.

So, if you do not have a subscription, you have to use the previous complex formula.


Case 2: Sorting Numerical Values (Using SMALL or LARGE and SORT)

Now let’s sort any data of numerical value.

For example, let’s try to sort the salaries of the employees in our data set.

We can also accomplish it in two ways.


Option 1: Using SMALL or LARGE Functions

To sort the salaries of the employees in ascending order, the formula will be:

=SMALL(D4:D18,ROW(A1:A15))
[It is an Array Formula. So do not forget to press Ctrl + Shift + Enter unless you are in Office 365]

Single Column Numerical Value Sort by SMALL

See, we have sorted the salaries in ascending order.

Let’s break down the formula now

ROW(A1:A15) returns an array of the numbers 1 to 15, {1, 2, 3,...,15}. Go to section 1.1.1 to see it in detail.

The formula becomes:

=SMALL(D4:D18,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})

It is an Array Formula.

  • SMALL(D4:D18,1)
  • SMALL(D4:D18,2)
  • SMALL(D4:D18,3)

 .

  ..

  ...

  • SMALL(D4:D18,15)

It returns 1st, 2nd, 3rd up to the 15th smallest value from the range D4:D18.

  • SMALL(D4:D18,1) returns the 1st smallest value from D4:D18.
  • SMALL(D4:D18,2) returns the 2nd smallest value from D4:D18.
  • SMALL(D4:D18,3) returns the 3rd smallest value from D4:D18.

  .

   ..

   ...

  • SMALL(D4:D18,15) returns the 15th smallest value from D4:D18.

Thus we get the salaries sorted from the small to the large order, that means, in ascending order.

Now if you understand it, can you arrange the joining dates in ascending order?

Yes. You are right. The formula will be:

=SMALL(C4:C18,ROW(A1:A15))

Dates in Ascending Order

And what will be the formula to sort the dates in descending order?

This is also easy. Just use a LARGE function in place of the SMALL function.

=LARGE(C4:C18,ROW(A1:A15))

Dates in Descending Order


Option 2: Using SORT Function

We can also sort the salaries or the joining dates in ascending or descending order using the SORT function of Excel (Only available in Office 365).

The formula to sort the salaries in descending order will be:

=SORT(D4:D18,1,-1,FALSE)

Salaies in Descending Order

See we have sorted the salaries in descending order.

Go to section Option 2, Case 1, Section1 for a detailed explanation.


2. Sorting the Whole Data Set According to a Column

Up to now, we have sorted only one column from the set of data.

But this time we will try to sort the whole data set according to a specific column.


Case 1: Sorting According to Text values (Using INDEX-MATCH-COUNTIF and SORT)


Option 1: Using INDEX-MATCH-COUNTIF Functions


Let’s try to sort the whole data set according to the names of the employees in ascending order (A-Z).

First, create an empty table with the title names of all the columns which you want to sort. Here we want to sort all the columns of the data set.

An Empty Table in Excel

Then insert this formula in the first cell (In this case, F4):

=INDEX(B4:D18,MATCH(ROW(A1:A15),COUNTIF(B4:B18,"<="&B4:B18),0),MATCH(F3:H3,B3:D3,1))

Sorting Whole Data Set by a Text Column

See, we have sorted the whole data set according to the names of the employees (A-Z).

Let’s break down the formula now.

COUNTIF(B4:B18,"<="&B4:B18) returns an array of the alphabetical rank of each of the names, {14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13}. See section 1.1.1 for details.

COUNTIF Array Formula in Excel

ROW(A1:A15) returns an array of numbers from 1 to 15, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}.

See section 1.1.1 for details.

ROW Array Formula in Excel

MATCH(ROW(A1:A15),COUNTIF(B4:B18,"<="&B4:B18),0) returns an array of the positions of each of the numbers from 1 to 15 in the array {14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13}. See section 1.1.1 for details.

MATCH Array Function in Excel

So it returns {12,12,9,3,8,11,14,4,10,5,2,6,15,1,7}.

Now, what does this MATCH(F3:H3,B3:D3,1) portion do?

F3:H3 are the names of the columns which we want to sort.

And B3:D3 are the names of all the columns of the data set.

It is another Array Formula.

  • MATCH(F3,B3:D3,1) 
  • MATCH(G3,B3:D3,1) 
  • MATCH(H3,B3:D3,1) 

These return an array consisting of the positions of the names of the columns we want to sort in the names of all the columns.

  • MATCH(F3,B3:D3,1)  returns the position of F3 (Employee Name) in the range B3 to D3. That is 1.
  • MATCH(F3,B3:D3,1)  returns the position of G3 (Joining Date) in the range B3 to D3. That is 2.
  • MATCH(F3,B3:D3,1)  returns the position of H3 (Salary) in the range B3 to D3. That is 3.

So it returns an array of {1,2,3}.

A MATCH Array Formula in Excel

Now the complete formula becomes:

=INDEX(B4:D18,{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6,  1, 2, 7 ,13},{1,2,3})

This is another Array Formula.

  • INDEX(B4:D18,14,{1,2,3})
  • INDEX(B4:D18,11,{1,2,3})
  • INDEX(B4:D18,4,{1,2,3})

     .

     ..

     ...

  • INDEX(B4:D18,13,{1,2,3})

Each one is broken down another thrice

  • INDEX(B4:D18,14,1)     INDEX(B4:D18,14,2)     INDEX(B4:D18,14,3)
  • INDEX(B4:D18,11,1)     INDEX(B4:D18,11,2)     INDEX(B4:D18,11,3)
  • INDEX(B4:D18,4,1)      INDEX(B4:D18,4,2)      INDEX(B4:D18,4,3)

   .                                          .                                    .

   ..                                         ..                                   ..

   ...                                        ...                                  ...

  • INDEX(B4:D18,13,1)     INDEX(B4:D18,13,2)     INDEX(B4:D18,13,3)

INDEX(B4:D18,14,1) returns the 14th element of the 1st column of the range B4 to D18. That is the first man, Alfred Morris.

INDEX(B4:D18,14,2) returns his joining date, and INDEX(B4:D18,14,3) returns his salary.

Thus all the names are sorted alphabetically, along with their salaries and joining dates.

Now, if you understand this, can you sort the names in descending order along with just their salaries?

Yes, You are right. The formula will be:

=INDEX(B4:D18,MATCH(ROW(A1:A15),COUNTIF(B4:B18,">="&B4:B18),0),MATCH(F3:G3,B3:D3,1))

Two Columns Sorted by INDEX-MATCH


Option 2: Using SORT Function

You can sort the whole data set in ascending order of the names using the SORT function of Excel.

The formula will be:

=SORT(B4:D18,1,1,FALSE)

Sorting Whole Data Set by SORT

See, we have again sorted the whole data set according to the names.

Remember the syntax of the SORT function?

=SORT(array,[sort_index],[sort_order],[by_col])
  • The array here is the whole data set, B4:D18, because we want to sort the whole data set.
  • The [sort_index] is 1, because we want to sort according to the 1st column of the data, the names.
  • The [sort_order] is 1. Because we want the ascending order. For descending, use -1.
  • And the [by_col] is FALSE, because we do not want to sort the data according to their positions in the column.

Special Note: Now after discussing both methods of sorting multiple columns of a data set according to a single column (the INDEX-MATCH method and the SORT method), you should know that there are two major disadvantages of the SORT method in comparison to the INDEX-MATCH method?

Can you find those out?

Sure. I will tell you. But first, you try to find these out. I will tell you in the last part of my article.


Case 2: Sorting According to Numerical Values (Using INDEX-MATCH-SMALL and SORT)

Now we are in the final section.

This time we shall try to sort the whole data set by some numerical values.

Let’s try to sort the whole data set by the joining dates, in ascending order.


Option 1: Using INDEX-MATCH-SMALL

First of all, create an empty table with the titles of the columns you want to sort, like section 2.2.1

An Empty Table in Excel

Then in the first cell (In this case, F4), enter this formula:

=INDEX(B4:D18,MATCH(SMALL(C4:C18,ROW(A1:A15)),C4:C18,0),MATCH(F3:H3,B3:D3,0))
[Array Formula. So do not forget to press Ctrl + Shift + Enter.]

Sorting Whole Data Set by Date

See, we have sorted the data set according to the joining dates, from 12/12/2010 to 9/2/2016.

Now let’s break down the formula.

SMALL(C4:C18,ROW(A1:A15)) returns the joining dates in the ascending order, {12/12/10,4/3/11,5/5/11,...,9/2/16}

See section 1.2.1 for details.

Joining Dates in Ascending Order

MATCH(SMALL(C4:C18,ROW(A1:A15)),C4:C18,0) is an Array Formula.

  • MATCH(12/12/10,C4:C18,0)
  • MATCH(4/3/11,C4:C18,0)
  • MATCH(5/5/11,C4:C18,0)

   .

   ..

   ...

  • MATCH(9/2/16,C4:C18,0)

It returns an array of the positions of each date in the cells C4:C18.

  • MATCH(12/12/10,C4:C18,0) returns the position of 12/12/10 in the range C4:C18, that is 8.
  • MATCH(4/3/11,C4:C18,0) returns the position of 4/3/11 in the range C4:C18, that is 9
  • MATCH(5/5/11,C4:C18,0) returns the position of 5/5/11 in the range C4:C18, that is 10

   .

   ..

   ...

  • MATCH(9/2/16,C4:C18,0) returns the position of 9/2/16 in the range C4:C18, that is 4.

Array MATCH Formula in Excel.

We, see it returns the array {8,9,10,7,12,11,2,3,13,6,15,1,14,5,4}.

MATCH(F3:H3,B3:D3,0) returns an array of the positions of the titles of the columns that we want to sort in the titles of the columns of the whole data set, {1,2,3}.

See section 2.1.1 for details.

A MATCH Array Formula in Excel

The formula then becomes:

=INDEX(B4:D18,{8,9,10,7,12,11,2,3,13,6,15,1,14,5,4},{1,2,3})

This is another Array Formula.

It returns the cell contents of all the cells with row as each value of the array {8,9,10,7,12,11,2,3,13,6,15,1,14,5,4} and column as each value of the array {1,2,3}.

See section 2, Case 1, Option 1 for details.

Thus we get the data sorted by the joining dates in ascending order.

Finally, if you understand this, can you sort only the names and salaries of the employees according to the descending order of the salary?

Yes. You are right. The formula will be:

=INDEX(B4:D18,MATCH(LARGE(D4:D18,ROW(A1:A15)),D4:D18,0),MATCH(F3:G3,B3:D3,0))
[LARGE in place of SMALL function, because descending order]

Sorting Whole Dataset by Salary


Option 2: Using SORT Function

And obviously, you can sort the data set by ascending or descending order of any column of numerical value by the SORT function of Excel, in case you have an Office 365 subscription.

The formula to determine the data set by the descending order of the joining dates will be:

=SORT(B4:D18,2,-1,FALSE)

Whole Data Set Sorted by Date

See section 2.1.2 for details.

Note: With this I have come to an end. But before saying good bye, I shall tell you what are the two major disadvantages of SORT method comparing to the INDEX-MATCH method.

  • First, in SORT method, the column according to which you want to sort some data set must be within the data set.

For example, if you want to sort just the names and the joining dates of the employees according to their salaries, you can not do that using the SORT function.

If you want to sort according to the salaries, you have to include that column in the list of the columns you want to sort.

But you do not have this problem in the INDEX-MATCH method.

  • Second, in SORT method, you can not sort non-adjacent multiple columns.

For example, you can not sort just the names and the salaries together using the SORT method. You have to sort either both the names, joining dates and salaries, or the names and the joining dates or the joining dates and the salaries.

Simply the columns you want to sort must be adjacent.

You also do not have this problem in the INDEX-MATCH method.

You can solve the first problem with the SORTBY method of Excel other than the INDEX-MATCH method.

Special Tips: All the methods discussed here create a copy of the sorted data set of the original data set.

If you do not want to create a copy of the sorted data set, just want to sort the original data set, there are tools to do that in the Excel toolbar (Home>Sort and Filter in the Editing section or Data>Sort). Just select the data set you want to sort and go there.

And if you want to create a sorted copy, but do not want to use any formula, can do that using the PivotTable of Excel. I will discuss that one other day.


Conclusion

Using these methods you can sort any type of values from any set of data using formulas. Do you know any other method? Let us know in the comment section.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo