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.

Sort Data in Excel Using the SORT Formula

To sort Data in Excel using a formula,

• Select a cell to insert an array formula.
• Use the following formula and press the ENTER button.

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

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)

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)

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)

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)

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)

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.

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.