Alphabetize

 

Sort Alphabetically by Multiple Columns | Alphabetize Using a Formula | Ignore A, An, The | Use Flash Fill for Alphabetical Sorting

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.

Data to Alphabetize

2. On the Data tab, in the Sort & Filter group, click Sort.

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.

Sort Alphabetically by Multiple Columns

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 are coming up next!

Sorted in Alphabetical Order

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.

Use a Formula to Alphabetize

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.

Sort in Ascending Order

Done. This formula is quite simple. The next section uses a slightly more complicated formula.

Alphabetized Using a 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.

Formula that Removes A, An, The

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.

Sort in Ascending Order

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.

Alphabetize Ignoring A, An, The

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.

Last Name

3. On the Data tab, in the Data Tools group, click Flash Fill (or press CTRL + E).

Click Flash Fill

Flash fill extracts all last names from the list of full names with titles. That's awesome!

Use Flash Fill to Extract Elements for Alphabetical Sorting

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.

Sort in Ascending Order

Done! The first column is now alphabetically sorted by last name.

Alphabetically Sorted by Last Name