How to Sort Multiple Columns in Excel Independently of Each Other

In this article, we will show 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. Moreover, 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.

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

In this article, we will discuss how to sort multiple columns in Excel independently of each other. Firstly, we will use a VBA code to sort the columns. Then, we will use the SORTBY and LEN functions in each column to resolve the matter. Here, we have a dataset of fruits with their delivery information and quantities. We are going to sort those columns independently.

how to sort multiple columns in excel independently of each other


1. Using VBA Code

VBA codes are a very effective way to do a complicated Excel operation with a click of a button. Using VBA code, we can easily sort all the data in multiple columns at once. Let’s see how to do that.

Steps:

  • Firstly, go to the Developer tab.
  • From there, select the Visual Basic command.
  • As a result, the visual basic window will be on the screen.

openong visual basic window to show how to sort multiple columns in excel independently of each other

  • From the Visual Basic window, first, select Insert.
  • From the available options, choose Module.
  • Consequently, a module will be on the screen.

inserting module to show how to sort multiple columns in excel independently of each other

 

  • In the module, write the following code and save it.
Sub Sort_multiple_columns()
    Dim xRg As Range 'Declare range for Columns
    Dim yRg As Range 'Declare range for Rows
    Dim Am_ws As Worksheet 'Name the WorkSheet
    Set Am_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 Am_ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange Am_ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

writing and saving code to show how to sort multiple columns in excel independently of each other

  • Again, go to the Developer tab.
  • Then, choose Macros.
  • As a result, a prompt will be on the screen.

  • From the prompt, first, select the code name.
  • Then, click on Run.
  • As a result, a prompt will be on the screen.

running code to show how to sort multiple columns in excel independently of each other

  • In the prompt, first, select the B5:F15 range as the desired range.
  • Then, click OK.

  • Consequently, all the columns will be sorted independently at a once.


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

The SORTBY function sorts the data in a range based on a criterion. The LEN function returns the length of a text as a numerical value. 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.

Steps:

  • Firstly, select the B13 cell and type,
=SORTBY(B5:B10, LEN(B5:B10),1)
  • Hit the Enter button.
  • As a result, the B5:B10 range will be sorted by the text length.

sorting fruit name by text length to show how to sort multiple columns in excel independently of each other

  • Secondly, choose the C13 cell and enter,
=SORTBY(C5:C10, LEN(C5:C10),1) 
  • Then, press Enter.
  • Consequently, the C5:C10 range will be sorted according to lower to greater number.

  • Thirdly, choose the D13 cell and enter the following formula,
=SORTBY(D5:D10, LEN(D5:D10),1)
  • Then, press the Enter button.
  • Consequently, we will have a sorted D5:D10 range according to earlier to later date.

  • After that, select the E13 cell and insert the following formula,
=SORTBY(E5:E10, LEN(E5:E10), 1)
  • Press Enter.
  • Consequently, the E5:E10 range will be sorted .

  • Finally, clcik on the F13 cell and type the formula below,
=SORTBY(F5:F10, LEN(F5:F10), 1)
  • Then, hit Enter.
  • As a result, the column will be sorted by lower to higher price.

Formula Explanation

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

Conclusion

In this article, we have talked about how to sort multiple columns in Excel independently of each other. We have shown all the methods with their respective examples but there can be many other iterations. We have also discussed the fundamentals of the used functions. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


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