How to Auto Sort Table in Excel (5 Methods)

While working with a large data table in Microsoft Excel, sometimes we need to sort tables and Excel can easily do it. Excel is very useful for automatically sorting tables. We can use the SORT, SORTBY, VLOOKUP, LARGE Functions, and VBA macros also to auto-sort tables in Excel. Today, In this article, we’ll get to learn how the Excel auto sort table can be done.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Suitable Ways to Auto Sort Table in Excel

Let’s say, we have a dataset where some Sales representative name and their salary are given in columns B, and C respectively. We will learn how to auto sort tables when we entered new data in the table by using the SORT, SORTBY, VLOOKUP, LARGE Functions, VBA Macros, and so on. Here’s an overview of the dataset for our today’s task.

Excel auto sort table


1. Apply the SORT Function to Auto Sort Table in Excel

We can apply the SORT function to auto sort table in Excel. The SORT function can sort data tables in ascending or descending order and in multiple columns. We will learn about these ways to auto-sort data.

1.1 Excel Auto Sort Table in Ascending Order

From our dataset, we want to auto-sort the table in ascending order by applying the SORT function. This is the easiest way to auto sort a table in ascending order by applying the SORT function. Let’s follow the steps to learn!

Step 1:

  • First, create a table with the same heading, and select cell F5.

Excel Auto Sort Table in Ascending Order

Step 2:

=SORT(B5:D14,3,1)
  • Where B5:D14 is the cells array of the SORT function, 3 is the [sort_index] which is column number, and 1 is the [sort_order] which is the ascending order.

Excel Auto Sort Table in Ascending Order

  • After that, simply press Enter on your keyboard and you will get the return of the SORT function.


1.2 Excel Auto Sort Table in Descending Order

Now, we will learn to auto-sort the table in descending order. Let’s follow the instructions below.

Step 1:

  • First of all, make a similar table with the same heading, and select cell F5.

Excel Auto Sort Table in Descending Order

=SORT(B5:D14,3, -1)
  • Where B5:D14 is the cells array of the SORT function, 3 is the [sort_index] which is the column number, and –1 is the [sort_order] which is the descending order.

Excel Auto Sort Table in Descending Order

  • Further, press Enter on your keyboard and you will be able to auto-sort the table by using the SORT function.

Excel Auto Sort Table in Descending Order

Step 2:

  • Now, we will check whether the table is auto sorted or not. From our dataset, we change the Salary of Michael from $63,782.00 to $75,000.00, and press Enter.

Excel Auto Sort Table in Descending Order

  • After pressing the Enter button, we can see that our data table is automatically sorted.


1.3 Excel Auto Sort Table in Multiple Columns

Here, we want to auto-sort multiple columns. Please, follow the steps below.

Step 1:

  • First, create a table with a similar heading and select cell E5.

Excel Auto Sort Table in Multiple Columns

=SORT(B5:C14, {1,2}, {1,1})
  • Where {1,2} is the sort-index and first 1 is the column index number that we want to sort and 2 is the second column index number. {1,1} is the sort_order and 1st and 2nd 1 is the sorted column order in ascending order.

Excel Auto Sort Table in Multiple Columns

  • Now, simply press Enter on your keyboard, and you will get your desired output in the below screenshot.

Excel Auto Sort Table in Multiple Columns

Step 2:

  • To check the auto-sort table, we will replace any data from the table. Let’s say, we replace Ben with Russel, and press Enter.

Excel Auto Sort Table in Multiple Columns

  • Finally, we will be able to auto-sort the table by pressing Enter which has been given in the below screenshot.

Read More: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)


2. Use the SORTBY Function to Auto Sort Table in Excel

In this method, we will learn how to auto sort tables by using the SORTBY function. Using the SORTBY function to auto-sort the table is the easiest way. Let’s follow the steps below to learn!

Step 1:

Use the SORTBY Function to Auto Sort Table in Excel

=SORTBY(B5:D14, D5:D14)

Use the SORTBY Function to Auto Sort Table in Excel

Use the SORTBY Function to Auto Sort Table in Excel

Step 2:

  • Now, we want to check whether it is the auto-sorted table or not. For that, from our dataset, we change the salary of Carlos from $65,208.00 to $70,000.00.

Use the SORTBY Function to Auto Sort Table in Excel

  • After that, press Enter, and you will see that our data table is automatically sorted.

Read More: How to Undo Sort in Excel (3 Methods)


3. Perform the VLOOKUP Function to Auto Sort Table in Excel

Here, we will perform the VLOOKUP function to auto-sort the table in Excel. It is really easy to work to auto-sort the table by performing the VLOOKUP function. Let’s follow the instructions to learn!

Step 1:

=RANK.EQ(D5,D$5:D$14,1)

Perform the VLOOKUP Function to Auto Sort Table in Excel

Perform the VLOOKUP Function to Auto Sort Table in Excel

Perform the VLOOKUP Function to Auto Sort Table in Excel

Step 2:

=VLOOKUP(F5,$B$5:$D$14,2, FALSE)

Perform the VLOOKUP Function to Auto Sort Table in Excel

  • Similarly, perform the VLOOKUP function in the entire column H that has been given below screenshot.

Step 3:

  • Now, check the auto-sort table whether it is sorted or not. To check it, change the salary of Dalton from $ 54,805.00 to $ 80,000.00, and press Enter.

Perform the VLOOKUP Function to Auto Sort Table in Excel

  • After pressing the Enter button, you will be able to auto-sort the table that has been given in the below screenshot.

Related Content: [Solved!] Excel Sort Not Working (2 Solutions)


Similar Readings:


4. Apply the LARGE Function to Auto Sort Table in Excel

In this method, we’ll learn how to auto sort tables by applying the LARGE function. The LARGE function can auto sort data tables in descending order only. Let’s follow the steps below.

Step 1:

Apply the LARGE Function to Auto Sort Table in Excel

=LARGE($D$5:$D$14, C5)
  • We are using the dollar sign to fix the cell’s reference and the selection does not change while moving the function from cell D5 to cell D6.

Apply the LARGE Function to Auto Sort Table in Excel

Apply the LARGE Function to Auto Sort Table in Excel

Step 2:

Apply the LARGE Function to Auto Sort Table in Excel

Step 3:

  • Now, it’s your turn to check that the table is auto sorted or not! We change Carlos’s Salary from $65,208.00 to $ 80,000.00, and press Enter.

  • After that, it goes to the top as it is the max value.

Apply the LARGE Function to Auto Sort Table in Excel

Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)


5. Run a VBA Code to Auto Sort Table in Excel

After learning the above process to auto-sort tables, we will learn how to auto sort tables by using the VBA Macros. Let’s follow the steps below to learn!

Steps:

  • To apply the VBA Code, Right-click on your mouse on the worksheet named VBA from our dataset.

Run a VBA Code to Auto Sort Table in Excel

  • A window named Auto Sort Table pops up, and apply the below VBA Code, and you can 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"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Run a VBA Code to Auto Sort Table in Excel

  • Now, run the VBA Code.

  • After running the VBA code, you can auto-sort the table that has been given below screenshot.

Run a VBA Code to Auto Sort Table in Excel

Read More: How to Sort Range Using VBA in Excel (6 Examples)


Things to Remember

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

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


Conclusion

I hope all of the suitable methods mentioned above to auto-sort tables will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo