# Auto Sort When Data is Entered in Excel (3 Methods) Sorting data automatically in excel is really useful when you are handling a wide range of data. Excel has no definite tool to auto-sort your data. In this article, we will discuss how to sort your data automatically when you insert a new value in your data range. Today, we will learn three methods of auto sorting data.

## Three Different Methods to Auto Sort When Data is Entered in Excel

### 1. Using The “SORT” Function to Auto Sort Data

The “SORT” function is used to rearrange your range of data in ascending or descending order. Today, we will learn three types of auto sorting data.

#### i. Auto Sort Data in Ascending Order

A data set is given like this one in the picture. We need to sort them in ascending order. Step-1:

Create another table where you want to put your sorted data. Step-2:

Apply the “SORT” function.

Here,

• “Array” is your data range (B5:C14)
• [sort_index] is the column you want to sort on (2)
• [sort_order] is where you can specify the order (1)

Press “Enter”. We got out auto-sorted data in ascending order. Now if you change any value the data will auto-sort according to the value. #### ii. Auto Sort Data in Descending Order

Step-1:

For descending order, go to the function tab and change the order from 1 to -1. And, you will get your auto-sorted data in descending order. Step-2:

Now we want to check whether it is auto sorted or not. For that, we change the value to “Kiwi” from 800 to 3000. Press “Enter”. Look, our data range is automatically sorted. #### iii. Auto Sort Multiple Columns When Data is Entered in Excel

Now we want to auto sort multiple columns. Let’s follow these steps.

Step1:

Apply the “SORT” function to auto sort multiple columns.

Here,

• “Sort_index” is {1,2}, where 1 is the first column index number and 2 is the second column index number that we want to sort.
• “Sort_order” is the order {1,1}, where the 1st and 2nd column is to be sorted in ascending order. Step-2:

Now Press “Enter”. We got our sorted data. Step-3:

To check, whether it can auto sort or not, change a name in the data range. Now, we can see that the data is auto sorted. ### 2. Using The “VLOOKUP” Function to Auto Sort When Data is Entered

We can use the “VLOOKUP” function for auto sorting our data. Here’s how,

Step-1:

Take a set of data like this one in the picture. Step-2:

Create a new column named “Rank” and rank the value by using the “RANK” function.

=RANK.EQ(E4,\$E\$4:\$E\$13) The data is ranked by their values. Step-3:

Now, Copy the titles of the data and paste them besides. In the “Rank” column, insert the sequence numbers like the screenshot. Step-4:

Apply the “VLOOKUP” function. Here,

• “Lookup_Value” is the number you want to find in the data (G4).
• “Table_Array” is your data range(C\$4:E\$13).
• “Col_index_number” is the column number where you want to get your value (2).
• [range_lookup] is to find the exact number(FALSE).

=VLOOKUP(G4,C\$4:E\$13,2,FALSE) Press “Enter”. Now we got our sorted value according to the “lookup_value”. Step-5:

To check the auto sorting, change any value in the “Table array”. And press “Enter”. Our data table is auto sorted. ### 3. Using VBA to Auto Sort

In this method, we will use the VBA macro to auto sort our data.

Step-1:
Open the VBA window Step-2:

Apply the VBA code. You need to make changes in the “Red-marked” area according to your column value. You can copy this code from here,

``````Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range("B:B")) Is Nothing Then

Range("B1").Sort Key1:=Range("B2"), _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

End If

End Sub
``````

Where,

• B:B refers that it will auto-sort Column B
• B1 is the 1st cell in Column B
• B2 is the 2nd cell in Column B

Step-3:

Finally, we have the desired data table. If you change any value from the table the data will be auto sorted. ## Quick Notes

✅ The “SORT” function is only available for “Excel 365”. You won’t be able to use this function if you do not have this version of excel.

✅While using the “SORT” function to auto sort multiple columns, the first column will auto sort first. The second column will sort after the previous column is sorted.

✅While applying the “VLOOKUP” function, remember to “Block” the “Table_Array”.

✅When you use the  VBA code, you need to insert your own worksheet value in the code.

## Conclusion

Today we have discussed three methods to auto-sort when data is entered in excel. Hope this article is useful to you. If you have any queries or confusion, you are welcome to comment. #### Asikul Himel

1. Reply Kanhaiyalal Newaskar. Sep 5, 2021 at 11:14 AM

Dear Sir, 5th Sept.2021.

Very clearly and nicely you showed this article. Very easy to understand.
Thanking you for the efforts taken.
Awaiting eagerly to receive such notes and articles in future too.
I remain.

• Reply Asikul Himel Sep 5, 2021 at 8:34 PM

Thank you for your kind thoughts. This will encourage us to contribute more.

2. Reply Thank you, Asikul, I learned a new trick in Excel today. Just keep filling my Excel tool box.

• Reply Asikul Himel Sep 5, 2021 at 8:35 PM

You are most welcome! 