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.
- Using Excel SORT function to Auto Sort Multiple Columns
- Auto Sort Multiple Columns Without Using Excel VBA
- 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.
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.
Apply the “SORT” function.
- “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.
Look at the result, Our data is sorted respectively to “Sales Rep.” name.
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.
First, look at this data range in the picture given below. We need to rank out which Sales Rep. has made more Sales.
Now we will use the “RANK” function to rank them out.
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.
- “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”
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.
Make a data set.
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 SubIn 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
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.
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.
- How to Sort Columns in Excel without Mixing Data
- How to Sort Multiple Columns in Excel Independently of Each Other
- Auto Sort When Data is Entered in Excel (3 Methods)
- How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Advanced Sorting Options in Excel