In Microsoft Excel, there are numerous procedures to auto-sort a table or an array if someone changes data. In this article, you’ll learn **how you can use the SORT function** or combine it with other necessary functions to sort data automatically when data is changed later.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we have used to prepare this article.

**9 Suitable Methods to Auto Sort When Data Changes in Excel**

**1. Auto Sort in a Descending Order When Data Changes**

In the following dataset, there are sales values of some salesmen in the first table. In the second table, we’ll sort the first table by those sales values in descending order and then change a sales value to see if the table auto sorts.

We’ll use the **SORT** function here to sort the table data first. The generic formula of the SORT function is:

**=SORT(sort_array, [sort_index], [sort_order], [by_col])**

**📌**** Step 1:**

➤ Select the output **Cell E5** in the second table and type the following formula:

`=SORT(B5:C12,2,-1,FALSE)`

➤ Press **Enter **and the function will return a sorted array with the sales values in descending order.

**📌**** Step 2:**

➤ Now change any of the sales data in the first table, e.g: In **Cell C9**, type 4000 and press **Enter**.

Like in the picture below, you’ll find the sales value of Jordan in a new row based on the position of the sales value.

**Read more:** **How to Auto Sort Multiple Columns in Excel**

**2. Auto Sort in an Ascending Order When Data Changes**

Similarly, we can also apply the function to sort the given table in ascending order. In that case, we have to simply change the third argument **([sort_order])** to **‘1’** which denotes the option of ascending order.

So, the required formula in **Cell E5** will be now:

`=SORT(B5:C12,2,1,FALSE)`

After pressing **Enter**, you’ll get the entire table data sorted by sales values in ascending order.

Now if you change any sales value in **Column C**, the sorted table will show the output at once.

**3. Auto Sort by Column When Data Changes in Excel**

Let’s transpose all data in the previous table. So, the columns have been converted into rows now. We have to sort the table data by the columns in **Row 5** now where the sales data are lying.

The required formula to sort the table by columns in **Cell C7** will be:

`=SORT(C4:I5,2,-1,TRUE)`

Press **Enter **and you’ll find the sales data in descending order in **Row 8**.

**Read more:** **How to Sort Rows in Excel**

**4. Auto Sort Columns by Different Orders When Data Changes**

In practical scenarios, it’s very common to **sort multiple columns** by different orders. For example, in the modified dataset below, a new column with the **Counter **header has been added. So, we’ll now consider both **Counter **and **Sales **columns and sort the entire table data by assigning the criteria of ascending and descending orders in the **Counter **and **Sales **columns respectively.

For example, the letter** ‘A’** will be shown thrice in a row at first in the **Counter **column and the corresponding sales values will be sorted in descending order.

In the output **Cell F5**, the required formula will be then:

`=SORT(B5:D12,{2,3},{1,-1})`

Press **Enter **and you’ll be displayed the sorted table right away. Now change any sales data in **Column D**, you’ll notice the instant update in the sorted table.

**5. Auto Sort by Filtering When Data Changes in Excel**

**FILTER** function simply filters a range or an array based on the given conditions or criteria. By using the **FILTER **function inside the **SORT **function, we can extract a range of rows from the primary table based on the given condition(s) and then sort them by a specified order.

For example, from the following dataset, we’ll filter the rows where sales values are more than or equal to** $ 2000** and then the **SORT **function will sort those extracted rows in descending order.

The required formula in the output **Cell E5** will be:

`=SORT(FILTER(B5:C12,C5:C12>=2000),2,-1)`

Now press **Enter **and you’ll find the final output with given conditions as shown in the picture below. You can alter any data in the **Sales **column in the first table to see the changes in the sorted table.

**Similar Readings:**

**Auto Sort When Data is Entered in Excel (3 Methods)****How to Sort Rows by Column in Excel (4 Methods)****Sort Column by Value in Excel (5 Methods)**

**6. Auto Sort Top 3 When Data Changes in Excel**

By combining **INDEX****, SORT, and ****SEQUENCE** functions, we can extract three or any number of rows where top sales values are present and then sort them in descending order.

To get the rows with the top three sales values from the primary table and then to sort them in descending order, the required formula in the output **Cell E5** will be:

`=INDEX(SORT(B5:C12, 2, -1), SEQUENCE(3),{1,2})`

After pressing **Enter**, you’ll find the top 3 sales values in descending order in the new table.

**🔎**** How Does the Formula Work?**

- The
**SORT**function inside the**INDEX**function defines the sorted array. - The
**SEQUENCE**function defines the number of rows that have to be extracted from the array. - In the third argument
**([column_num])**of the**INDEX**function, the index numbers of both columns have been assigned to obtain the final output with those two columns present in the primary table.

**7. Auto Sort Bottom 3 When Data Changes in Excel**

To get the bottom or lowest three sales values, we have to make a minor change in the formula that was used in the previous section. As we have to extract the sales values in ascending order here, the third argument** ([sort_order]) **of the **SORT **function will be** ‘1’** now.

So, the final formula in the output **Cell E5** should be:

`=INDEX(SORT(B5:C12, 2, 1), SEQUENCE(3),{1,2})`

After pressing **Enter**, you’ll get the lowest or bottom three sales values in ascending order at once in the output table.

**8. Auto Sort and Get a Value from a Specific Position**

By incorporating **INDEX **and **SORT **functions only, we’ll now extract the second-highest and second-lowest sales values from the table.

To get the second-highest sales value from the given table data, we have to type the following formula in **Cell E7:**

`=INDEX(SORT(B5:C12,2,-1),2,{1,2})`

After pressing **Enter**, you’ll be shown the second-highest sales value right away.

And to get the second-lowest sales value, we have to simply change the ‘sort order’ in the SORT function. So, the required formula to obtain the second-lowest sales value in **Cell E11** will be:

`=INDEX(SORT(B5:C12,2,1),2,{1,2})`

You can change any sales data now in **Column C** to find if it matches the criteria of second-highest or second-lowest value in the output cells.

**9. Auto Sort with Table While Entering New Data in Excel**

All methods mentioned above are valid for data changes only. But if you add new data under the given table, you’ll not find any change in the sorted array. But to make an instant update in the sorted array for new data entries in the primary table, we have to convert the primary table into a filtered table format.

In the following picture, you’re seeing no change in the sorted table after the input of new data in the primary table. But we’ll now see how to make the sorted table auto-update with a new input in the primary table.

**📌**** Step 1:**

➤ Select the range of cells of the primary table **(B4:C12)** first.

➤ Press **CTRL+T** to convert the chart into a filtered table format.

**📌**** Step 2:**

➤ In **Row 13**, add new data with the name of a salesman and the corresponding sales value.

You’ll notice the instant update in the sorted table as shown in the picture.

**Concluding Words**

I hope all of these methods mentioned above will now help you to apply them in your Excel spreadsheet while working with auto sorting. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.