Md. Abdur Rahim Rasel
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.
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.
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.
- 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.
- 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.
- Further, press Enter on your keyboard and you will be able to auto-sort the table by using the SORT function.
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.
- 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.
- 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.
- Now, simply press Enter on your keyboard, and you will get your desired output in the below screenshot.
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.
- 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:
- To apply the SORTBY function in our dataset, first select cell F5.
- After selecting cell F5, type the SORTBY function in the Formula Bar. The SORTBY function is,
=SORTBY(B5:D14, D5:D14)
- Hence, simply press Enter on your keyboard and you will get the return of the SORTBY function.
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.
- 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:
- First of all, type the RANK function in cell B5. The RANK function is,
=RANK.EQ(D5,D$5:D$14,1)
- After that, press Enter, and you will get 10 as the return of the RANK function.
- Hence, autoFill the RANK function to the entire column B.
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.
- 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.
- 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:
- How to Create Custom Sort in Excel (Both Creating and Using)
- Sort Column by Value in Excel (5 Methods)
- How to Sort by Color in Excel (4 Criteria)
- Sort Alphabetically In Excel And Keep Rows Together
- How to Sort IP Address in Excel (6 Methods)
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.
- 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.
- After that, press Enter, and you will get $69,738.00 as the return of the LARGE function.
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.
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.
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.
- 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
- Now, run the VBA Code.
- After running the VBA code, you can auto-sort the table that has been given below screenshot.
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.
