# How to Auto Sort Table in Excel (5 Methods)

Excel features a variety of useful tools for automatically sorting tables. In this article, we’ll discuss how to use the SORT, SORTBY, VLOOKUP and LARGE Functions, and also VBA macros for this purpose.

To illustrate the methods, we’ll use the following dataset where some Sales representatives’ names and  salaries are given in columns B and C respectively.

### Method 1 – Using the SORT Function

The SORT function can sort data tables in ascending or descending order and in multiple columns.

#### 1.1 – Auto Sort Table in Ascending Order

Steps:

• Create a table with the same headings, and select cell F5.

• Enter this SORT function in the Formula Bar:
`=SORT(B5:D14,3,1)`

Where B5:D14 is the array to be sorted, 3 is the [sort_index], which is the column number, and 1 is the [sort_order],which is ascending order.

• Press Enter on your keyboard to return the result.

#### 1.2  – Auto Sort Table in Descending Order

Steps:

• Make a similar table with the same headings, and select cell F5.

• Enter this SORT function in the Formula Bar:
`=SORT(B5:D14,3, -1)`

Where B5:D14 is the array to be sorted, 3 is the [sort_index], which is the column number, and –1 is the [sort_order],which is  descending order.

• Press Enter on your keyboard to return the result.

• To check whether the table is auto-sorted or not, change the salary of Michael from \$63,782.00 to \$75,000.00, and press Enter.

Our data table is automatically sorted.

#### 1.3 – Auto Sort Table in Multiple Columns

Steps:

• Create a table with the same headings and select cell E5.

• Enter this SORT function in the Formula Bar:
`=SORT(B5:C14, {1,2}, {1,1})`

Where {1,2} is the sort-index, 1 is first the column index number that we want to sort and 2 is the second. {1,1} is the sort_order where the 2nd 1 represents ascending order.

• Press Enter on your keyboard to return the result.

• To check if auto-sorted table is working properly, replace any data from the table and press Enter. For example, replace Ben with Russel.

• Press Enter to Auto Sort the table.

### Method 2 – Using the SORTBY Function

This is the easiest way to Auto Sort tables in Excel.

Steps:

• Select cell F5.

• Enter the following SORTBY function in the Formula Bar:
`=SORTBY(B5:D14, D5:D14)`

• Simply press Enter on your keyboard to return the result.

• To check whether the table auto-sorted or not, change the salary of Carlos from \$65,208.00 to \$70,000.00.

• Press Enter.

Our data table is automatically sorted.

Read More: Excel Auto Sort when Data is Entered

### Method 3 – Using the VLOOKUP Function

Steps:

• Enter the following RANK function in cell B5:
`=RANK.EQ(D5,D\$5:D\$14,1)`

• Press Enter.

10 is returned.

• Autofill the RANK function to the entire column B.

• Apply the following VLOOKUP function in cell G5:
`=VLOOKUP(F5,\$B\$5:\$D\$14,2, FALSE)`

• Press Enter.

Russel is returned as the output.

• AutoFill the VLOOKUP function to the entire column G.

• Similarly, perform the VLOOKUP function in the entire column H.

• To check whether the auto-sort was successful or not, change the salary of Dalton from \$ 54,805.00 to \$ 80,000.00, and press Enter.

You will be able to auto-sort the table returned.

### Method 4 – Using the LARGE Function

The LARGE function can auto-sort data tables in descending order only.

Steps:

• Select cell E5 to apply the LARGE function.

• Enter the following LARGE function in the selected cell:
`=LARGE(\$D\$5:\$D\$14, C5)`

We use the dollar sign to fix the cell’s reference so that the selection does not change when moving the function from cell D5 to cell D6.

• Press Enter, and you will get \$69,738.00 as the result of the LARGE function.

• AutoFill the LARGE function to the entire column E.

You will now be able to auto-sort data tables in descending order.

• To check that the table is auto-sorted correctly, change Carlos’s Salary from \$65,208.00 to \$ 80,000.00, and press Enter.

Carlos goes to the top, as it is the maximum value.

### Method 5 – Using VBA Code

Steps:

• Right-click your mouse on the worksheet named VBA from our dataset.

A window named Auto Sort Table pops up.

• Enter the below VBA Code.
• Change the “Red-marked” area according to your column value.

The VBA Code is:

``````Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub``````

• Press F5 to run the VBA Code.

After running the VBA code, you can auto-sort the table that is returned.

## Things to Remember

While using the SORT function to auto-sort multiple columns, the first column will auto-sort first. The second column will be sorted after the previous column is sorted.

When using the VBA code, you need to insert your own worksheet value in the code.

## Related Articles

<< Go Back to Auto Sort in Excel | Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF