How to Alphabetize in Excel

Microsoft Excel is an effective software for organizing important data, such as finances, sales data, customer information, etc. Alphabetizing the data in worksheets is one of the best ways to quickly organize, access, and reference those data. Alphabetizing a range of cells means sorting a range alphabetically in excel. Data can be sorted both ways, either in ascending order or in descending order.

For instance, let’s say you have a large data set of customer’s order information and you want to find a particular customer’s order history. Combing through the whole list will become quite a chore. Rather than wasting time scrolling through, you can alphabetize your columns to find your data quickly.

There are various ways you can use to alphabetize data in Excel, depending on the type of dataset you are working on. Excel allows you to sort a single column or a single row or selected range or an entire worksheet, as well as options to group multiple columns or rows together while you sort them. Let’s learn how to alphabetize data in Excel spreadsheets by rows or columns.

Why Alphabetize data in Excel

Alphabetizing the data will have numerous advantages:

It makes the data more sensible and makes it easier to read.
It makes it easier for a user to look up a particular value or customer name in an Excel datasheet.
It helps the user to visually identify duplicate records so we can prevent data entry errors.
It allows users to easily group the columns or lists together so that users can look at them side-by-side.

Alphabetizing (sort alphabetically) is an easy, common way to quickly sort your information. It is particularly useful for very large datasets. Totally, there are 4 ways to alphabetize data in Excel: A-Z or Z-A button, Sort feature, filter function, and formulas.

Alphabetizing a Column in Excel

The fastest way to sort alphabetically in Excel is by using the in-built Sort feature.

First, open the spreadsheet that contains the dataset. Select the cells or column that you want to sort and be sure not to select any blank cells. Then, go to the ‘Data’ tab and click either ‘A-Z’ to sort ascending or ‘Z-A’ to sort descending in the Sort and Filter group.

If there are any blank cells in between, Excel assumes that all the data has been sorted and stops above the black cell. If you wish to sort the entire column just select any cell in the column and use the above option.

The same options can also be accessed under the ‘Sort and Filter’ tool in the Editing section of the ‘Home’ tab.

Once you click the button, excel alphabetize your list immediately.

How to Sort and Keep Rows Together in Excel

You can easily sort a column if you only have one column in your worksheet, but it gets a little bit complicated when your worksheet contains multiple columns beside the column you want to sort. Also, sometimes, we work on spreadsheets where we need to keep corresponding rows intact after sorting the column.

For instance, if we have a spreadsheet of students’ mark list. When we sort the column of student names in alphabetical order, we also want the marks entered in the rows next to them should also move accordingly.

In such cases, you can use the ‘A-Z’ or ‘Z-A’ button to sort one of the columns in alphabetical order and it will automatically arrange the data in other columns, keeping the rows together. Let’s see how that works with another example.

First, select the first column according to which you want to sort other columns. Then select either ‘A-Z’ or ‘Z-A’ under the Data tab or Home tab to sort the data in one column, and Excel will automatically rearrange the other columns accordingly.

Once you click the sorting button, a Sort Warning dialog window will appear. Select the radio button for ‘Expand the selection’ and click the ‘Sort’ button.

This will keep the rows together while sorting columns, without mismatching any data (as shown below).

If you select ‘Continue with the current selection’ option, Excel will sort only the selected column.

How to Sort Alphabetically by Multiple Columns

If you want to alphabetically sort a dataset by more than one column, you can do that with Excel’s Sort feature.

For example, we want to arrange the following table alphabetically first by Country, and then by First Name:

First, select the entire table you want to sort by alphabetizing, then go to the ‘Data’ tab and in the Sort and Filter group, click on ‘Sort’.

Or click the ‘Sort & Filter’ icon in the Editing section of the ‘Home’ tab and select ‘Custom Sort’ to access the same feature.

The Sort dialog box will show up and show the first column (parameter) in which the table will be sorted. In the ‘Sort by’ dropdown box, select the primary column by which you want to alphabetize the data first, ‘Country’ in our case, and in the ‘Order’ dropdown choose either ‘A to Z’ or ‘Z to A’.

Then, click the ‘Add Level’ button to add the second sorting level. Select the second column (in our case First Name) by which you want to alphabetize the data in the ‘Sort By’ box and select either ‘A to Z’ or ‘Z to A’. If your table has headers on the top, check the ‘My Data Has Headers’ checkbox so as it can skip the headers while sorting them. Add more sort levels if necessary, and click ‘OK’.

The table is arranged alphabetically: first by Country, and then by First Name as shown below.

How to Sort Rows Alphabetically in Excel

Sometimes you may want to alphabetize rows in Excel, rather than columns. This can be done by using Excel’s Sort feature. For example, let’s say you have a spreadsheet where the first-row column B to T contains the city names where the number of stores available under different retail types and Column A contains retailer type. The cells are used to keep track of how many stores in each city in different retailer categories.

First, select all the rows that you want to alphabetize; If you don’t want to move row labels, leave them out of your selection. Now, navigate to the ‘Data’ tab and click ‘Sort’ in the Sort and Filter group.

In the Sort dialog box, click the ‘Options’ button.

Another small dialog box will appear, giving you the option to select ‘Sort left to right’ or ‘Sort top to bottom’. Select the ‘Sort left to right’ option to alphabetize by row instead and click ‘OK’ to get back to the Sort dialog box.

Now, select the Row you want to alphabetize in the ‘Sort by’ drop-down list (Row 1 in this example). In the other field, keep Cell Values in the ‘Sort on’ box, and select ‘A to Z’ (ascending order) in the Order box. Then, click ‘OK’ to close the dialog box.

Now, the first row in our table is sorted in alphabetical (ascending) order, and the remaining rows are rearranged accordingly as shown below.

How to Alphabetically Sort a Column by Using a Filter

Another quick way to sort alphabetically in Excel is by using the Filter feature. Once you apply filters to columns, the sorting options for all columns can be accessed with just one mouse click.

To add a filter to columns, select one or more columns and click the ‘Filter’ option in the Sort & Filter group on the ‘Data’ tab. If you want to apply the filter to the whole table, select any cell in the table and click the ‘Filter’ option.

You can also access the Filter option by clicking the ‘Sort and Filter’ tool in the Editing group of the ‘Home’ tab, and selecting ‘Filter’.

A small drop-down arrow will appear in each of the column headers. Click on the arrow of any column you want to alphabetically sort, and select either ‘Sort A to Z’ or ‘Sort Z to A’.

This will arrange the column you want in your chosen alphabetical order, and if you chose ‘Sort A to Z’, a small upward arrow will appear on the filter button indicating the sorting order (ascending):

Advanced Sorting in a Custom Order

Sorting alphabetically is not ideal for every alphabetical data. Sometimes, data could be alphabetized, but it’s not the most effective way to sort it.

Let’s imagine a situation where you may have a list containing the names of months or weekdays, sorting alphabetically is not useful in that situation. It makes more sense to sort that list chronologically. If you sort from A to Z, it will arrange the months in alphabetical order, April will come first, then August, February, June, and so on. But this is not what you want. Fortunately, it is very easy to arrange chronologically in Excel using the Advanced Custom Sort option.

Start by selecting the table you want to sort. Then select ‘Custom Sort’ under ‘Sort & Filter’ in the Editing section of the Home tab.

In the Sort dialogue box, choose the column that contains months of the year in the column section, sort on ‘Values’, as usual, and in the Order section, choose ‘Custom List’.

In the Custom Lists dialog, you can create a list of your own. There are some default custom lists including days of the weeks, abbreviated months, months of the year, and so on. Choose the appropriate sort option as per your need (in our case January, February, ..) and click ‘OK’. Then click ‘OK’ again to sort your list chronologically.

You will notice that you’ve successfully sorted the list as per the month order.

How to Sort Alphabetically in Excel Using Excel Formulas

If you are a fan of formulas, then you can use Excel formulas to alphabetize a list. The two formulas that can be used for alphabetizing data are COUNTIF and VLOOKUP.

For example, we have a list of names we want to alphabetize using formula.

To sort this, we will add a temporary column named this ‘Sorting order’ to the existing table.

In the cell (A2) next to the data, use the following COUNTIF formula:

=COUNTIF($B$2:$B$20,"<="&B2)

The above formula compares a text value in cell B2 with all the other text values (B3:B20) and returns its relative rank. For example, in cell B2, it returns 11, as there are 11 text values that are lower than or equal to the text ‘Nancy’ in alphabetical order. The result is the sorting order of the employee name in cell B2.

Then use the fill handle drag this formula to fill it in the entire range. This gives the sorting order of each of the names in the list.

Now, we have to arrange the data shown in the above screenshot based on the sorting order number, and to do this we are going to use a VLOOKUP function.

The Syntax:

=VLOOKUP(<sort number>,A:B,2,0)

Here ‘sort number’ represents the numbers in ascending order from 1 – 20. For descending order the numbers should be from 20 – 1.

Here, we have Column D for sorted names. For cell D2, enter the following VLOOKUP formula:

=VLOOKUP(1,A:B,2,0)

Similarly, for the second and third cells, you have to use the formula as :

=VLOOKUP(2,A:B,2,0) and =VLOOKUP(3,A:B,2,0) and so on…

After entering this VLOOKUP formula to every cell next to the data, the list gets alphabetized.

Instead of manually entering the above-mentioned formula (1-20) to every cell, you can also use the row function to make your work easier. The Row () function returns the row number of the current cell. So, with the help of the row function, the formula will be:

=VLOOKUP(ROW()-1,A:B,2,0)

This formula will give you the same result as the above formula.

And then use the fill handle to drag this formula to fill it in the entire range.

How To Alphabetize Entries by The Last Name

Occasionally, we often have to alphabetically sort datasheets with last names. In such cases, even though the names start with the first names, you need to alphabetize them by the last name. You can do this with Excel Text formulas.

To do that, first, you will have to extract the first and the last names from full names into two different columns. Then reverse the names, sort them, and then reverse them back to their original form.

With a full name in A2, enter the following formulas in two different cells (B2 and C2), and then copy the formulas down the columns (using fill handle) until the last cell with data:

To extract the first name, enter this formula in cell C2:

=LEFT(A2,SEARCH(” “,A2)-1)

To extract the last name, enter this formula in cell D2:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

And then, in cell E2, concatenated the first and last names in the reverse order separated by a comma:

=D2&", "&C2

We have separated first and last names and reversed them. Now we have to alphabetize them and reverse them back. As you can see above, when we extract names using the formula, columns C, D, and E actually contain formulas but they are formatted to look like values. So we need to convert the formula to values.

To do that, first select all the formula cells (E1:E31) and press Ctrl + C to copy them. Right-click the selected cells, click on the ‘Values’ icon under ‘Paste Options’, and press the ‘Enter’ key.

Then, select any cell in the resulting column, and click the ‘A to Z’ or ‘Z to A’ option on the ‘Data’ or ‘Home’ tab as per your needs.

A Sort Warning dialog box will pop up once you click the button. Choose the ‘Expand Selection’ option and click on the ‘Sort’ button.

As you can see, the entire column alphabetized by the last name.

Now, if you wish to reverse it back to the original ‘First Name Last Name format’, you just have to enter the same formulas but with different cell references.

You have to separate the name (E2) into two parts again by using the below formulas:

In G2, extract the first name: