How to Sort Multiple Columns in Excel Independently of Each Other

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.

Download the Practice Workbook

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

1. Sort Multiple Columns in Excel Independently of Each Other Using Dialog Box

Let’s assume we have a dataset of some foods with their Name, Quantity, Delivery Date, Status, and Price. Now our task is to sort them independently using the dialog box.

Sort Multiple Columns in Excel Independently of Each Other Using Dialog Box

Step 1: Select the entire data set that you want to sort

Select the entire data set that you want to sort

Step 2: Select Sort under the Data tab

 Select sort dialog box

Step 3: Now a Sort window will pop up. Select the column name and select the other options as per your need

Select name

Step 4: Now Click on Add Level option and do the same thing for the other columns

Click on Add level

Step 5: Lastly, click on the OK button

 Click on OK in the SORT window

Step 6: Now all the data will be sorted individually

Sorted data

2. Sort Multiple Columns in Excel Independently of Each Other Using Sort Icons

Here we will do the same thing using the Sort icon in Excel. Let’s see the steps:

Step 1: Select the whole data like the previous example

Step 2: Now click on the Sort icon under the Data tab. You can choose A to Z (Ascending order) or Z to A (Descending order as per your need)

Click on Sort icon

Step 3: All the data will be sorted

Output of sorted data

3. Sort Multiple Columns in Excel Independently of Each Other Using SORTBY Function

Excel provides a function for sorting named SORTBY. Let’s see the basics of the function first

Syntax 

=SORTBY (array, by_array, [sort_order], [array/order], ...)

Arguments

Argument Required or Optional Value
array Required Pass the Range or array to sort.
by_array Required Pass the range or array to sort by.
[sort_order] Optional Sort order. 1 = ascending (default), -1 = descending.
[array/order] Optional Pass the additional array and sort order pairs.

Now we will sort all the columns of the previously used dataset using the SORTBY function.

Sorting using sort function

Step 1: Enter the in B21 and press Enter

=SORTBY(B5:F16, C5:C16, 1, D5:D16, 1,E5:E16,1,F5:F16,1)

Formula Explanation

  • B5:F16 this is the array or range from which data will be sorted.
  • C5:C16, D5:D16, E5:E16, F5:F16 using these ranges our data will be sorted by.
  • Lastly, 1 is used as we are considering ascending order.

Enter formula using SORTBY function

4. Sort Multiple Columns in Excel Independently of Each Other Using SORTBY and MATCH Function

In this method, we will sort all the data according to their Status. For this, we will need SORTBY and MATCH functions.

Sort Multiple Columns in Excel Independently of Each Other Using SORTBY and MATCH Function

Step 1: Enter the formula in cell B21 and press Enter

=SORTBY(B5:F16, MATCH(E5:E16, H9:H11, 0))

Formula Explanation

  • B5:F16 this is the array or range from which data will be sorted.
  • MATCH(E5:E16, H9:H11, 0) this portion will be those rows that will be matched with the H9:H11 status order. 0 is used to get the exact same match. If you want to explore more about the MATCH function you can visit this link

Enter the formula using Sortby and match function

5. Sort Multiple Columns in Excel Independently of Each Other Using SORTBY and RANDARRAY Functions

Excel provides the facility to sort data randomly. In this method, we will see that. For this, we will need SORTBY and RANDARRAY functions. Again, we will consider the same dataset.

Step 1: Enter the formula in cell B21 and press Enter

For column B

=SORTBY(B5:B16, RANDARRAY(ROWS(B5:B16)))

For Column C

=SORTBY(C5:C16, RANDARRAY(ROWS(C5:C16)))

For Column D

=SORTBY(D5:D16, RANDARRAY(ROWS(D5:D16)))

For Column E

=SORTBY(E5:E16, RANDARRAY(ROWS(E5:E16)))

For Column F

=SORTBY(F5:F16, RANDARRAY(ROWS(F5:F16)))

Formula Explanation

  • ROWS(F5:F16) will return the rows from this range. For more details, you can check this link
  • The RANDARRAY function produces an array of random numbers to be used for sorting. RANDARRAY(ROWS(F5:F16)) this portion will create different sorts by the range and based on this SORTBY function will do the sorting.

 Enter formula using sortby and randarray

6. 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 their string length.

Step 1: Enter the formula in cell B21 and press Enter

For Column B:

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

For Column C

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

For Column D

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

For Column E

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

For Column F

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

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.

Enter formula using SORTBY and LEN

7. 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: Select Marcos under the Developer tab

Select Marcos under Developer

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

Create macro

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

Run the 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: Now a pop-up window will come. Select the data that you want to sort. All the selected data ranges will be shown on the pop-up window. Click on the OK button after selection

 Select range

Step 5: Now all the data will be sorted automatically

VBA output

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.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo