Rifat Hassan
In our day-to-day life, 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 formula in excel.
Download Practice Workbook
2 Easy Methods to Sort Data in Excel Using Formula
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 formula in excel. The methods are
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 formula in excel. 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)
- Then, press the Enter button to sort the names alphabetically (A-Z) starting with Johnson and ending with Usman.
🔎 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.
Option 2: Using the SORT Function
Our aim is to learn how to sort data in excel using formula 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)
- If you press Enter again, you will get the desired result again.
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
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 formula 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.
- 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))
- Now, press 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))
- After pressing the Enter button, you will get the final result.
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)
- Afterward, we 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
This time, we will learn how to sort data in excel using formula 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.
Option 2: Using the SORT Function
You will learn how to sort data in excel using formula 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)
- Then, we again sorted the whole data set according to the names.
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
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))
- 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
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.
- 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.
- 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.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to sort data in excel using formula in excel. 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.
Further Readings
- How to Arrange Numbers in Ascending Order in Excel using Formula
- How To Sort Alphabetically In Excel And Keep Rows Together
- How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
- Sorting Columns in Excel While Keeping Rows Together
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Auto Sort Multiple Columns in Excel (3 Ways)
- How to Sort Columns in Excel without Mixing Data
