Jonayed
Sometimes we need to auto-sort when data is entered in Excel. In this article, we are going to show you how to auto-sort when data is entered in Excel with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download the free workbook from here:
Learn to Auto Sort When Data is Entered in Excel with 5 Ways
While writing this article, “Excel Auto Sort When Data is Entered”, we’ve considered a dataset that contains approximately 3 columns and 11 rows. In this dataset, we added Name and Total Marks to make you better understand. But if you want, you can change the entities and put the values of your own.
1. Auto Sort Using SORT Function Without Macro
Using the SORT function is a very useful procedure for Excel auto sort when data entered. You can do it in two ways. We have discussed them both. You can also find them by searching- Auto Sort Excel Formula.
1.1 In Ascending Order
If you want to auto sort when data entered in Excel then this portion is for you. You can do it by following the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we have developed a table to show you the Sorted Condition.
- Now select cell E6.
- Secondly, write down the following formula there.
=SORT(B6:C11,2,1)
🔨 Formula Breakdown
Here,
👉 2 implies the column number.
👉 1 implies the ascending order to be sorted.
- Now tap Enter.
- Finally, you will get the desired result on those cells.
Undoubtedly, Excel auto-sort when data entered using ascending order sorting is a very useful method.
📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula
1.2 In Descending Order
You can also use the SORT function in descending order. You just have to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6.
- Secondly, put the given formula on that cell.
=SORT(B6:C11,2,-1)
🔨 Formula Breakdown
Here,
👉 2 is the number of columns.
👉 -1 implies the descending order to be sorted.
- Thirdly, press Enter.
- Finally, the sorted values will appear to you.
Truly, this is a very easy way to Excel auto-sort when data entered.
📕 Read More: Sort Multiple Columns Independently of Each Other in Excel
2. Auto Sort Multiple Columns When Data Changes in Excel
In Excel, you can also auto-sort multiple columns. This technique is a very unique technique of Excel. You can also find this by searching- Excel Auto Sort Column. You just have to perform the following steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cell E6.
- Secondly, write the given formula there.
=SORT(B6:C11,{1,2},{1,1})
- Now tap Enter.
- Consequently, you will get the required values.
Finally, this procedure is very easy to Excel auto-sort when data entered.
📕 Read More: 6 Ways to Auto Sort When Data Changes in Excel
3. Using SORT and FILTER Functions Together
In Excel, you can auto-sort when data enter by using the SORT function and FILTER function together. It is not a very difficult task. You just have to maintain the given steps to perform this process.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, click on cell E6.
- Now write the following formula there.
=SORT(FILTER(B6:C11,C6:C11>=C10),2)
🔨 Formula Breakdown
Here,
👉 FILTER(B6:C11,C6:C11>=C10) implies we will filter the cells C6 to C11 whose values Greater than or Equal to C10.
👉 SORT(FILTER(B6:C11,C6:C11>=C10),2) implies that the sorting will take place for the filtered values.
- Thirdly, tap Enter.
- Lastly, you will get the values that are auto-sorted. You see that the value of C7 is not included here as its value is not greater than or equal to C10.
Undoubtedly, using SORT and FILTER functions together to Excel auto-sort when data is entered is a very user-friendly method to perform.
📕 Read More: How to Sort by Last Name in Excel
4. Applying SORT, INDEX, and SEQUENCE Functions Combined
If you intend to auto-sort in Excel when data entered by applying the SORT, INDEX, and SEQUENCE functions combined, then this portion is for you. You may also find it by searching- Excel Auto Sort without Macro. You can easily do it by maintaining the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cell E6.
- Now put the given formula on that particular cell.
=INDEX(SORT(B6:C11,2,1),SEQUENCE(6),{1,2})
🔨 Formula Breakdown
Here,
👉 SORT(B6:C11,2,1) implies that the sorting operation will take place in ascending order.
👉 SEQUENCE(6) refers that the sorting will go sequentially (1,2,3,4,5,6).
👉 INDEX(SORT(B6:C11,2,1), SEQUENCE(6),{1,2}) means indexing will happen sequentially after the sorting in ascending order.
- Thirdly, press Enter.
- Finally, the sorted values will appear to you as well.
Truly, this is a very useful and user-friendly process in Excel.
📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel
5. Auto Sort Macro in Excel Using VBA Code
If you want to auto sort in Excel using VBA Macro, then this part is for you. You can do this very easily. You just have to maintain the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, go to Excel.
- Secondly, press Alt+F11. It will open the VBA window.
- Thirdly, click on Sheet 8 (VBA).
- Now you will get another window there.
- Fourthly, copy the following code and paste it on the new window.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B6").Sort Key1:=Range("B7"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub- Here you need to change Range(“B6”) and Range(“B7”) to your required cell from which you want to start sorting.
- Finally, you will find the desired result of auto-sort in Excel.
- You can see the GIF given below.
Undoubtedly, using VBA Macro to auto-sort in Excel is a very useful technique indeed.
📕 Read More: How to Sort Alphabetically in Excel with Multiple Columns
📄 Important Notes
While performing the processes mentioned above, you need to be aware of these things:
🖊️ You should be careful while using the SORT function in Excel.
🖊️ You need to define the cells carefully when you intend to use the Excel functions.
🖊️ You have to be aware of auto-sorting multiple columns.
🖊️ While using VBA Macro you need to be careful about defining the range of the code.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Firstly, we’ve used the SORT function to Excel auto-sort when data entered. You can use this function in two orders:
1. Ascending order.
2. Descending order.
📌 Secondly, we’ve adopted auto-sorting for Multiple Columns.
📌 Thirdly, we applied the SORT function and FILTER function together in Excel.
📌 Fourthly, we have used SORT, INDEX, and SEQUENCE functions.
📌 Finally, we’ve tried to show you the procedures of how to auto-sort in Excel using VBA Macro.
Conclusion
We wish that you would be able to auto sort when data is entered in Excel using this article. As several methods have been shown here, you can easily follow which one is comfortable for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
