How to Sort Multiple Columns in Excel Independently of Each Other

In Microsoft Excel, there are a lot of ways to arrange text data alphabetically, dates chronologically, and numbers from smallest to largest or from highest to lowest. In addition, there are some ways to customize the sorting process. With that Excel 365 offers a brand-new way to sort data with formulas which is very comfortable and astonishingly simple to use. In this article, I will show different ways to sort multiple columns in Excel independently of each other.

Read More: How to Sort Multiple Columns in Excel


Download the Practice Workbook


2 Ways to Sort Multiple Columns in Excel Independently of Each Other

Here, we have a dataset of fruits with their delivery information and quantities. We are going to sort those columns independently.

1. Sort Multiple Columns in Excel Independently of Each Other At Once Using VBA Code

Using VBA code, we can easily sort all the data in multiple columns at once. Let’s see how to do that.

Step 1: First, select Marcos under the Developer tab.

Sort Multiple Columns in Excel Independently of Each Other At Once Using VBA Code

Step 2: Next, give the name of the Macro program and click on Create option. Make sure the Macro in option is as This Workbook.

Sort Multiple Columns in Excel Independently of Each Other At Once Using VBA Code

Step 3: Now write the code in the VBA window and Run it (Shortcut F5).

Sort Multiple Columns in Excel Independently of Each Other At Once Using VBA Code

Code:

Sub Sort_multiple_columns()
    Dim xRg As Range 'Declare range for Columns
    Dim yRg As Range 'Declare range for Rows
    Dim ws As Worksheet 'Name the WorkSheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Sort Multiple Columns", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

Step 4: After that, a pop-up window will come. Select the data that you want to sort. All the selected data ranges will be shown in the pop-up window. Click on the OK button after selection.

 Select range

Step 5: Finally, all the data will be sorted automatically.

VBA output


2. Sort Multiple Columns in Excel Independently of Each Other by String Length

We can easily sort data by their string length using SORTBY and LEN functions. Now we will sort each column of the previous data by its string length.

Step 1: In the beginning, enter the formula in cell B21.

For Column B:

=SORTBY(B5:B16, LEN(B5:B16),1)

In Column C:

=SORTBY(C5:C16, LEN(C5:C16),1) 

Again for Column D:

=SORTBY(D5:D16, LEN(D5:D16),1)

And in Column E:

=SORTBY(E5:E16, LEN(E5:E16), 1)

Finally for Column F:

=SORTBY(F5:F16, LEN(F5:F16), 1)

Step 2: Then press Enter.

Sort Multiple Columns in Excel Independently of Each Other by String Length

Formula Explanation

  • Here, LEN(F5:F16) will find the length of the selected rows. For more details, please check out this link.
  • Lastly, the SORTBY function sorts them according to their length number.

Things to Remember

Common Errors When They show
#REF! If a SORTBY formula references another Excel file, both workbooks need to be open. If the source workbook is closed, a #REF! error occurs.
#VALUE! The sort_order arguments can only be 1 (ascending) or -1 (descending). If no value is set, SORTBY defaults to ascending order. If any other value is set, a #VALUE! error is returned.
#SPILL! Like any other dynamic array function, SORTBY spills the results into an automatically resizable and updatable range. If there are not enough empty cells to display all values, a #SPILL! error is thrown.

Conclusion

These are the ways to sort multiple columns in excel independently of each other. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. The first method is wrong. sort by, then by,… does not sort independently

Leave a reply

ExcelDemy
Logo