Placing items in alphabetical order is one of the most common ways of organizing data. This is because it uses a standard system (the alphabet) to arrange items, so there is an accepted location for each item on the list.
Since Excel is all about handling and organizing data, it’s no surprise that this is one of the built-in tasks it handles really well.
Here’s how to alphabetize in Excel.
How to alphabetize columns in Excel
Method 1 - Using the A-Z icon
Using the data set below as a sample, we would probably want to alphabetize by last name using Column A.
Step 1 - Click on a cell in the column you want to sort by. Notice that A4 is our active cell in the above example.
Step 2 - Go to the Data tab and click this icon:
![]() |
![]() |
|
The last names will be sorted alphabetically in ascending order by the column of your active cell.
Download your free practice file!
Use this free Excel file to practice along with the tutorial.
Method 2 - Using the Filter command
An alternative to the above method is to use the Filter command, also on the Data tab. To do so:
Step 1 - Click on a cell in the column you want to sort by.
Step 2 - Go to the Data tab and click this icon:
To remove the filter dropdown arrows, click on the Filter icon again.
Both the Sort and Filter options are also accessible from the Home tab by using a dropdown menu and selecting their respective icons.
- You wanted to sort by columns.
- Row 1 is a header row and should not be sorted with the list of names.
- Columns A and B are elements of a single dataset, so rows should be kept together when a column is sorted.
In this case, these assumptions were all correct, so everything worked out well. Sometimes though, the dataset to be sorted is some variation of the above conditions, so the “A to Z” or “Z to A” icon won’t do.
Sort alphabetically by multiple columns
For example, some datasets have several columns with different potential sorting categories, like the database of books in the image below.
We may want to apply multi-level sorting by first separating books into fiction versus nonfiction, and then alphabetically by the author’s name.
To carry out this task:
- Click anywhere within the dataset.
- Go to the Data tab on the ribbon and click on the “Sort” command.
- Excel automatically detects the dataset to be sorted and displays the Sort dialog box on the screen.
- Click the “Sort by” dropdown arrow and select the Classification column. The data will be sorted based on the cell values, and in the A-Z order, which is the default.
- To add a second sorting level, click the “Add Level” button and select the Author column.
- Click OK.
- The data is now reorganized. Fiction books appear at the top of the list, with their authors being listed from A to Z. Non-Fiction books are grouped alphabetically next.
How to alphabetize rows in Excel
Less often, we may have rows that we want to alphabetize. We can imagine a dataset like the one below, for instance, where we have the number of book copies available by author.
Of special note is that this dataset doesn’t look like most tabular datasets, so Excel may not identify header rows correctly. To make sure that you don’t get any unexpected results, do the following:
- Highlight the data to be sorted. In this example, we would not highlight the text in Column A since we do not want it shifted when the data gets sorted.
- Click the “Sort” icon from the Data tab.
- Uncheck My data has headers.
- Click Options.
- Select the “Sort left to right” radio button and click OK.
- From the “Sort by” dropdown list, choose Row 1. Click OK.
Unexpected results when sorting alphabetically in Excel
1. Header row gets sorted among data
This happens when Excel doesn't recognize your header row, so it thinks it should be sorted with the data below.
- Highlight the A2 to B10 range, making sure that A2 is the first cell (the active cell) of your selection.
- Click the “A to Z” icon.
or
- Click any cell within the range.
- Click the “Sort” icon.
- Look at the data range selected by Excel. If it includes the header row, check the “My data has headers” box. This will remove that row from your selection to be sorted.
- Select the column you want to sort by.
- Click OK.
2. Excel only alphabetized a portion of your data set
This likely happened because there was a break, or gap in your data set, which Excel interprets as two separate pieces of data, causing it to ignore anything after the gap.
3. I only want a section of my data set to be sorted
There may be times when you want to do the opposite — sort only one section of an unbroken data set. Let’s use the list below as an example.
We cannot simply click the “A to Z” icon since Excel will maintain the data across each row. In this case, we must be specific about telling Excel exactly what we want to sort.
- Select the range C2 to C10.
- Click the “A to Z” or the “Sort” icon. A Sort Warning dialog box will appear.
- Since we have deliberately selected only a portion of the data set for sorting, choose the “Continue with the current selection” radio button.
- Click Sort.
- The original order of our passenger list has not changed, but the destinations are now in alphabetical order.
How to sort alphabetically in Excel with formulas
But what if your data set is a bit trickier, like the one below?
The LEN, LEFT, RIGHT, and SEARCH functions are incredibly useful for manipulating text data, as we’ll see below.
Step 1 - Extract the first names
=LEFT(A2,SEARCH(" ",A2)-1)
Step 2 - Extract the last names
In the adjacent cell, enter:
=RIGHT(A2,(LEN(A2)-SEARCH(" ",A2)))
Step 3 - Sort as usual
We can now sort by Column C in the usual manner.
Step 4 - Remove the working columns
Simply delete columns B and C since our original list is now alphabetized.
Summary
There’s no doubt that you will need to alphabetize lists in Excel since it’s one of the more frequently used data organization tasks. Sharpen your sorting skills now with these tips on how to alphabetize in Excel.
Then move on to more topics in Excel with our Excel - Basic and Advanced course. Or you can start with our free Excel in an Hour course.
Level up your Excel skills
Become a certified Excel ninja with GoSkills bite-sized courses
Start free trial