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.

**Table of Contents**hide

**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)`

**Note:**This is an

**Array Formula**. So press

**Ctrl + Shift + Enter**to insert this formula (Not necessary if you are in

**Office 365**).

- 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 arrayand column number equal to`{12,13,9,...,7}`

**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.

**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 **

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**

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.

**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. 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**

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**