How to Sort Data in Excel Using Formula (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sorting data in Excel means arranging data in order, like A to Z or smallest to largest, for better organization. This can be useful for organizing data in a more meaningful way, making it easier to analyze and interpret.

In this Excel tutorial, you will learn how to sort data in Excel using formulas in some practical cases.

Here’s an overview of our sample dataset to understand sorting easily. We have the Employee Names, Joining Dates, and Salaries in our dataset. Check out the image below to see the changes that occur after sorting the data.

Sorting data in Excel

Here are the 2 methods of sorting data by using Excel Formulas:


Using the SORT Function

The SORT function is useful for sorting text, dates, or numbers. The function is designed to work with dynamic arrays, allowing you to spill the sorted results over a range of cells dynamically. You can use this function for both ascending and descending order.

Here are three cases of how to use the SORT function:


Case 1: Sorting in Ascending Order

In the SORT function, the sort_order or the 3rd argument defines the order of sorting. Use 1 for ascending order and -1 for descending order. The 2nd argument specifies the column number for sorting the selected data range.

To sort data in ascending order using the SORT function:

  1. Select a blank cell.
  2. Apply the formula: =SORT(B6:D12,1,1)

So, the text values in the first column are sorted in ascending order.

Applying SORT function to get ascending order

If you want to sort the third column which has number values, change the sort_index or the 2nd argument in the formula according to your cell range.

To sort the number values in the third column, follow the instructions below:

  1. Select a blank cell
  2. Apply the formula: =SORT(B6:D12,3,1)

Applying SORT function for numeric values

Note: The SORT function can be utilized to arrange the dates in ascending or descending order. Once the formula is applied, the date column will be displayed in the General format. Modify the format from General to Date to accurately represent the dates.

Case 2: Sorting in Descending Order

Here we will change the sort_order or the 3rd argument from 1 to -1 to sort the data in descending order. Let’s apply it for the third column.

To sort data in descending order using the SORT function, follow the instructions below:

  1. Select a blank cell.
  2. Apply the formula: =SORT(B6:D12,3,-1)

The third column is now sorted in descending order.

Applying SORT function to get number values in descending order

Case 3: Sorting Rows

We’ve modified the dataset here. We transposed our earlier dataset to enhance the understanding of this method. To sort the rows, we will insert TRUE as the 4th argument. Otherwise, Excel will consider FALSE in the 4th argument by default for sorting columns.

To sort rows using the SORT function, follow the steps below:

  1. Select a cell.
  2. Apply the formula: =SORT(C5:H7,1,1,TRUE)

Thus, the data is sorted horizontally in alphabetical A-Z order.

Sorting data horizontally

Note: The SORT function is available in only Office 365, MS Excel 2019, and later versions.


Using INDEX, MATCH and COUNTIF Functions

This formula is useful if you are working with multiple worksheets. It is applicable for both text and numerical values, allowing you to sort data in both ascending and descending order.

Here are the two cases given below:


Case 1: Ascending Order

To sort data in ascending order using INDEX-MATCH functions, follow the steps below:

  1. First, select a cell.
  2. Insert the following formula and press Enter:
    =INDEX(B6:D12,MATCH(ROW(A1:A7),COUNTIF(D6:D12,”<=”&D6:D12),0),MATCH(F5:H5,B5:D5,3))

Thus, the data in your third column is sorted in ascending order.

Applying INDEX-MATCH functions to sort data ascendingly

The formula can also be used for the first column where text values are present.

Case 2: Descending Order

For arranging the data in descending order, you might need to adjust the COUNTIF part accordingly.

To sort data in descending order follow the steps below:

  1. Select a cell.
  2. Apply the following formula:
    =INDEX(B6:D12,MATCH(ROW(A1:A7),COUNTIF(D6:D12,”>=”&D6:D12),0),MATCH(F5:H5,B5:D5,3))

So, the data in the third column has been arranged in descending order.

Applying INDEX-MATCH functions to sort data desscendingly

Note: This is an Array Formula. So press Ctrl + Shift + Enter to insert this formula (Not necessary if you are in Office 365).


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Conclusion

Hopefully, these methods will help you to learn how to sort data in Excel using formulas in Excel. You can now sort data ascendingly and descendingly for text, date, and numerical values. 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.


Frequently Asked Questions

How do I automatically sort data in Excel?

To sort data automatically in Excel:

  1. Select a range.
  2. Go to the Data tab > Sort & Filter group > Sort A to Z.
    For Ascending order.

Your data will be sorted automatically.

Can I create custom sorting orders using formulas?

Yes, the SORT function allows for custom sorting orders. You can use other functions like MATCH and SEQUENCE to define a custom order for sorting your data based on specific criteria.

How do I sort data in a table automatically?

If your data is organized as a table, click on the header of the column you want to sort, and choose “Sort Ascending” or “Sort Descending.” The table structure will automatically apply the sort to the entire dataset.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo