Excel Auto Sort When Data Changes (9 Examples)

In this article, we’ll demonstrate how to use the SORT function or combine it with other functions to sort data automatically when data is changed later.

Example 1 – Auto Sort in 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 use the SORT function to sort the first table by those sales values in descending order, and then change a sales value to see if the table auto sorts.

The generic formula of the SORT function is:

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

Steps:

In the output Cell E5 enter the following formula:

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

Press Enter .

The function will return a sorted array with the sales values in descending order.

Change any of the sales data in the first table, for example in Cell C9 type 4000.
Press Enter.

The sales value of Jordan is in a new row based on the position of the sales value.

Example 2 – Auto Sort in Ascending Order When Data Changes

To sort the given table in ascending order, simply change the third argument ([sort_order]) to ‘1’ (which denotes ascending order) in the previous formula.

The required formula in Cell E5 is now:

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

After pressing Enter, the entire table data is sorted by sales values in ascending order.

Change any sales value in Column C, and the sorted table will rearrange itself accordingly.

Example 3 – Auto Sort by Column When Data Changes in Excel

Let’s transpose all data in the previous table, so that the columns are converted into rows. We’ll need to sort the table data by the columns in Row 5, where the sales data are situated after being transposed.

The required formula to sort the table by columns in Cell C7 is:

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

Press Enter and the sales data will be in descending order in Row 8.

Example 4 – Auto Sort Columns by Different Orders When Data Changes

In the modified dataset below, add a new column with the header Counter. We’ll now consider both the Counter and Sales columns, and sort the entire table data using the criteria of ascending and descending orders in the Counter and Sales columns respectively.

For example, after sorting, the letter ‘A’ will be shown three times in a row at the top of the Counter column, and the corresponding sales values will be sorted in descending order.

In the output Cell F5, the required formula is:

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

Press Enter and the sorted table will appear. Change any sales data in Column D, and the sorted table will automatically update.

Example 5 – Auto Sort by Filtering When Data Changes in Excel

The 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 in a specified order.

For example, from the following dataset, we’ll filter the rows for sales values greater than or equal to $ 2000, then use the SORT function to sort those extracted rows in descending order.

The required formula in the output Cell E5 is:

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

Press Enter and the final output with the given conditions is as shown in the picture below. Alter any data in the Sales column in the first table to see the changes in the sorted table.

Example 6 – Auto Sort Top 3 When Data Changes in Excel

By combining the INDEX, SORT, and SEQUENCE functions, we can extract three (or any number of) rows containing the top sales values, then sort them in descending order.

The required formula in the output Cell E5 is:

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

After pressing Enter, the top 3 sales values in descending order appear in the new table.

How Does the Formula Work?

The SORT function inside the INDEX function defines the array to sort.
The SEQUENCE function defines the number of rows that will 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 output with those two columns from the primary table.

Example 7 – Auto Sort Bottom 3 When Data Changes in Excel

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

The formula required in the output Cell E5 is therefore:

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

After pressing Enter, the lowest or bottom three sales values appear in ascending order in the output table.

Example 8 – Auto Sort and Get a Value from a Specific Position

By combining the INDEX and SORT functions only, we can extract the second-highest and second-lowest sales values from the table.

The required formula in Cell E7 is as follows:

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

After pressing Enter, the second-highest sales value is returned.

To get the second-lowest sales value, simply change the ‘sort order’ in the SORT function. The required formula in Cell E11 is therefore:

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

Change any sales data in Column C, and if it matches the criteria of being the second-highest or second-lowest value, the data in the output cells change accordingly.

Example 9 – Auto Sort with Table While Entering New Data

All the methods discussed above are valid for changes to existing data only. But if you append new data to the given table, the sorted array will not change. To automatically update the sorted array to account for new data entries in the primary table, we have to convert the primary table into a filtered table format.

In the following picture, there is no change in the sorted table after the input of new data in the primary table.