Nehad Ulfat
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.
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.
