Sort Multiple Columns Independently of Each Other in Excel

When an Excel workbook is sorted according to your preference it becomes very easy to navigate. You may know how to sort multiple columns which are interconnected but what about those which are independent of each other? In this article, we will show you how to sort multiple columns independently of each other in Excel. For this session, we will use Excel 365.


📁  Download Excel File

Download the following file to practice yourself.


Learn to Sort Multiple Columns Independently of Each Other in Excel with These 3 Quick Methods

Today we will discuss 3 simple ways you can sort multiple columns independently of each other in Excel. We will use some random data which are not related to each other so that when we sort column-wise the adjacent columns won’t be affected. Unfortunately, Excel doesn’t have that many features to independently sort columns in one go. Despite that, we will discuss how you can use the built-in Sort & Filter feature to your advantage. Again we will use the SORTBY and LEN functions together and finally try to create a VBA code to sort multiple columns independently of each other automatically in Excel.

Dataset to sort multiple columns independently of each other

Method 1

1.  Using Built-in Sort Feature

For our first approach, we will use the old Sort & Filter feature from the HOME tab. Usually, you would use it to sort columns that are correlated. But we can also use it to sort multiple columns which are independent of each other. Let’s show you the process with the following example step by step.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select your desired column. In our case, we will select B6:B14.
  • Next, go to the Home tab, and in the Editing group, choose Sort & Filter. Then select however you want your column to be sorted. We will choose Sort A to Z.

  • As a result, a Sort Warning dialogue box will appear. Which will ask you whether or not you want other columns to sort next to your selected column. Since we don’t want to we will choose Continue with the current selection Then click Sort.

Custom sort to to sort multiple columns independently of each other

  • Thus, only your selected column will be sorted just like below.

You can sort the other columns by repeating the same process.

Column sorted in descending order to to sort multiple columns independently of each other

The ‘Payments’ column has been sorted from smallest to largest here.

📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel

Method 2

2.  Applying Formula

If you want to sort your columns according to the length of your text you can use a formula containing the SORTBY and LEN functions. Let’s learn the basics of these functions first.

Syntax of the SORTBY function:

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The arguments are:

array – The column you want to sort.

by_array1 – the column to sort on

[sort_order1] – sorting order. 1 for ascending order and -1 for descending. If you skip, Excel will assume the default value 1.

The rest of the arguments are optional.

Syntax of LEN function:

LEN(text)

LEN returns the text length as an integer.

Let’s use the functions in a formula.

⬇️⬇️ STEPS ⬇️⬇️

  • First, create a separate table where your sorted columns will reside as we did here.

  • Next, write this formula in the first row of your desired column. We will be writing the formula in cell B17 to sort ‘Teachers’ Name’ in descending order based on text length.

=SORTBY(B6:B14,LEN(B6:B14),-1)

SORTBY & LEN functions to sort multiple columns independently of each other

  • Finally, press Enter to see the sorted column.

  • Again, for Number’s case, we will show you the sorting process of the ‘Payments’ column. So, select cell D17 to write the formula like this.
=SORTBY(D6:D14,LEN(D6:D14),-1)

  • Lastly, press Enter to see the sorted Payments.

🔨 Formula Breakdown

👉   Firstly, the LEN function will derive the lengths of the texts of every row of the column range for ‘Christopher’ it will give 11 to the SORTBY function.

👉  Finally, the SORTBY function will rearrange the cell range B6:B14 according to the integer provided by the LEN function in descending order because we provided -1 in the sort_order section.

📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula

Method 3

3.  Employing VBA Code

Unfortunately, both of the methods shown above have to be repeated for each column you have. Excel doesn’t provide any automatic process to sort multiple columns independently of each other at once. But we can write a VBA code to make the process easier and more time efficient. And to write our code we need to access the Developer option. Hence, requires the necessity to customize your Ribbon.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin, navigate to the File tab and select it. Excel will then display its Backstage view.

File tab to to sort multiple columns independently of each other

  • You can find Options in the lower left-hand corner. We will click here.

  • In the Excel Options box, select Customize Ribbon and from there go to Main Tabs and check the Developer box and finally click Ok to apply the change.

Customizing Ribbon to sort multiple columns independently of each other

  • Now you will be able to access Developer Tab in the Ribbon. Select and then click Visual Basic.

  • Microsoft Visual Basic for Applications dialogue box will show up and here to write the code we will go to Insert and then Module.

Inserting Module to sort multiple columns independently of each other

  • As a result, a blank white module will appear. Here, write the following code.
Sub Sort_Multiple_columns()
    Dim FIRSTROW As Range
    Dim ROW As Range
    Dim den_ws As Worksheet

    Application.ScreenUpdating = False
    Set den_ws = ActiveSheet
    Set FIRSTROW = den_ws.Range("B5:E5")
    For Each ROW In FIRSTROW
        With den_ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=ROW, Order:=xlAscending
            .SetRange den_ws.Range(ROW, ROW.End(xlDown))
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    Next ROW
    Application.ScreenUpdating = True
End Sub

VBA code to sort multiple columns independently of each other

P.S: In the Set FIRSTROW = den_ws.Range(“B5:E5”), we specified the first Rows of the first and last columns. When writing the code, change that according to your worksheet.

  • Now, press F5 to execute the code.

VBA result to sort multiple columns independently of each other

Like Magic, each column of your worksheet will be sorted independently.

📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel


📄 Important Notes

🖊️   You can’t sort your columns individually in alphabetical order with SORTBY and LEN functions.

🖊️   Delete the blank cells before running the VBA code.


📝 Takeaways from This Article

📌   Readers will be able to sort multiple columns independently of each other in Excel with the shown methods.

📌  The VBA code will help the readers understand coding in Excel and the sorting process.


Conclusion

Finally, we have come to the conclusion of our article. I hope I have described some easy ways to sort multiple columns independently of each other in Excel without complicating too much. If you know another way to achieve the same result, feel free to comment below. For more tips and tricks for Excel, you are welcome to visit Excelden.com.


Related Articles

(Visited 78 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo