How to Alphabetize in Excel Like a Pro: An Ultimate Guide

Why Is Alphabetizing Important in Excel?

Alphabetizing in Excel refers to alphabetically sorting the data. There are only two possible trends, either starting from ‘A’ or starting from ‘Z’. The act holds importance when dealing with large size of data, which is as follows: 

• The alphabetically arranged lists are time efficient as they ease finding the data easily.
• The data compilation or grouping becomes easy with the sorted list. You can easily segregate the information according to convenience.
• The redundant entries will be next to each other in an alphabetically arranged list. It makes it easier to eliminate such entries, which take up extra space or cause discrepancies in overall data handling.
• Increases readability of data and makes it look neat and sorted, encouraging efficient usage.

Alphabetizing Techniques

There are two techniques to alphabetize the information. Users can either choose to use the ‘sort’ function or go with the ‘filter’ function. However, understanding the key difference between the two is critical for appropriate usage. 

The ‘sort’ function will enlist the data in two ways. The sorting can be done from ‘A’ to ‘Z’ or from ‘Z’ to ‘A’. The complete list will be visible and arranged alphabetically. 

The ‘filter’ function is a one-time step. The application of a filter allows one to easily sort the list in the desired sequence as and when required with a simple click.  

How to Alphabetize in Excel?

To begin the alphabetizing, we made a list of fruits and their quantity. Figure 1 depicts the original randomly arranged list.

Figure 1: List of fruits along with quantity.

We will begin with the sort function and head on to the filter function. 

Sort 

Step 1: Select the overall data to be sorted. Since we will be sorting both columns, we selected both, as shown in Figure 2.  Selecting both columns means the data in the second column will be available for use according to the data sorted in the first column. If you do not select the second column at this step, the dependent data may cause discrepancies in calculations.

Figure 2: Data selected in both columns 

Step 2: Now, directly after making your choice, head to the ‘Sort & Filter’ function in the ‘Data’ tab. You will get two options as you click on the option: ‘sort’ or ‘filter.’ Check the ‘sort’ section. There are three options, as seen in Figure 3. 

Figure 3: Three options available for using the ‘Sort’ function

The choice of option will enlist the information from A to Z, and the choice of option 2 will enlist the information from Z to A. Here, the information in the first column will be alphabetically arranged. The third option will open a dialog box allowing you to choose your columns or sort them as per the desired alphabet. Here, you can also choose to give a customized list for sorting.

Step 3: Let us check the result of choosing options 1 and 2. We will first choose option 1, and the alphabetically arranged result will be seen in Figure 4. Note the change in the second column accordingly on both ascending and descending sorting. 

Figure 4: Alphabetically arranged names of fruits from ‘A’ to ‘Z’

Step 4: We will first choose option 2, and the alphabetically arranged result will be as seen in Figure 5.

Figure 5: Alphabetically arranged names of fruits from ‘Z’ to ‘A’

Filter 

The option will add a drop-down menu at the top of the list to let you sort the table anytime and as you want. Continuing with the previous table, let us head to the exact steps. 

Step 1: Select the data of interest and again head to the ‘Data’ and then ‘Sort & filter.’ This time, we will be using the filter function. The users can also select only the headings to use this function. 

Step 2: As the filter option is selected, a small arrow will appear in the headings. It serves as a drop-down menu to sort the list as desired. Figure 6 represents the table with the filter function. 

Figure 6: Arrow to use filter function at ‘Fruits’ and ‘Quantity’

Step 3: After clicking on the small arrow, a dialog box will appear, offering the option to sort. Represented in Figure 7, select the appropriate one.

Figure 7: Dialog box of the ‘Filter’ function 

Step 4: Choose the suitable option, and the list will be sorted. As stated, it is a one-time setting that allows sorting according to the requirement. The values in the second column align with the first column. 

Figure 8: List sorted from ‘Z’ to ‘A’

Alphabetize Multiple Columns

In the case of dealing with more than one column, the requirement to have both of them sorted to a specific order is possible. Dealing with it is as easy as previous ones. Let’s check how to do it. 

Step 1: We added another column to the previous data in Figure 9. Selecting all that data to alphabetize the table. If only headers are selected at this step, Excel will enquire whether to select the headers or expand the selection. 

Figure 9: Random arrangement of three columns

Step 2: Head to the ‘Sort & Filter’ function after selecting the data and use option 3, as discussed above. The option specifically mentioned as ‘Sort’ will be selected here. 

Figure 10: ‘Sort’ option in ‘Sort & Filter’ function 

Step 3: Here, define the priority order for sorting. After choosing the first heading for sorting, simply click on ‘Copy level’ to replicate the level. Then, select the second heading of priority. In our selection, witness the selection prioritization by the second column, ‘Region,’ and then by the first column, ‘Fruits.’

Figure 11: Sorting for different columns 

Step 4: On clicking ‘OK’, the reflected changes are seen, as shown in Figure 12. 

Figure 12: Table first sorted by region, then by fruits. 

Conclusion 

Alphabetical sorting is helpful in getting neat and organized data. Easily identifying the duplicates, the data can be sorted in two ways: sort and filter. While sort will switch the data to a specific order of selection, the filter allows arranging it as and when required. The choice of usage method depends on the particular needs. Both functions are easy to use and can be applied interchangeably.

The Post Graduate Program In Business Analysis teaches you the solutions to tackle complex business problems using various tools. This training introduces you to SQL, Power BI, and Tableau and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

FAQs

1. How do I alphabetize a list that contains duplicates? 

After the selection of data, in the ‘Sort and Filter’ option, there is an option besides ‘Filter’ named ‘Advance.’ Click on the option, and a dialog box will appear. At the bottom of the dialog box, you can tick the option stating ‘Unique records only’ to avoid duplicated data in your sorted list. 

2. Can I undo an alphabetizing action?

Yes, the original list can always be restored with the undo option. 

3. How do I alphabetize last names in Excel?

Use the ‘Sort’ function in the ‘Sort & Filter’ option. Select the column with the last name and prioritize it for sorting. It requires the presence of a last name in a separate column in the table. 

4. How do I alphabetize data without affecting the rows? 

To keep the rows together with the first option, select both columns. If not required, select the specific column to disassociate the row-based information.  

5. What are the limitations of Excel's alphabetizing capabilities?

There are certain limitations, such as headers must be differently formatted to be recognized so else they will be included in data. Further, there can be discrepancies in data if overall information is not selected.