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

**Table of Contents**hide

**Download Practice Workbook**

**How to Sort Data in Excel Using Formula**

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

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

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

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}`

**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 arrayis`{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6, 1, 2, 7 ,13}`

**12**. - The position of
**2**in the arrayis`{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6, 1, 2, 7 ,13}`

**13.**

` .`

` ..`

` ...`

- The position of
**15**in the arrayis`{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6, 1, 2, 7 ,13}`

**7.**

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

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

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

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

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

Let’s break down the formula now

** ROW(A1:A15) **returns an array of the numbers

**1**to

**15,**

**Go to section**

`{1, 2, 3,...,15}`

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

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

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

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.

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

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,

**See section**

`{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6, 1, 2, 7 ,13}`

. **1.1.1**for details.

** ROW(A1:A15)** returns an array of numbers from

**1**to 1

**5**,

**.**

`{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}`

See section **1.1.1** for details.

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

**See section**

`{14, 11, 4, 8, 10, 12, 15, 5, 3, 9, 6, 1, 2, 7 ,13}`

. **1.1.1**for details.

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

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

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

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

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

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.

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

.

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.

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]

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

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:

- 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