# How to Sort Data Using a Formula in Excel – 2 Methods

This is an overview:

### 1.1 Sorting in Ascending Order

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

Text values in the first column are sorted in ascending order.

To sort the third column that contains number values, change the sort_index or the 2nd argument in the formula:

• Select a blank cell
• Use the formula: =SORT(B6:D12,3,1)

Note: After sorting, the date column will be displayed in General format. Change the format to Date.

### 1.2 Sorting in Descending Order

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

The third column is sorted in descending order.

### 1.3 Sorting Rows

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

Data is sorted horizontally in alphabetical order: A-Z.

## Method 2 – Using the INDEX, MATCH and COUNTIF Functions

### 2.1 Ascending Order

• Select a cell.
• Use 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))

Data in the third column is sorted in ascending order.

### 2.2 Descending Order

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

Data in the third column is sorted in descending order.

Note: This is an Array Formula. Press Ctrl + Shift + Enter for older Excel versions.

### How do I automatically sort data in Excel?

To sort data automatically:

• Select a range.
• Go to the Data tab > Sort & Filter > Sort A to Z.

### Can I create custom sorting orders using formulas?

Yes, use the SORT function, the MATCH and SEQUENCE functions to define a custom sorting order.

### How do I sort data in a table automatically?

Click the column header and choose “Sort Ascending” or “Sort Descending.”

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF