MD Tanvir Rahman
Sorting from data congestion is a real challenge nowadays. In real life, there is a lot of data. Auto Sort When Data Changes in Excel is irreplaceable to get specific data within a few moments. Every company uses Excel to monitor sales data, production data, biographic data, etc., and Excel auto sort when data changes are one of the key features to get desired data.
📁 Download Excel File
To practice and get the overview, please download the Excel file from here:
Learn to Auto Sort When Data Changes in Excel with These 6 Approaches
Auto sorting can be done in some specific ways. We tried every possible way to show you how to make auto sort when data changes in excel. We find out 8 different approaches to auto sort the data from a congested data sheet. Here we consider a Dataset containing Student Names and Marks. In these 6 approaches, we are going to use SORT, VLOOKUP, FILTER, and INDEX functions.
1. Auto Sort Using SORT Function in Excel
We are going to use the SORT function to descend (Top to Bottom) or ascend (Bottom to Top) the order. Generally, The SORT function sorts the elements of a particular range in descending or ascending.
1.1 In Descending Order
To descend the order SORT function can be used. Follow the below steps to get the highest to lowest values.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell i.e. E6.
- Using the SORT function input the formula in cell E6.
🔨 Formula Breakdown
The syntax of the SORT function is as follows: SORT(range,by_column,sort_order)
Here (B6:C11) symbolizes the entire range of data.
2 indicates the 2nd column of the table.
-1 refers to the descending order of sorted data.
- Press Enter and get the descending order.
1.2 In Ascending Order
The SORT function is also used to ascend the order. Follow the below steps to get the lowest to highest values.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell i.e. E6.
- Using the SORT function input the formula in cell E6.
🔨 Formula Breakdown
SORT function syntax is as follows: SORT(range,by_column,sort_order)
Here (B6:C11) symbolizes the entire range of data.
2 indicates the 2nd column of the table.
1 refers to the ascending order of sorted data.
- Press Enter and get the ascending order.
📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula
2. Auto Sort Multiple Columns Using SORT Function
To sort multiple columns automatically, we can use the SORT function and it changes with the values we enter from time to time. Follow the below steps to get the lowest to highest values.
⬇️⬇️ STEPS ⬇️⬇️
- Navigate a blank cell i.e. E6.
- Using the SORT function input the formula in cell E6.
🔨 Formula Breakdown
From the formula, the syntax of the SORT function is as follows: SORT(range,{column},{by_column,sort order}
Here (B6:C11) symbolizes the entire range of data.
{1,2} indicates the 1st and 2nd column of the table.
{1,1} refers to the ascending order alphabetically sorted data of the 1st column.
- Press Enter and get alphabetically ascending order.
📕 Read More: How to Sort Alphabetically in Excel with Multiple Columns
3. Auto Sort When Data Changes Using VLOOKUP Function in Excel
We can use the VLOOKUP function to look up Student names based on their marks. VLOOKUP function tells you what and where to look for sorting the data. Check the below procedure and formula breakdown.
⬇️⬇️ STEPS ⬇️⬇️
- We need to use an additional column to rank up according to students’ performance.
- After that, navigate B6 and impute a formula to rank.
- After ranking use the VLOOKUP function to get in ascending order.
- Insert a formula containing the VLOOKUP function to call data from the 2nd column of the table.
- Similarly to call data from the 3rd column, insert the formula at H6.
🔨 Formula Breakdown
Firstly we made a ranking based on a range containing students’ marks using the formula RANK.EQ(D6,$D$6:$D$11) where the range is selected from D6 to D11.
Then, VLOOKUP syntax is, VLOOKUP(lookup_value, table_array, column_index_num,[TRUE/FALSE])
Next, Lookup_value= G6 and H6 respectively where we want to get the value of the student name and marks.
After that, table_array= $B$6:$D$11; which is the entire data.
However, column_index_num= 2nd and 3rd column from where we will be able to get the sorted data.
📕 Read More: How to Sort by Last Name in Excel
4. Using SORT and FILTER Functions to Auto Sort with Criteria in Excel
Generally, the FILTER function is used to filter some data based on specific criteria. By combining SORT and FILTER Functions we can also eliminate unnecessary data.
⬇️⬇️ STEPS ⬇️⬇️
- Suppose, we need the names of students who obtained a minimum of 60 marks.
- We input data of 60 marks at cell F6.
- At E6, insert a formula containing SORT and FILTER functions.
🔨 Formula Breakdown
Initially, the Syntax of the formula is SORT(FILTER(array,data_range),by_column).
Secondly, B6:C11 is the entire range of data.
Thirdly, At F3 we imputed a data of 60.
After that, C6:C11>=F3 logic finds the data that are above 60=F3 which is the 2nd column.
Finally, Filtering by the FILTER function entire data is sorted by the SORT function.
📕 Read More: Sort Multiple Columns Independently of Each Other in Excel
5. Using SORT and INDEX Functions to Get N Highest or Lowest Values Automatically
We can also get the highest or lowest ‘N’ values using a combination of the SORT and INDEX functions. The INDEX function returns the value within a specific range or table. on the other hand, to sort out in ascending or descending order SORT function is used.
⬇️⬇️ STEPS ⬇️⬇️
- Select a blank cell i.e. E6.
- Insert the following formula at E6 containing INDEX and SORT functions.
🔨 Formula Breakdown
Syntax of the formula is, INDEX(SORT(array,column_no,sort_order), SEQUENCE(array_constant),{column_order})
(B6:C11, 2, -1) directs the range from B6 to C11. 2,-1 indicates the 2nd column of the table in descending order.
SEQUENCE(3) refers to the 3 vertical constant values of an array.
{1,2} is the column number from where it will be imputed maintaining sequences.
- Press Enter and get the top 3 highest marks.
Similarly, you can also get the 3 lowest marks ascendingly.
📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel
6. Auto Sort Out Specific Value Using INDEX and SORT Functions
Sometimes we need to know the 2nd or 3rd values of the table. Using INDEX and SORT functions, we can also sort out specific desired values. Let’s follow the procedure.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, Take a blank cell i.e. E6.
- Secondly, Insert the following formula at E6 containing INDEX and SORT functions.
🔨 Formula Breakdown
Initially, the Syntax of the formula is INDEX(SORT(array,column_no,sort_order),reference_cell, {column_order}).
Then, (B6:C11, 2, -1) indicates that the range of data is B6 to B11 and the 2nd column range is descending sort.
Next, In sorting, 1 indicates ascending. Meanwhile, -1 indicates descending order.
After that, F10 is the reference that is desired. As we show you, we find out the 3rd value from the top.
Finally, {1,2} is the column order which is returned by the INDEX function.
- Finally, Press Enter and get the 3rd highest mark with student’s name.
Similarly, You can 2nd the lowest value using the formula. Let’s try out and match the result.
📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel
📄 Important Notes
🖊️ Keep in mind that the SORT function is only available on MS Excel 2022.
🖊️ Choose Range and Column carefully while using the INDEX function as it returns its value.
🖊️ Carefully check logic during filtering using the FILTER function.
📝 Takeaway from This Article
📌 In this article, we discussed 6 different approaches with their syntax and working procedure.
📌 We demonstrated SORT, INDEX, FILTER, and VLOOKUP functions.
Conclusion
In this article, we tried our best to discuss every possible way to sort automatically when we change data. I hope this article will be very helpful to you. For queries or any suggestions, don’t forget to comment below. Also for more related articles, please visit www.ExcelDen.com
