Excel Auto Sort When Data Changes (9 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Auto Sort When Data Changes in Excel (9 Suitable Methods)


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

auto sort in descending order when data changes in excel

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

auto sort in descending order when data changes in excel

📌 Step 2:

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

auto sort in descending order when data changes in excel

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.

auto sort in descending order when data changes in excel

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.

auto sort in ascending order when data changes in excel


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.

auto sort by column when data changes in excel

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.

auto sort by column when data changes 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 the 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.

Auto Sort Columns by Different Orders When Data Changes in excel

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.

Auto Sort Columns by Different Orders When Data Changes in excel


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

Auto Sort by Filtering When Data Changes in Excel

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

Auto Sort by Filtering When Data Changes in Excel

Read More: How to Auto Sort in Excel When Data Is Entered


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.

Auto Sort Top 3 When Data Changes in Excel

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

Auto Sort Bottom 3 When Data Changes in Excel


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.

Auto Sort and Get a Value from a Specific Position when data changes in excel

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.

Auto Sort and Get a Value from a Specific Position when data changes in excel

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 the second-highest or second-lowest value in the output cells.

Auto Sort and Get a Value from a Specific Position when data changes in excel


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

Auto Sort with Table While Entering New Data in Excel

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

Auto Sort with Table While Entering New Data in Excel

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

Auto Sort with Table While Entering New Data in Excel


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Auto Sort in Excel | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo