How to Auto Sort Table in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Auto Sort Table in Excel (5 Suitable Ways)

Let’s say, we have a dataset where some Sales representatives’ name and their salary are given in columns B, and C respectively. We will learn how to auto-sort tables when we enter 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 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:

  • Now, type the SORT function in the Formula Bar. The SORT function in the Formula Bar is,
=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

  • Hence, type the SORT function in the Formula Bar. The SORT function in the Formula Bar is,
=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

  • Hence, type the SORT function in the Formula Bar. The SORT function is,
=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 Auto Sort Multiple Columns in Excel


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:

  • To apply the SORTBY function in our dataset, first select cell F5.

Use the SORTBY Function to Auto Sort Table in Excel

  • After selecting cell F5, type the SORTBY function in the Formula Bar. The SORTBY function is,
=SORTBY(B5:D14, D5:D14)

Use the SORTBY Function to Auto Sort Table in Excel

  • Hence, simply press Enter on your keyboard and you will get the return of the SORTBY function.

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: Excel Auto Sort when Data is Entered


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

  • After that, press Enter, and you will get 10 as the return of the RANK function.

Perform the VLOOKUP Function to Auto Sort Table in Excel

  • Hence, autofill the RANK function to the entire column B.

Perform the VLOOKUP Function to Auto Sort Table in Excel

Step 2:

  • Now, we apply the VLOOKUP function in cell G5. The VLOOKUP function in cell G5 is,
=VLOOKUP(F5,$B$5:$D$14,2, FALSE)

  • Again, press Enter, and you will get Russel as the output of the VLOOKUP function.

  • Further, autoFill the VLOOKUP function to the entire column G.

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


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:

  • Select cell E5 first to apply the LARGE function.

Apply the LARGE Function to Auto Sort Table in Excel

  • Now, write down the LARGE function in the selected cell.
=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

  • After that, press Enter, and you will get $69,738.00 as the return of the LARGE function.

Apply the LARGE Function to Auto Sort Table in Excel

Step 2:

  • Hence, autoFill the LARGE function to the entire column E.

  • After auto-filling the LARGE function in column E, you will be able to auto-sort data tables in descending order by applying the LARGE function.

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


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


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 you use the VBA code, you need to insert your own worksheet value in the code.


Download Practice Workbook

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


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


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

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.
Md. Abdur Rahim Rasel
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo