Hassan Shuvo
If you have a large dataset and want to quickly sort your data alphabetically in Excel with multiple columns, Excel has some amazing built-in features to help you. Apart from those, we can also do the same thing with some functions. Today we are going to find out how we can sort multiple columns alphabetically in Excel with some of those simple yet elegant features.
📁 Download Excel File
You can download this file to practice.
Learn to Sort Multiple Columns Alphabetically in Excel with These 7 Methods
We are going to discuss 7 straightforward and effortless ways you can sort data alphabetically with multiple columns in Excel. For that, we will be using a dataset of some of my favorite movies with their IMDB scores which we will sort alphabetically and also in some cases in descending order. Please, bear with us in today’s learning process.
1. Using Sort & Filter Feature
Probably the easiest way to sort data alphabetically with multiple columns in Excel is by using the Home Tab in the ribbon. Let me explain how you will sort your dataset alphabetically in the following steps:
⬇️⬇️ STEPS ⬇️⬇️
- First, you have to select any cell in the column you wished to sort alphabetically. Here, we chose cell B6.
- Next, on the Home Tab, look at your right corner in the ribbon. In the Editing group, you will notice the Sort and Filter.
- Upon clicking on Sort & Filter, you will get a couple of options to sort your data accordingly. Since we want our data to rearrange according to alphabetical order we are choosing A to Z.
- Finally, if you click on Sort A to Z, you will see your data have rearranged as such. You can also customize conforming to your preference.
One thing you may notice no matter which column you choose, once you sort one column, the other columns get sorted automatically according to your chosen column. Quite interesting, right?
Moreover, you can access the same feature from the Data Tab. Go to Data and from the Sort & Filter group, you can access A-Z or Z-A to sort your data in ascending or descending order.
Another way to sort alphabetically in Excel with Multiple columns is to go through the Context Menu. Just select your desired column and Right-click your mouse and from Sort, you will find your options.
📕 Read More: Sort Multiple Columns Independently of Each Other in Excel
2. From SORT Dialog Box
If you want to sort multiple columns with multiple conditions at once, you can do that from the Sort dialogue box. To gain access to the Sort dialogue box please follow my lead. In our example, first, we will sort our first column alphabetically then our 3rd column from largest to Lowest.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select your entire dataset like we selected B6:D15.
- Next, go to Data Tab and select Sort.
- Congratulations! You have accessed the Sort dialogue box. Here we will put our sorting order for two of our columns. Before we do that, since we have headers we will check the box My data has headers.
- At this moment, you will see that Excel has already created the first sorting level for you. There are three boxes in here like
Sort by – you will select which column you want to sort. If you click on the drop-down list you will see your headers are there.
Sort on – you will choose what to sort. Typically it will be cell values.
Order – your sorting condition. It may be A to Z or Z to A or you can customize the order.
- There is an option to add another level where we can add a sorting order for another column. So we will click Add level.
- Now we will add our next column the “IMDB score” column and we will sort it from Smallest to largest.
- Selecting these options we will click OK to apply for sorting order. Similarly, if you want, you can add more levels according to your preference.
Excel will show your rearranged columns first alphabetically and then by smallest to largest value.
📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel
3. Adding Filter Drop-down Menu
The best thing about Excel is that the developers always promote comfortability. In That spirit, they’ve included the Auto Filter feature meaning you can add a Filter button to your Data table header, and then sorting will be one click away. Let’s explore this feature.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select one of the column headers in your data table like we select cell B5.
- Then go to the Home Tab where in the Editing group choose Sort & Filter.
- Next, select Filter.
- At this moment, you will see a small drop-down arrow has appeared in each of the column headers.
- Select Sort A to Z by clicking the drop-down arrow next to the column you want to alphabetize.
- You will be glad to see your column is sorted alphabetically from A to Z.
- You can also notice while clicking the drop-down arrow next to column ‘IMDB Score’, Excel gives you options to sort them from Smallest to Largest and vice versa.
📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel
4. Employing SORT Function
If you want to keep your original dataset intact and your sorted dataset on another table, you can work with the SORT function. Let me explain the format of the function below.
Syntax of the formula:
=SORT(array,[sort_index],[sort_order],[by_col])
The arguments in the formula are as follows:
Array – the data range you want to sort
Sort index – an identification number for the row or column to sort by like column numbers 1,2, etc.
Sort order – a number specifying the preferred sort order, with 1 being the ascending order (default) and -1 being the descending order
By col – a logical value that represents the preferred sort direction; TRUE to sort by column, FALSE to sort by row (default).
The last 3 arguments are optional. If not mentioned, Excel will assume the default value. In the Sort index’s case, it will be column 1/row 1.
Let’s use the formula in our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, you have to create an additional data table for your sorted values.
- Next, to type the following formula select F6 and in the Formula Bar, type this.
=SORT(B6:D15,1,1)
- Finally, press Enter to see the sorted result.
You might wonder why we skipped the last argument. That’s because we want Excel to sort them by column which is already its default setting.
📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula
5. Sort by Last Name
If you have names in your entries and you need to sort them according to the last names, here is another way to sort them alphabetically. Take a look at the directors’ names we have for the movies. We are going to sort them alphabetically in Excel in accordance with their last name.
But first, we need to sort their names in a ‘Last name, First name’ manner in a new column.
⬇️⬇️ STEPS ⬇️⬇️
- In Column E6, we will concatenate the first name and last names in reverse order. We will also put commas in between to separate them. See the below picture to understand.
=D7&”,”&C7
- Now, Enter and then drag the Fill Handle downward to copy the formula.
- Now, since we only need only values to rearrange the columns we need to eliminate the formula. So we are going to Copy (press Ctrl+C shortcut) the data in the E column and again paste it in the same column as values. After copying, paste by right-clicking the selected cells and clicking on values under Paste
- Now, if you click on any data in the E column, you will see no formula can be found on the Formula Bar.
- As our final step, we select any row in the resulting column and click A to Z in Data You can follow any steps here described in Methods 1-3.
- As you can see your movies have been perfectly rearranged in accordance with the director’s last name alphabetically.
📕 Read More: How to Sort by Last Name in Excel
6. Applying Array Formula
If you have a dataset with multiple columns where each column is independent, then you can sort them individually with an array formula comprising INDEX, MATCH, ROWS, and COUNTIF functions.
Although our example dataset is correlated, for this section we will assume our columns are independent with respect to other columns. So let’s apply the formula in the following fashion.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily we will have to create an additional Datatable on which we will apply the formula.
- Next selecting F6, just copy and paste the formula in the Formula Bar.
=INDEX(B$6:B$15,MATCH(ROWS(B$6:B6),COUNTIF(B$6:B$15,”<=”&B$6:B$15),0))
- Finally, drag the Fill Handle downwards to copy the formula to other Rows.
- Now, select all the Rows in the first column and drag the Fill Handle rightwards to copy the formula to other columns.
- Follow the same process for every column.
The aforementioned formula is based on the traditional INDEX MATCH combination utilized in Excel lookup operations. We updated it in the following manner since “alphabetical lookup” is what we need.
🔨 Formula Breakdown
👉 When all the values in the same column are compared to one another, COUNTIF($B2:$D2,”=”&B$6:B$15) returns an array with the relative rankings of the data which acts as the lookup array for the MATCH function.
👉 The lookup value is provided by ROWS(B$6:B6). The returning number is increased by 1 as we move down thanks to a combination of absolute and relative references. In other words, the lookup value for F6 is 1, F7 is 2, and so on.
👉 In the lookup array generated by COUNTIF(), MATCH looks for the lookup value computed by ROWS() and returns its relative position. For instance, the lookup value for F6 is 1, which is in the lookup array’s position 10; hence, MATCH returns 10.
👉 Finally, using its relative location inside the column, INDEX obtains the true value. It retrieves “All Quiet on the Western Front” as the 10th value for B6 in the range B6:B15.
Lastly, notice we didn’t put absolute cell reference before the column alphabet because our column will change when we drag the fill handle to the right for the other columns. Again, we used absolute cell reference for the Row index number because as we drag it downwards it will change.
📕 Read More: 6 Ways to Auto Sort When Data Changes in Excel
7. Using VBA
At last, we have come to the last way to sort our dataset alphabetically which is using the VBA script. Before writing VBA code we Developer tab enabled in our Ribbon. Find out how to access the Developer tab here.
These instructions will help you write your code when you access the Developer tab.
⬇️⬇️ STEPS ⬇️⬇️
- In the left-hand corner of the Ribbon, click Visual Basic while still on the Developer Tab.
- A window will show up. Then click Insert and subsequently, Module.
- In the module copy and paste the following formula.
Sub Sort_Alpha_Multiple_Col()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("B5"), Order:=xlAscending
.SetRange Range("B5:D15")
.Header = xlYes
.Apply
End With
End Sub
- To apply change press F5 or click on Run and click Run Sub/Userform.
- Your Column will automatically rearrange alphabetically.
- You can also put secondary sorting order if you like. Let’s say, we want the next order to be descending on the 3rd column. So we put an additional code like this.
Sub Sort_Alpha_Multiple_Col()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("B5"), Order:=xlAscending
.SortFields.Add Key:=ActiveSheet.Range("D5"), Order:=xlDescending
.SetRange Range("B5:D15")
.Header = xlYes
.Apply
End With
End Sub
- As for the result, the second sorting order will apply after keeping the first order intact.
📄 Important Notes
🖊️ For multiple columns, if you sort one column in alphabetical order the other will follow if you use Excel’s built-in features.
🖊️ The SORT function can only be used in Excel 365.
🖊️ If you don’t like the Filter button hanging beside your header, you can remove it by clicking the Filter option from Sort & Filter. Your data will remain rearranged.
🖊️ For Independent Columns, use Functions and Formulas rather than Built-in features. It will help you if you keep on adding new Entries.
🖊️ While using Array Formula, please keep in mind, your source data can not contain Empty Cells or Duplicate Values.
📝 Takeaways from This Article
📌 The article showcased Three in-built features of Excel to sort in alphabetical order.
📌 We explained a couple of Functions, how they work, and their limitations.
📌 Finally, a VBA code to make the process even more interesting.
Conclusion
Ultimately, we come to the end of the article. I hope I was articulate enough for you to understand the process. If you can make sense of them, my efforts will be realized. If you have any questions, feel free to comment below. I will try to respond as soon as possible. Have fun using Excel and you can visit Excelden.com for more Tutorials. Thank You.
