How to Sort Data in Excel Using Formula (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In our day-to-day lives, sorting data is one of the most important works in any field. If you have a large amount of data in your worksheets, you can use this to see data that fits your requirements. Sometimes, you need to sort data from small to large, sometimes according to alphabets, and sometimes according to specific conditions. No matter what you want to do, you have to do it by sorting data. Even, to find specific data from a large dataset, these tools help identify them effortlessly. In this tutorial, you will learn everything about how to sort data in Excel using formulas.


How to Sort Data in Excel Using Formula (2 Easy Ways)

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, We have the Employee Names in column B, their Joining Dates in column C, and their Salaries in column D. If you follow the steps correctly, you should learn how to sort data in Excel using the formula. The methods are described below.

Dataset to sort data in excel using formula


1. Sorting Only a Single Column

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

Case 1: Sorting Text values

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 to sort data in Excel using formulas. Now and then, we have to search for specific information in our large Excel worksheet. But, it’s tiresome to search for it manually. A formula collaborating the INDEX and MATCH functions can do the amazing work of searching data very easily. It can also perform advanced lookups. In this article, we’ll show you the simple and effective ways to use the INDEX MATCH formula in Excel. The COUNTIF function brings conditional logic into the equation and counts the number of cells that meet specific criteria. The steps of this method are

  • First, insert the following formula in the cell.
=INDEX(B5:D11,MATCH(ROW(A1:A7),COUNTIF(B5:B11,"<="&B5:B11),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).

Inserting Formula to sort data in excel using formula

  • Then, press the Enter button to sort the names alphabetically (A-Z) starting with Johnson and ending with Usman.

Final Result to sort data in excel using formula

🔎 How Does the Formula Work?

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

  • COUNTIF(B5:B11,”<=”&B5:B11),0),1): is an Array Formula. So it returns how many cells are there in the range B5:B11 which have texts alphabetically behind or equal to each of the cells from B5 to B11.
  • ROW(A1:A7): These are actually the ranks in alphabetical order of each cell of the range B5 to B11.
  • MATCH(ROW(A1:A7), COUNTIF(B5:B11,”<=”&B5:B11): The MATCH function returns the position of each of the numbers from 1 to 7 in the array.
  • INDEX(B5:D11, MATCH(ROW(A1:A7), COUNTIF(B5:B11,”<=”&B5:B11),0),1): Finally, the INDEX function is also an Array Formula. This returns the cell content from the range B5:D11 (Our data set) with a row number equal to each of the numbers in the array {12,13,9,...,7} and column number equal to 1.

Read More: How to Use Excel Shortcut to Sort Data


Option 2: Using the SORT Function

Our aim is to learn how to sort data in Excel using formulas by using the SORT function Excel. 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. While working in Excel, we often have to sort a range or a table according to some specific values. Excel provides a function called SORT which you can use to sort any range of cells according to a specific row or column.

  • The formula to sort the names in ascending order (A-Z) will be:
=SORT(B6:D12,1,1,FALSE)

  • After pressing the Enter button, we again sorted the names in ascending order (A-Z).

Now let’s break down the formula.

🔎 How Does the Formula Work?

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

  • SORT(B6:D12): The array is the range of cells from which we want to sort any column. Here we have sorted the Employee name column from B6:D12.
  • SORT(B6:D12,1): [sort_index] is the column number of the column that we want to sort in the given array. Here are array has only one column and we want to sort that. So [sort_index] is 1. It is optional. The default is also 1.
  • SORT(B6:D12,1,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. The default is 1.
  • SORT(B6:D12,1,1, FALSE): 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. The default is 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(B5:D11,2,-1,FALSE)

Inserting Formula to sort data in excel using formula

  • If you press Enter again, you will get the desired result again.

Final Result to sort data in excel using formula

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.

Read More: How to Undo Sort in Excel


Case 2: Sorting Numerical Values

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

We will learn how to sort data in Excel using formulas by using the SMALL or LARGE functions in Excel. To sort the salaries of the employees in ascending order, the formula will be:

=SMALL(D5:D11,ROW(A1:A7))

It is an Array Formula. So do not forget to press Ctrl + Shift + Enter unless you are in Office 365.

Inserting Formula to sort data in excel using formula

  • See, we have sorted the salaries in ascending order.

🔎 How Does the Formula Work?

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

  • ROW(A1:A7)): The array is the range of cells from which we want to sort any column. Here we have sorted from A1:A7.
  • SMALL(D5:D11, ROW(A1:A7)): The SMALL function sorts the cells D5 to D11 from small to large.

Thus we get the salaries sorted from the small to the large order, that means, in ascending order. Now that you understand it, can you arrange the joining dates in ascending order? Yes. You are right. The formula will be:

=SMALL(C6:C12,ROW(A1:A7))

Inserting Formula to sort data in excel using formula

  • Now, press the Enter button to get the final result.

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(C5:C11,ROW(A1:A7))

Inserting Formula to sort data in excel using formula

  • After pressing the Enter button, you will get the final result.

Read More: How to Add Sort Button in Excel


Option 2: Using the 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).

  • In this case, the formula to sort the salaries in descending order will be:
=SORT(D4:D18,1,-1,FALSE)

Inserting Formula to sort data in excel using formula

  • Afterward, we sorted the salaries in descending order.

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


2. Sorting the Whole Data Set According to a Column

This time, we will learn how to sort data in Excel using formulas according to a specific column. The cases of this method are.


Case 1: Sorting According to Text Values

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, insert this formula in the first cell (In this case, F6):
=INDEX(B6:D12,MATCH(ROW(A1:A7),COUNTIF(B6:B12,"<="&B6:B12),0),MATCH(F5:H5,B5:D5,1))

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

🔎 How Does the Formula Work?

  • COUNTIF(B5:B11,”<=”&B5:B11): returns an array of the alphabetical rank of each of the names.
  • ROW(A1:A7): returns an array of numbers from 1 to 7, {1,2,3,4,5,6,7}.
  • MATCH(ROW(A1:A7), COUNTIF(B5:B11,”<=”&B5:B11),0): returns an array of the positions of each of the numbers from 1 to 7 in the array.
  • INDEX(B6:D12,MATCH(ROW(A1:A7),COUNTIF(B6:B12,”<=”&B6:B12),0),MATCH(F5:H5,B5:D5,1)):This is another Array Formula.

Read More: How to Sort Data by Value in Excel


Option 2: Using the SORT Function

You will learn how to sort data in Excel using formulas in ascending order of the names using the SORT function of Excel.

  • Firstly, insert the formula in cell F5.
=SORT(B5:D11,1,1,FALSE)

Inserting Formula to sort data in excel using formula

  • Then, we again sorted the whole data set according to the names.

Note: Now after discussing both methods of sorting multiple columnsof 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.

Read More: How to Remove Sort in Excel


Case 2: Sorting According to Numerical Values 

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,  in the first cell (In this case, F5), enter this formula:

=INDEX(B5:D11,MATCH(LARGE(B5:D11,ROW(A1:A7)),D5:D11,0),MATCH(F4:H4,B4:D4,0))
[Array Formula. So do not forget to press Ctrl + Shift + Enter.]

  • Afterward, you will get the desired if you press the Enter button.

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

Option 2: Using the SORT Function

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.

  • Firstly, The formula to determine the data set by the descending order of the joining dates will be:
=SORT(B5:D11,1,-1,FALSE)

  • Finally, after pressing the Enter button, you will get the desired result.

Note: With this, I have come to an end. But before saying goodbye, I shall tell you what the two major disadvantages of the SORT method are compared to the INDEX-MATCH method.
  • Firstly, in the 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.

  • Secondly, in the SORT method, you can not sort non-adjacent multiple columns.
  • Moreover, you can not sort just the names and the salaries together using the SORT method. You have to sort either the names, joining dates, and salaries, or the names and the joining dates or the joining dates and the salaries.

Now, simply the columns you want to sort must be adjacent. You also do not have this problem in the INDEX-MATCH method. Finally, You can solve the first problem with the SORT BY method of Excel other than the INDEX-MATCH method.

Special Tips: All the methods discussed here to 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, and 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. 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.

Read More: How to Sort in Ascending Order in Excel


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to sort data in Excel using formulas. in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo