If you want to sort alphabetically in Excel, be it column or row sort, the process is usually simple. However, when it comes to ordering multiple rows or columns, or working with letters and numbers, this process may require a few additional steps.
This article will teach you everything you need to know about the alphabetical order of data in an Excel spreadsheet.
Alphabetical order in Excel with arrangement
The method you use to sort alphabetically in Excel depends on how many columns or rows contain the data you want to organize.
The easiest way is to sort one column of data alphabetically. If you have multiple columns and want to make sure that all data is alphabetically aligned, there are a few additional steps.
Finally, there are special considerations to consider if you want to sort multiple columns and whether the data has a header row or not.
The easiest way to alphabetically arrange in Excel is to use one column of data. For example, if you have a column with a list of names.
1. Select a data column.
2. Select the main menu and the Sort & Filter drop-down menu in the Editing group on the ribbon.
3. Select “Sort A to Z” or “Sort Z to A” depending on how you want to organize the data
Now, when you look at the highlighted column, you will see that the data there has been sorted alphabetically using the method you selected.
What if you have numbers in your data? For example, you have a second column with the age of each person.
You want to sort this column, so follow the same procedure as above and select the data column.
This time, when you select Sort & Filter on the ribbon, you will see that the sorting options have changed.
These options will either sort the numbers from smallest to largest, or vice versa.
Sort by multiple columns
There is one problem with sorting columns individually in a table that contains multiple columns of data.
If you select only one column and sort it, the remaining columns will stay where they are. This means your spreadsheet will be messed up and the data will no longer make sense. The answer to this question is to sort all columns at once.
Let’s do it again with a table with examples of name and age.
1. This time, instead of selecting one column, select the entire table.
2. From the main menu, select Sort & Filter and, as before, select your sorting preferences.
Now you will see that in your Excel spreadsheet, not only the names are sorted alphabetically, but also the ages from the second column sorted along with them. Those eras remained with the correct name.
This not only works with a two-column table in Excel, but it will work regardless of the size of your table if you select all the data on the worksheet before sorting.
Optionally, you can also sort multiple columns into one and then sort that column instead.
You will notice that there is another option in the Sort & Filter dropdown menu, Custom Sort.
To see how this works, let’s take a much larger table as an example. The table in this example is a list of SAT scores for New York State high schools.
- Select the entire data table in the spreadsheet, select the main menu, select Sort & Filter, and select Custom Sort.
2. You will see a new pop-up window where you can choose how to sort each column. Select the column you want to sort by, select a sorting method, and then select a sorting method (A to Z, Z to A, or Custom).
You will notice that there are several interesting sorting options. These are:
- Cell values: sort alphabetically or, in the case of numbers, from lowest to highest or highest to lowest.
- Cell color: arrange data grouped by cell color
- Font color: arrange data grouped by font color.
- Conditional Formatting Icon: Arrange data by icon in a cell according to the conditional formatting logic.
This will sort the entire data table by the first selected column. But you can also do a secondary sort (after sorting the first selected column).
Just select Add Level. In the new fields that appear, select the Column, Sort By, and Order drop-down lists again.
Click OK and you will see that your entire table is now sorted across multiple columns.
It sorts first by the first selected column, then by the second, third, etc.
Note. You may have noticed that the column sorting options in the dropdown above include header names, not letters. This is because the “My data has headers” checkbox is selected in the sort window. If your spreadsheet has headers, be sure to select that.
Alphabetical order in Excel with filter
When using the Sort & Filter dropdown in the main menu, you might have noticed that there is also a Filter option.
If you choose this, you will see drop-down arrows appear at the top of each column. Selecting one of the arrows at the top of any of these columns displays a long list of options.
Here’s how each of these options works:
- Sort A to Z or Sort Z to A: Sort the sheet using this column alphabetically in any direction.
- Sort by color: The same multi-column sort window as in the previous section opens.
The other filtering options at the bottom won’t help you sort alphabetically in Excel, but there are many options for filtering data that you don’t need. When you select text filters, you can choose to display only data on the sheet that:
- is equal to a specific value
- is not equal to a specific value
- Starts with a word or number
- Ends with word or number
- Contains word or number
- Contains no word or number
- Custom filter allows you to combine any of the specified above filters to use multiple filters.
As you can see, there are many ways to arrange (or arrange numbers) in Excel. The option you choose really depends on how much data you want to sort, how large the table you want to sort, and whether you only want to sort one column or a row or several.
Learn more about various Excel tips and tricks and share any other techniques you know to sort rows and columns in Excel.