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.

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

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

**๐ 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.

**๐ 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)**

- 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)**

- Similarly to call data from the
**3rd**column, insert the formula at**H6**.

**=VLOOKUP(F6,$B$6:$D$11,3,FALSE)**

**๐จ**** 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.

**๐ 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.

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

**๐ 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.

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**