In this tutorial, I am going to show you 4 simple methods to auto-sort in Excel when data is entered. You can quickly use these methods even in large datasets to sort the values in any particular order. Throughout this tutorial, you will also learn some important Excel tools and functions that will be very useful in any Excel-related task.
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Product, Storage (units), and Sorted Data. Although we may vary the number of columns later.
1. Using the SORT Function for Ascending Sorting
The SORT function in Excel sorts the values in a range that we supply as input in an ascending or descending order. Let us see how to use this function to auto-sort in Excel when we enter any sort of data.
- First, go to cell E5 and insert the following formula:
- Now, press Enter and this will insert the data after sorting them according to the set criteria.
- Here, if you now change the Storage units value in the first table for any product, this will automatically sort it in the second table.
Read More: How to Auto Sort Multiple Columns in Excel
2. Sorting in Descending Order
We can also use the SORT function to auto-sort in Excel in descending order when data is entered randomly. Follow the steps below to do this.
- To begin with, double-click on cell E5 and enter the below formula:
- Next, press the Enter key and you should get the data in descending order.
- Now, if you change any value in the main data, it will automatically sort it in the new data.
3. Auto Sorting Multiple Columns
If you want to auto-sort multiple columns in Excel when data is entered, then you can follow this method. Below are the detailed steps.
- To begin this method, double-click on cell E5 and insert the formula below:
- Next, press the Enter key and consequently, this will sort out the 2 columns of the main data table even if we change any value from it.
Read More: Excel Auto Sort When Data Changes
4. Applying VLOOKUP Function
The VLOOKUP function in Excel can look up values vertically in a table. In this method, we will see how to apply this function to auto-sort in Excel when we enter any sort of data.
- For this, navigate to cell B5 and insert the formula below:
- Then, press Enter which will decide the rank for all of the data values.
- Next, manually write the rankings in the second table in ascending order as in the image below.
- Then, type in this formula in cell G5:
- After that, press the Enter key and then insert the following formula in cell H5:
- Finally, again press Enter and this will arrange the data in descending order.
Things to Remember
- The SORT function is only available in Microsoft 365.
- The result from this function is a dynamic array so can not change the individual values.
- The first argument of the SORT function has to be a range of cells.
- If no criteria are specified, this function will sort in ascending order by default.
- The SORT function will give a #VALUE error if the sort_index is out of range.
Download Practice Workbook
You can download the practice workbook from here.
I hope that you were able to apply the methods that I showed in this tutorial on how to auto-sort in Excel when data is entered. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.