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.
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.
- Thus, only your selected column will be sorted just like below.
You can sort the other columns by repeating the same process.
The ‘Payments’ column has been sorted from smallest to largest here.
📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel
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)
- 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.
- 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
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.
- 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.
- 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.
- 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
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.
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
- 2 Easy Steps to Sort Alphanumeric Data in Excel
- 6 Ways to Auto Sort When Data Changes in Excel
- How to Sort by Last Name in Excel
- Sort Duplicates in Excel Easily
- How to Sort Alphabetically in Excel with Multiple Columns