# How to Sort Data in Excel Using a Formula (7 Formulas)

Excel allows sorting your data in several ways. You can use both the **Sort** button as well as formulas to sort data in Excel. In this article, you will learn to sort data in Excel using a formula. You will learn 7 different sorting formulas, that can help you fix any kind of sorting problem. If you are in a rush, you can check out the quick solution section to get quick help. But unfortunately, it only works in **Microsoft Office 365**. So without having any further discussion, let’s dive straight into them one by one.

## Quick Solution

**To sort Data in Excel using a formula,**

**=SORT(C2:D10,2,1,FALSE)**

**❌**** Attention:***The SORT function is available only in Microsoft Office 365!*

**Formula Explanation**

**C2:D10**➤ is the range of the columns that we want to sort.**2**➤ is the sort_index.**1**➤ is responsible for sorting in ascending order. Using –**1**will sort in descending order.**FALSE**➤ is responsible for sorting by columns. Use**TRUE**to sort by rows.

### Formula #1: Sort a Single Column in Excel Having Numbers Using the SMALL, LARGE, and ROWS Function

**1. Sort in Ascending Order (Sort Smallest to Largest)**

❶ Create a separate column where you want to get your numbers sorted.

I made a separate column called Price in column **F**.

❷ Now insert the following formula in the first cell (Cell **F2**) of the new column.

**=SMALL($D$2:$D$10,ROWS($C$1:C1))**

❸ After that hit the **ENTER** button to execute the formula.

❹ Now place your mouse curse in the right bottom corner of cell **F2**. The **Fill** **Handle** icon will appear.

❺ Drag the **Fill** **Handle** icon from cell **F2** to **F10**. This will apply the above formula all the way from cell **F2** to **F10**.

**Formula Explanation**

**ROWS($C$1:C1)**➤ returns the row count numbers such as 1,2,3, etc as you drag down the formula. Dollar signs are added to**C1**to lock the cell. If you don’t lock the first cell, it will return a series of 1 only. Thus, the formula will not do its job.**SMALL($D$2:$D$10,ROWS($C$1:C1))**➤ returns the first smallest number in the cell range**$D$2:$D$10**. As the**ROWS function**continues to return 2,3,4, etc respectively, then the**SMALL function**keeps returning the second, third, etc smallest number. Thus the numbers look sorted in ascending order.

**2. Sort in Descending Order ( Sort Largest to Smallest)**

❶ Create a separate column to store the formula result.

I made another column named **Price** in column **F**.

❷ Insert the following formula in the first cell of the new **Price** column.

**=LARGE($D$2:$D$10,ROWS($C$1:C1))**

❸ Hit **ENTER** to execute the formula.

❹ Drag the **Fill** **Handle** icon from cell **F2** to **F10** to apply the same formula all around this cell range.

**Formula Explanation**

**$D$2:$D$10**➤ is the range of the cells where the numbers are located. You need to update this as per your datasheet.**$C$1:C1**➤ is the row counter. You can keep this intact in your formula. Or, you can modify this but always use the same cell address here with the first part of it locked.

**🔗****How to Sort Multiple Columns in Excel (4 Techniques)**

### Formula #2: [Array Formula] Sort a Single Column in Excel Having Numerical Values with the SMALL, LARGE, and ROW Functions

**1. Sort in Ascending Order Using the Array Formula (Sort Smallest to Largest)**

❶ Create a new column to store the formula result.

❷ Enter the following array formula in the first cell of the newly created column.

**=SMALL(D2:D10,ROW(C1:C9))**

❸ If you are using **Microsoft Office 365**, just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the whole new column ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

**Formula Explanation**

**D2:D10**➤ is the cell range where the numbers are stored.**C1:C9**➤ is used to return a row count number. You should always make the count equal to the number of rows in your data table. For example, if you have**50**rows in your data table, it will be**C1:C49**. You can other cell addresses here such as**A1:A9, B1:B9, C1:C9,**etc.

**2. Sort in Descending Order Using Array Formula ( Sort Largest to Smallest)**

❶ Create a new column to store the sorted numbers.

❷ Insert the following formula in the very first cell of the new column.

**=LARGE(D2:D10,ROW(C1:C9))**

❸ If you are using **Microsoft Office 365**, just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the whole new column ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

**Formula Explanation**

**D2:D10**➤ is the cell range where the numbers are stored.**C1:C9**➤ is used to return a serial number equal to the total row numbers in the data table starting from 1.

**🔗 How to Sort in Excel by Name (3 Techniques)**

### Formula #3: Sort a Single Column in Excel Having Texts Using the INDEX, MATCH, ROWS, and COUNTIF Functions

**1. Sort in Ascending Order (Sort A to Z)**

❶ Create a new column to store the sorted texts.

❷ Insert the following formula in the first cell of the new column.

**=INDEX($B$2:$D$10,MATCH(ROWS($A$1:A1),COUNTIF($B$2:$B$10,"<="&$B$2:$B$10),0),1)**

❸ Now press the **ENTER** button to finish inserting it.

❹ Drag the **Fill Handle** icon from cell **F2** to **F10**.

**Formula Explanation**

**$B$2:$D$10**➤ is the table range. You need to update this range according to your data table.**$A$1:A1**➤ is used to return the row number counts such as 1, 2, 3, etc. You can use anything like**$B$1:B1, $C$1:C1, $D$1:D1,**etc here.**$B$2:$B$10**➤ is the range of the cells where the text data are stored. You need to update this range as per your data table.**<=**➤ is responsible for sorting data from**A to Z**. If you change this operator to**>=**, it will sort data from**Z to A.****0**➤ is used to define**Exact Match**. You can leave this option as it is.**1**➤ is the column index number. This means the range**$B$2:$B$10**is the first column of the table range**$B$2:$D$10.**You must update this according to your data table. You can use**2**to indicate the second column,**3**for the third column, etc.

**2. Sort in Descending Order ( Sort Z to A)**

❶ Create a new column to store the sorted data.

❷ Insert the following formula in the very first cell of the newly created column.

**=INDEX($B$2:$D$10,MATCH(ROWS($A$1:A1),COUNTIF($B$2:$B$10,">="&$B$2:$B$10),0),1)**

❸ Hit **ENTER**.

❹ Drag the **Fill Handle** icon to the end of the new column.

**🔗 How to Sort by Number in Excel (4 Techniques)**

### Formula #4: [Array Formula] Sort a Single Column in Excel Having Texts Using the INDEX, MATCH, ROW, and COUNTIF Functions

**1. Sort in Ascending Order (Sort A to Z)**

❶ First of all, create a new column to store the sorted data.

❷ Enter the following array formula in the first cell of the new column.

**=INDEX(B2:D10,MATCH(ROW(A1:A9),COUNTIF(B2:B10,"<="&B2:B10),0),1)**

❸ For **Microsoft Office 365**, just hit the **ENTER** button to insert the formula.

For the Other versions of Excel users,

**Select an equal number of cells as the source column ➤ in the first cell of the selection range, insert the above formula ➤ then press CTRL + SHIFT + ENTER to insert the above array formula.**

**Formula Explanation**

**B2:D10**➤ is the data table range. You will update this range.**A1:A9**➤ is used to return**1, 2, 3…10,**as there is a total of**10**rows in the data table. Here, you can use anything like**B1:B9, C1:C9, D1:D9,**etc.**B2:B10**➤ cell range of the column where the text data are stored. You need to update this range.**<=**➤ is responsible for sorting data in ascending order. You can use**>=**to sort in descending order.**0**➤ is used to define**Exact Match**. In general, you need not update this.**1**➤ is a column index number. This means range**B2:B10**is the 1st column of the table range**B2:D10**. You can use**2,3,4,**etc to refer**2nd**,**3rd**, and**4th**columns respectively.

**2. Sort in Descending Order ( Sort Z to A)**

❶ Create a new column to store the sorted text data.

❷ Use the following array formula in cell **F2**, which is the first cell of the new column.

**=INDEX(B2:D10,MATCH(ROW(A1:A9),COUNTIF(B2:B10,">="&B2:B10),0),1)**

❸ For **Microsoft Office 365**, just hit the **ENTER** button to insert the formula.

For the Other versions of Excel users,

**Select an equal number of cells as the source column ➤ in the first cell of the selection range, insert the above formula ➤ then press CTRL + SHIFT + ENTER to insert the above array formula.**

**🔗 How to Sort in Excel by Date (3 Ways)**

### Formula #5: [Array Formula] Sort Data in Excel Using the SORT Function (Single & Multiple Columns)

The **SORT function** is available only in **Microsoft Office 365.**

**//If you are using an older version of Excel, you can’t use this formula.**

**SORT Function Syntax**

**=SORT (table_array, [sort_index], [sort_order], [by_col])**

**Function Arguments**

**table_array**➤ This field is**mandatory**. Here, you will insert the cell range of a single column or multiple columns to sort their contents.**sort_index**➤ This is**optional**to insert. By default, the value is set to**1**. Here, you will insert the column number of the table array based on which the data will be sorted.**sort_order**➤ This is also an**optional**field. It has two values. Value**1**is for sorting in an ascending and**-1**is for sorting in descending order. The default value is**1**.**by_col**➤ This is also**optional**. Its value can be either**TRUE**or**FALSE**. If you want to sort by the column you will insert**TRUE**. To sort by row you will insert**FALSE**. But by default, its value is set to**FALSE**.

**1. Sort in Ascending Order (Sort Smallest to Largest)**

❶ Allocate cells where you want to keep the sorted data.

❷ Insert the following formula in the first cell of the selected space.

In this case, it’s cell **G2**.

**=SORT(D2:E10,2,1,FALSE)**

❸ Just hit the **ENTER** button to insert the formula.

**Formula Explanation**

**D2:E10**➤ is the range of the columns that we want to sort.**2**➤ is the**sort_index.****1**➤ is responsible for sorting in ascending order.**FALSE**➤ is responsible for sorting by columns.

**2. Sort in Descending Order ( Sort Largest to Smallest)**

❶ Create separate columns as per the requirement to store the sorted data.

❷ Insert the following formula in the very first cell of the newly created columns. Cell **G2** for this instance,

**=SORT(D2:E10,2,-1,FALSE)**

❸ Now hit the **ENTER** button to execute the above formula.

**Formula Explanation**

**D2:E10**➤ is the range of the columns that we want to sort.**2**➤ is the**sort_index.****-1**➤ is responsible for sorting in descending order.**FALSE**➤ is responsible for sorting by columns.

**🔗 How to Sort in Excel by Color (3 Techniques)**

### Formula #6: [Array Formula] Sort Multiple Columns in Excel Having Numbers Using the INDEX, MATCH, ROW, LARGE, and SMALL Functions

**1. Sort in Ascending Order (Sort A to Z)**

❶ Allocate space to store the sorted numbers.

❷ Insert the following formula in the first cell of the newly allocated space. Cell **G2** for this instance.

**=INDEX(D2:E10,MATCH(SMALL(D2:D10,ROW(A1:A9)),D2:D10,0),MATCH(G1:H1,G1:H1,0))**

❸ Now, if you are using **Microsoft Office 365**, just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the range G2:H10 ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

**Formula Explanation**

**D2:E10**➤ is the range of the cells, whose contents you want to sort.**D2:D10**➤ cell range of the first column of the considered table array,**D2:E10.****A1:A9**➤ returns row count value. It returns serial numbers from**1 to 9**. This series of numbers should be equal to the number total number of rows in the data table. In this case, the total number of rows in the range**D2:E10**is**10**. That’s why**A1:A9**was taken. This should always start from cell**1**such as**A1, B1, C1,**etc.**0**➤ defines**Exact Match**type.**G1:H1**➤ is the very first-row range of the newly created table to store the sorted data.

**2. Sort in Descending Order ( Sort Z to A)**

If you replace the **SMALL function** in the formula of the previous method, it will sort numbers in descending order.

Anyways, here is the full process for you.

❶ First, create a blank table with headers like columns **D & E** in columns **G & H.**

❷ Then insert the following formula in cell **G2**.

**=INDEX(D2:E10,MATCH(LARGE(D2:D10,ROW(A1:A9)),D2:D10,0),MATCH(G1:H1,G1:H1,0))**

❸ Now, if you are using **Microsoft** **Office** **365**, just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the range G2:H10 ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

**Formula #7: [Array Formula] Sort Multiple Columns in Excel Having Texts Using the INDEX, MATCH, ROW, and COUNTIF Functions**

**1. Sort in Ascending Order (Sort Smallest to Largest)**

❶ Create a blank table just like the one whose contents you want to get sorted.

❷ Insert the following formula in the first cell of the blank table. Cell **G2** for this case.

**=INDEX(B2:C10,MATCH(ROW(A1:A9),COUNTIF(B2:B10,"<="&B2:B10),0),MATCH(F1:G1,F1:G1,0))**

❸ Now, if you are using **Microsoft Office 365,** just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the range F2:G10 ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

**Formula Explanation**

**B2:C10**➤ is the range of the source data table.**A1:A9**➤ is the row number counter.**B2:B10**➤ is the first column of the source data table.**<=**➤ is responsible for sorting in ascending order.**0**➤ defines**Exact Match.****F1:G1**➤ first row of the newly created blank table.

**2. Sort in Descending Order ( Sort Largest to Smallest)**

This is the same formula as the formula used in the previous method. Just the **“<=”** is replaced by** “>=”.**

To use the formula,

❶ First of all, allocate a cell range to store the formula result.

❷ Insert the following formula in the very first cell of the allocated cell range. Cell **F2** for this case.

**=INDEX(B2:C10,MATCH(ROW(A1:A9),COUNTIF(B2:B10,">="&B2:B10),0),MATCH(F1:G1,F1:G1,0))**

❸ Now, if you are using **Microsoft Office 365**, just press the **ENTER** button to execute.

For the other versions of MS Excel, **select the range F2:G10 ➤ then insert the above formula in the first cell of the selected cells ➤ press CTRL + SHIFT + ENTER to insert the array formula.**

## Conclusion

So, we have discussed 7 different formulas to sort data in Excel. Each of the formulas has its own purpose while sorting data in Excel. If you face any problems using any of the formulas, let us know in the comment section. We will try to help you asap.