Auto Sort When Data is Entered in Excel (3 Methods)

auto sort using sort function

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.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

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.

auto sort using sort function

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.

auto sort using sort function

Step-2:

Now Press “Enter”. We got our sorted data.

auto sort using sort function

Step-3:

To check, whether it can auto sort or not, change a name in the data range.

auto sort using sort function

Now, we can see that the data is auto sorted.

auto sort using sort function

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.

auto sort using vlookup function

Step-2:

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

=RANK.EQ(E4,$E$4:$E$13)

auto sort using vlookup function

The data is ranked by their values.

auto sort using vlookup function

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.

auto sort using 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)

auto sort using vlookup function

Press “Enter”. Now we got our sorted value according to the “lookup_value”.

auto sort using vlookup function

Step-5:

To check the auto sorting, change any value in the “Table array”.

auto sort using vlookup function

And press “Enter”. Our data table is auto sorted.

auto sort using vlookup function

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

auto sort using vba macro

Step-2:

Apply the VBA code. You need to make changes in the “Red-marked” area according to your column value.

auto sort using vba macro

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"), _

Order1:=xlAscending, Header:=xlYes, _

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.

auto sort using vba macro

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.

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

    Kanhaiyalal Newaskar

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

Leave a reply

ExcelDemy
Logo