Table of Contents
Alphabetize
This tutorial will guide you through the process of alphabetizing in Excel.
While sorting data alphabetically in Excel is easy certain situations may require more advanced techniques to achieve the desired alphabetical order.
Sort Alphabetically by Multiple Columns
Suppose we want to alphabetically sort a list of movies first by genre and then by title.
1. Select a single cell in the range to sort.
2. On the Data tab in the Sort & Filter group click Sort.
The Sort dialog box appears.
3. Set the primary sort key to the Genre column.
4. Click on Add Level.
5. Set the secondary sort key to the Title column.
6. Click OK.
Done. The movies are sorted by genre first and within each group (Comedy Drama Sci-Fi) sorted by title. Alphabetizing in Excel is that simple. More advanced techniques coming up next!
Alphabetize Using a Formula
Sometimes a formula can help when alphabetizing data for example to alphabetize serial codes based on the last character (suffix).
1. Use the RIGHT function to extract the last character (suffix) from each serial code.
2. Select a cell in the Suffix column.
3. To sort in ascending order on the Data tab in the Sort & Filter group click AZ.
Done. This formula is quite simple. The next section uses a slightly more complicated formula.
Ignore A An The
Suppose we want to alphabetically sort a list of movies while ignoring articles (A An The) at the beginning of titles.
1. Use the formula below to remove “A ” “An ” or “The ” at the beginning of each title.
Explanation: the formula checks if a title starts with “A ” “An ” or “The ” using the OR function combined with multiple LEFT functions. If it does it finds the first space using the FIND function and then uses the MID function to skip the article and extract the rest of the title. If there’s no article the title remains unchanged (see Jaws).
2. Select a cell in the Helper column.
3. To sort in ascending order on the Data tab in the Sort & Filter group click AZ.
Done. The formula ensures that the titles are sorted by the main words instead of articles (A An The) making the alphabetical list more intuitive and useful.
Not a formula hero? Read on for a great tip that can help with alphabetizing.
Use Flash Fill for Alphabetical Sorting
Flash Fill can also extract elements for alphabetical sorting even from complex data such as full names with titles.
1. Type the last name of the first person in a new column adjacent to the original data.
2. Select this cell.
3. On the Data tab in the Data Tools group click Flash Fill (or press CTRL + E).
Flash fill extracts all last names from the list of full names with titles. That’s awesome!
4. Select a cell in the Last Name column.
5. To sort in ascending order on the Data tab in the Sort & Filter group click AZ.
Done! The first column is now alphabetically sorted by last name.
