Aung Shine
Quite often, we have to perform the Sort operation on our Excel data. Additionally, we can sort the data based on numerous orders. The Alphabetical Order is one of the most used ones. However, manually sorting in alphabetical order in a large worksheet is a tiresome job. In this article, we will show you the simple yet effective methods to Sort Data in Alphabetical Order in Excel.
To illustrate, we will use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company.
How to Sort Data in Alphabetical Order in Excel (8 Easy Methods)
1. Sort Value in Alphabetical Order in Excel with Sort Feature
The Excel Sort feature helps us to sort the data very easily. In our first method, we will use this feature. Therefore, follow the steps to perform the task.
STEPS:
- First, select the range B5:D10.
- Then, go to Home ➤ Editing ➤ Sort & Filter ➤ Sort A to Z.
- Finally, you’ll get the sorted result.
Read More: How to Undo Sort in Excel
Similar Readings
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Name
- How to Sort by Last Name in Excel
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
2. Apply Excel Filter Feature to Set Data in Alphabetical Order
We can also apply the Filter feature to Sort data. So, learn the below steps to carry out the task.
STEPS:
- Firstly, click B4.
- After that, select Home ➤ Editing ➤ Sort & Filter ➤ Filter.
- Now, press the drop-down beside the Salesman header and select Sort a to Z.
- At last, it’ll return the sorted data.
Read More: Excel Auto Sort when Data is Entered
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
- How to Sort Columns in Excel Without Mixing Data
3. Sort Multiple Columns in Excel
Moreover, we can sort multiple columns at the same time. This is particularly helpful when we have the same values in multiple cells of a column. Hence, follow the process to Sort Data in Alphabetical Order in Excel.
STEPS:
- In the beginning, select the range B5:D10.
- Then, select Data ➤ Sort & Filter ➤ Sort.
- Consequently, the Sort dialog box will pop out.
- Now, press Add Level.
- Next, select Salesman in Sort by and Product in Then by fields.
- Subsequently, select A to Z from the Order options and press OK.
- In the end, you’ll get the desired sorted data.
Read More: How to Sort and Filter Data in Excel
Similar Readings
- How to Sort Two Columns in Excel to Match
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- How to Sort by Date in Excel
- Excel Sort Dates in Chronological Order
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
4. Alphabetically Sorting Rows
By default, Excel applies the Sort operation from top to bottom. But, we can sort left to right through a small setting. Therefore, learn the process of sorting rows alphabetically.
STEPS:
- First of all, select the range and go to Data ➤ Sort & Filter ➤ Sort.
- As a result, the Sort dialog box will pop out. Here, press Options.
- Afterward, select the circle for Sort left to right and press OK.
- Then, select Row 4 (Headers row) and select A to Z in Order.
- Subsequently, press OK.
- Eventually, it’ll return the reorganized data.
Read More: How to Sort Dates in Excel by Month and Year
Similar Readings
- How to Sort Excel Sheet by Date
- How to Sort by Month in Excel
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Birthdays in Excel by Month and Day
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
5. Order Data in Excel Using SORT Function
Additionally, we can use the Excel SORT function for ordering data. So, follow the procedure.
STEPS:
- Select cell F5 at first.
- Here, type the formula:
=SORT(B5:D10,1,1)- Lastly, press Enter and it’ll spill the rearranged data.
Read More: How to Add Sort Button in Excel
6. Create a Helper Column for Sorting Value in Alphabetical Order
However, we can create a Helper Column for Sorting values alphabetically. In order to perform the task, learn the following steps.
STEPS:
- Firstly, select cell E5 and type the formula:
=COUNTIF($B$5:$B$10,"<="&B5)- After that, press Enter and use the AutoFill tool to complete the series.
The COUNTIF function compares the text values and returns their relative rank.
- Now, select cell F5. Here, type the formula:
=INDEX($B$5:$B$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))- Next, press Enter and complete the rest with the AutoFill tool.
⏩ How Does the Formula Work?
- ROWS($E$5:E5)
The ROW function returns the respective row numbers.
- MATCH(ROWS($E$5:E5),$E$5:$E$10,0)
The MATCH function returns the relative position of the items present in the range $E$5:$E$10.
- INDEX($B$5:$B$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))
Finally, the INDEX function returns the value present in the row spilled from the MATCH(ROWS($E$5:E5),$E$5:$E$10,0) formula.
- Then, in cell G5, type the formula:
=INDEX($C$5:$C$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))- Press Enter and fill the series using AutoFill.
⏩ How Does the Formula Work?
- ROWS($E$5:E5)
The ROW function returns the respective row numbers at first.
- MATCH(ROWS($E$5:E5),$E$5:$E$10,0)
The MATCH function returns the relative position of the items present in the range $E$5:$E$10.
- INDEX($C$5:$C$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))
Finally, the INDEX function returns the value present in the row spilled from the MATCH(ROWS($E$5:E5),$E$5:$E$10,0) formula.
- Subsequently, in cell H5, type the formula:
=INDEX($D$5:$D$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))- Finally, press Enter and complete the rest with AutoFill.
⏩ How Does the Formula Work?
- ROWS($E$5:E5)
The ROW function returns the respective row numbers at first.
- MATCH(ROWS($E$5:E5),$E$5:$E$10,0)
The MATCH function returns the relative position of the items present in the range $E$5:$E$10.
- INDEX($D$5:$D$10,MATCH(ROWS($E$5:E5),$E$5:$E$10,0))
Finally, the INDEX function returns the value present in the row spilled from the MATCH(ROWS($E$5:E5),$E$5:$E$10,0) formula.
Read More: Advanced Sorting in Excel
Similar Readings
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
- How to Remove Sort in Excel
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
7. Combine Excel Functions to Organize Data
To avoid the hassle of creating a helper column, we can combine some Excel Functions to Sort data.
STEPS:
- Select cell E5 at first.
- Then, type the formula:
=INDEX($B$5:$B$10,MATCH(ROWS($B$5:B5),COUNTIF($B$5:$B$10,"<="&$B$5:$B$10),0))- Next, press Enter and use the AutoFill tool to fill the series.
- Lastly, you’ll get organized data.
⏩ How Does the Formula Work?
- COUNTIF($B$5:$B$10,”<=”&$B$5:$B$10)
The COUNTIF function compares the text values present in the range $B$5:$B$10 and returns their relative rank at first.
- ROWS($B$5:B5)
The ROWS function returns the respective row numbers.
- MATCH(ROWS($B$5:B5),COUNTIF($B$5:$B$10,”<=”&$B$5:$B$10),0)
The MATCH function returns the relative position of the items present in the specified range which is the output of COUNTIF($B$5:$B$10,”<=”&$B$5:$B$10).
- INDEX($B$5:$B$10,MATCH(ROWS($B$5:B5),COUNTIF($B$5:$B$10,”<=”&$B$5:$B$10),0))
In the end, the INDEX function extracts the names in alphabetical order.
Read More: How to Perform Custom Sort in Excel
8. Sort Mixed Data Alphabetically in Excel
Sometimes, we may have to sort mixed data that contains duplicates, blanks, and numbers. In our last method, we’ll solve this kind of case. So, follow along to learn how to Sort Mixed Data in Alphabetical Order in Excel.
STEPS:
- In the beginning, select cell E5 and type the formula:
=COUNTIF($B$5:$B$10,"<="&B5)- Then, press Enter and fill the series with AutoFill.
Here, it compares the text values and returns the relative rank.
- After that, in cell F5, type the formula:
=--ISNUMBER(B5)- Subsequently, press Enter and complete the rest with AutoFill.
The ISNUMBER function looks for the Number values.
- Again, select F11 and use the AutoSum feature in Excel to find the total.
- Select cell G5 to type the formula:
=--ISBLANK(B5)- Press Enter and use AutoFill to complete the rest.
Here, the ISBLANK function looks for the blank cells.
- Afterward, select cell G11 and apply the AutoSum feature to find the total.
- Select cell H5 and type the formula:
=IF(ISNUMBER(B5),E5,IF(ISBLANK(B5),E5,E5+$F$11))+$G$11- Press Enter and use the AutoFill tool.
NOTE: This formula with the IF function segregates blanks, numbers, and text values. If the cell is blank, it returns the sum of cell E5 and cell G11. For any numerical value, it returns the comparative rank and adds the total number of blanks. If it is text, it will return the comparative rank and add the total number of numerical values and blanks.
- Now, select cell I5 and type the formula:
=IFERROR(INDEX($B$5:$B$10,MATCH(SMALL($H$5:$H$10,ROWS($I$5:I5)+$G$11),$H$5:$H$10,0)),"")- Next, press Enter and use the AutoFill tool.
- Finally, it’ll return the sorted data with the blank cell at the last position.
⏩ How Does the Formula Work?
- ROWS($I$5:I5)
Firstly, the ROWS function returns the respective row numbers.
- SMALL($H$5:$H$10,ROWS($I$5:I5)+$G$11)
Here, the SMALL function returns the specified smallest value from the range $H$5:$H$10.
- MATCH(SMALL($H$5:$H$10,ROWS($I$5:I5)+$G$11),$H$5:$H$10,0)
The MATCH function returns the relative position of the items present in the specified range.
- INDEX($B$5:$B$10,MATCH(SMALL($H$5:$H$10,ROWS($I$5:I5)+$G$11),$H$5:$H$10,0))
The INDEX function extracts the names in alphabetical order from the range $B$5:$B$10.
- IFERROR(INDEX($B$5:$B$10,MATCH(SMALL($H$5:$H$10,ROWS($I$5:I5)+$G$11),$H$5:$H$10,0)),””)
Lastly, the IFERROR function returns blank if an error is found, otherwise returns the data.
Read More: Sorting Columns in Excel While Keeping Rows Together
Problems While Sorting Data in Alphabetical Order in Excel
1. Blank or Hidden Columns and Rows
If there are blank or hidden data, we will not get the sorted result correctly. So, we need to delete the blank cells before applying the Sort operation to ensure a precise result.
2. Unrecognizable Column Headers
Again, if the headers are in the same format as the regular entries, it is likely that they will end up somewhere in the middle of the sorted data. To prevent this, select only the data rows, and then apply the Sort operation.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to Sort Data in Alphabetical Order in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
