How to Auto Sort Multiple Columns in Excel (3 Ways)

Auto sort columns without using VBA

Data sorting is an amazing tool to arrange your information systematically as per your requirements. There are many cases when we need to sort multiple columns automatically. Excel has no fixed tool to auto-sort your data. There are some easy ways of auto sorting your data in your worksheet including VBA macro. In this article, we will discuss three ways to auto sort multiple columns in excel.

Read more: Excel Auto Sort When Data Changes 

Download Practice Workbook 

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

Three Ways to Auto Sort Multiple Columns in Excel

To auto sort multiple columns in excel we will discuss three ways of doing it.

  1. Using Excel SORT function to Auto Sort Multiple Columns
  2. Auto Sort Multiple Columns Without Using Excel VBA
  3. Auto Sort Multiple Columns  Using Excel VBA

These methods are discussed below. 

1. Using Excel SORT Function to Auto Sort Multiple Columns

Let’s suppose we have a set of data like the one given below in the picture. We need to sort this scattered data. We need to Sort “Sales Rep.” Name Alphabetically.

Auto sort multiple columns using sort function

We also need to ensure that if one of the values changes, the data range will be re-sorted systematically. We can use the “SORT” function to achieve this type of condition. 

 Now we will solve this problem step by step. 

Step 1:

Apply the “SORT” function. 

Here, 

  • “Array” is your data range (C4:D13)
  • [sort_index] is the column you want to sort on (1)
  • [sort_order] is where you can specify whether you want to arrange in the ascending or the  descending order.

Now, click “Enter”

Step 2:

Look at the result, Our data is sorted respectively to “Sales Rep.” name. 

Step 3: 

Now if you change any name or alphabetic order the data will be sorted automatically. 

2. Auto Sort Multiple Columns Without Using Excel VBA

Generally, to auto sort, multiple columns Excel VBA is a pretty handy method. But for that, you need to learn the Excel VBA. There is a  way where you can do it without using Excel VBA. Let’s discuss it. 

Step 1: 

First, look at this data range in the picture given below. We need to rank out which Sales Rep. has made more Sales.

Auto sort columns without using VBA

Step 2: 

Now we will use the “RANK” function to rank them out. 

Step 3: 

From the result, we can see that the ranks are not given serially. Now, we will auto-sort this Rank and Sales Rep. To do this, create another table, and in the “Rank” column,  input 1 to 10 serially.

Step 4 : 

We will now use the  “INDEX” and “MATCH” functions. 

=INDEX($C$5:$C$14,MATCH(G5,$E$5:$E$14,0))

Here, 

  • “Array” is the range of cells from where you want to return value (C5:C14). Block it by pressing F4
  • To pick the row number, use the “MATCH” function within the “INDEX” function.
  • For “lookup_value” select the Rank number (G5)
  • For “lookup_array” select the array (E5:E14). Block it by pressing F4
  • Select “Exact match” 

Click Enter.

Step 5 :

Now we have got out auto-sorted data. If you change the data in the raw table, the value will auto-sort in the sorting table 

3. Auto Sort Multiple Columns  Using Excel VBA

You can easily auto-sort your data if you know Excel VBA. Let’s see how.

Step 1:
Make a data set. 

Auto sort columns using VBA

Step 2: 

Open the VBA window 

Step 3 : 

Use this VBA code. You need to change the red-marked area in the given picture according to your workbook 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"), _

Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

End If

End Sub 
In this VBA code, 
  • B:B means it will auto-sort Column  B 
  •  B1 is the 1st cell in Column B
  • B2 is the 2nd cell in Column B

Step 4: 

Finally, we have our required result.

Things to Remember 

➤ The “SORT” function is only available for Excel 365.

➤ Remember to Block your data range when you are using the “INDEX” function.

➤The ”Column_number” and “Area_Number” are optional when you are using the number 2  method. 

➤When you use the mentioned VBA code, you need to put down your own workbook value in the code.

Conclusion 

Using these methods, you can easily auto sort multiple columns in excel. If you have any confusion or query, feel free to comment. Don’t forget to support.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo