How to Alphabetize in Excel?
Alphabetize in Excel
One of the reasons for Excel's widest popularity is its ability to swiftly and effortlessly sort data. Excel facilitates easy methods that alphabetically sort the lists of strings (i.e., putting them in alphabetical order), and numerically arrange the numeric values both from largest to smallest and vice-versa.
The advantages of sorting the values alphabetically and numerically are as follows:
- This feature aids the user to quickly search any specific name or value in the Excel datasheet.
- It quickly helps the user to find all the duplicate values so as we can prevent the data entry error.
- It increases the readability of the Excel spreadsheet so as the user can comprehend it faster with less effort.
- It allows the user to group the fields together for any particular customer so that the user can look at them parallelly.
How to sort alphabetically in Excel
Excel’s Quick Sort feature enables rapid ordering or alphabetization of columns based on some simple steps. This is one of the easiest ways to alphabetically sort a column in Excel. Follow the below given steps to enable sorting for single column data for Excel 2003, Excel 2002 window versions, and Excel 2008, Excel 2004 for Mac versions:
- The first step is to eliminate all the blank cells present in the list. Because when the sorting functionality alphabetizes the Excel data, and in between it encounters with any blank cells, it assumes that all data has been sorted and only sorts the data above the blank row. Thus, it could create confusion.
- Select the cell or the column that you wish to sort. For instance, we have to sort the ‘Full_name’ column, so we will select the A1 cell.
- Click on the Data tab present on the Excel Ribbon toolbar and select the ‘Sort’ option.
- The Sort dialog box will pop up. In the ‘Sort by’ box, select the desired column.
- The next step is to choose the order of sorting. (in the given example we have selected Ascending, A to Z).
- Click on the OK button. As a result, the selected Excel list will be sorted alphabetically.
Follow the below given steps to enable sorting for single column data for Excel 2007 and above window versions and Excel 2016, Excel 2011 for Mac versions:
- The first step is to eliminate all the blank cells present in the list. Because when the sorting functionality alphabetizes the Excel data, and in between it encounters with any blank cells, it assumes that all data has been sorted and only sorts the data above the blank row. Thus, it could create confusion.
- Select the cell or column you want to sort.
- In the Home Ribbon tab, select the Editing section ->Sort & Filter option. Choose the ‘Sort A to Z’ sorting option to alphabetically sort your list.
- You will notice that cells in the Excel sheet the selected column has been sorted alphabetically.
Sort Alphabetically by Multiple Columns
With the above approach, we have learned to sort and alphabetize the data by a single column. But what if you are working with Excel data having multiple columns. If you want to alphabetize a range of cells using more than one column, we can use Excel's full sort functionality. Let’s check the step-by-step procedure for implementing the multiple columns sorting functionality:
- Select the range of cells or columns that you want to sort by alphabetizing, or you can directly press the shortcut key i.e., CTRL + A.
- In the Home ribbon tab, under the editing section, select the Sort & Filter option -> select the Custom Sort option.
- The Custom Sort dialog box will pop up.
- If your Excel data has headers on the top, tick on the ‘My Data Has Headers’ so as it can skip the headers while sorting.
- In the ‘Sort By’ box, choose the primary column which you wish to alphabetize. Here, we have selected the ‘full_name’ field.
- In the ‘Sort on’ box, choose the ‘Cell Values’.
- Select the alphabetic order i.e., A to Z in the Order box.
- Click on the Add Level button present at the top of the sort dialog box. You will notice another column level will pop up below the first column.
- Similarly, you can select another column in the ‘Sort By Box field’ (whose data you wish to sort alphabetically).
- Again, select ‘Cell Values’ in the Sort On box.
- Select the alphabetic order i.e., A to Z in the Order box.
- Click on the OK button.
As a result, the selected Excel list will be sorted alphabetically.
Advanced Sorting in Excel
The conventional sorting approaches that we have implemented so far comes with some loopholes in it. Let’s imagine a situation where the user wants to order the sheet chronologically by weekday, or some other user-defined customized sorting methods. If we follow the previous approach (sort from A to Z), it will arrange the months as per their alphabetic order unlike, April will come first, then August, then February, and so on. But this will not be the desired output. To combat the challenges, Microsoft Excel has introduced the Advanced Custom Sort option. With the help of this, the user can select the default option other than Alphabetizing and also can add his customized sort options.
Follow the below-given steps to embed the Advanced sorting method in your excel sheet:
- In the Home ribbon tab, under the editing section, select the Sort & Filter option -> select the Custom Sort option.
- The Sort dialog box will appear.
- Select the dropdown arrow from the Order list and choose the Custom List option.
- The ‘Custom Lists’ dialog will pop up. There are some default custom lists unlike Gender (Male/Female), Months (January, February, March, etc.,) Abbreviated Months (Jan, Feb, Mar, and so on.), Weekdays (Sunday, Monday, Tuesday, and so on.) You can choose the appropriate sort option as per your need and requirement. For instance, here we have selected Months.
- You can also add your own customized list of data. Click on the ‘Add’ present on the top right of the dialog box. Then, type of the entries as per their order.
- Once done, click on the OK button to sort your list. You will notice that the cells are quickly arranged and have been sorted as per the month order.