6 Ways to Auto Sort When Data Changes in Excel

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.

Dataset of Excel Auto sort when Data Changes.

Method

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.

SORT function

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.
=SORT(B6:C11,2,-1)

πŸ”¨ 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.

Auto sort when data changes in descending order in Excel

SORT function

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.
=SORT(B6:C11,2,1)

πŸ”¨ 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.

Auto sort when data changes in ascending order in Excel

πŸ“• Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula

Method

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.
=SORT(B6:C11,{1,2},{1,1})

πŸ”¨ 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.

SORT function to sort automatically when data changes in Excel

πŸ“• Read More: How to Sort Alphabetically in Excel with Multiple Columns

Method

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.
=RANK.EQ(D6,$D$6:$D$11)

Ranking to sort automatically in Excel

  • 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.
=VLOOKUP(F6,$B$6:$D$11,2,FALSE)

data sorting using VLOOKUP in Excel

  • Similarly to call data from the 3rd column, insert the formula at H6.
=VLOOKUP(F6,$B$6:$D$11,3,FALSE)

Use of VLOOKUP function

πŸ”¨ 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

Method

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.
=SORT(FILTER(B6:C11,C6:C11>=F3), 2)

πŸ”¨ 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.

SORT and FILTER function to sort out automatically under a criteria.

πŸ“• Read More: Sort Multiple Columns Independently of Each Other in Excel

Method

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.
=INDEX(SORT(B6:C11, 2, -1), SEQUENCE(3), {1,2})

πŸ”¨ 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.

INDEX and SORT function to sort out 3 highest numbers.

Similarly, you can also get the 3 lowest marks ascendingly.

INDEX and sORT function to determine 3 lowest numbers to Auto Sort When Data Changes in Excel

πŸ“• Read More: 4 Quick Ways to Create Custom Sort List in Excel

Method

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.
=INDEX(SORT(B6:C11, 2, -1),F10, {1,2})

πŸ”¨ 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.

INDEX and SORT function to auto sort 2nd highest when data changes in Excel

Similarly, You can 2nd the lowest value using the formula. Let’s try out and match the result.

INDEX and SORT function to Auto Sort When Data Changes in Excel

πŸ“• 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


Related Articles

(Visited 81 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo