Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

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:

  1. 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.
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel
  • Click on the Data tab present on the Excel Ribbon toolbar and select the ‘Sort’ option.
 Alphabetize in Excel
  • The Sort dialog box will pop up. In the ‘Sort by’ box, select the desired column.
 Alphabetize in Excel
  • The next step is to choose the order of sorting. (in the given example we have selected Ascending, A to Z). 
 Alphabetize in Excel
  • Click on the OK button. As a result, the selected Excel list will be sorted alphabetically.
 Alphabetize in Excel

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:

  1. 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.
  2. Select the cell or column you want to sort.
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel
  • You will notice that cells in the Excel sheet the selected column has been sorted alphabetically.
 Alphabetize in Excel

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:

  1. 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.
 Alphabetize in Excel
  • In the Home ribbon tab, under the editing section, select the Sort & Filter option -> select the Custom Sort option.  
 Alphabetize in Excel
  • The Custom Sort dialog box will pop up.
 Alphabetize in Excel
  • 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. 
 Alphabetize in Excel
  • In the ‘Sort By’ box, choose the primary column which you wish to alphabetize. Here, we have selected the ‘full_name’ field.
 Alphabetize in Excel
  • In the ‘Sort on’ box, choose the ‘Cell Values’.
 Alphabetize in Excel
  • Select the alphabetic order i.e., A to Z in the Order box. 
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel
  • 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.  
 Alphabetize in Excel
  1. Click on the OK button.
 Alphabetize in Excel

As a result, the selected Excel list will be sorted alphabetically.

 Alphabetize in Excel

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:

  1. In the Home ribbon tab, under the editing section, select the Sort & Filter option -> select the Custom Sort option.  
 Alphabetize in Excel
  • The Sort dialog box will appear.
 Alphabetize in Excel
  • Select the dropdown arrow from the Order list and choose the Custom List option.
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel
  • 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.
 Alphabetize in Excel