How to Alphabetize in Excel 2010

Introduction


Alphabetizing set of data means sorting a listing alphabetically in MS Excel! It can be carried out both ways, either in ascending get or in descending order. The application has still another function prematurely and easily sort data. This includes both alphabetizing provides of strings (i.e. getting them in alphabetical order), and ordering exact values — both from biggest to smallest and smallest to largest.

A lot of people understand how to alphabetize a listing in Excel, but few of these actually know the most effective and fastest ways to do it. In this short article I am likely to highlight some of the greatest techniques to alphabetize in Excel 2010. Please read…

Why to Alphabetize Data

If you are physically sorting important computer data in Microsoft Excel, you are wasting your time! The complete position of spreadsheets is to produce knowledge sorting easy and quick. Alphabetizing will have numerous benefits:

  • angle-double-right
    It could make it simpler for an individual to appear up a particular price or person's title
  • angle-double-right
    It can help other people who utilize the spreadsheet understand it quicker and with less energy and
  • angle-double-right
    It creates the data more sensible
  • angle-double-right
    It offers you the ease to search prices based on alphabetical order
  • angle-double-right
    Additionally, it helps it be simpler for you yourself to creatively identify copy documents in important computer data set

Process to Alphabetize data using just 2 clicks in Excel Ribbon

1. Open the Microsoft Excel 2010 spreadsheet that contains the data you need to alphabetize or enter the random data. Select column you wish to alphabetize. In the following example, I have selected the column B (cell B5:B12).

alphabetize data chart

Click to enlarge

2. Then click the "Data" tab at the menu bar of the spreadsheet and then look up the "Sort" ribbon. Click the "AZ sort" icon to sort in ascending alphabetical order. Or Click the "ZA sort" icon to sort in descending order. Your highlighted cells will now be sorted in ascending order and obtain the sorted data as output.

student name data

Click to enlarge

Alphabetizing a column using shortcut keys

If you should be an individual who wants to use the keyboard significantly more than doing the exact same tasks with the mouse, then here I'll share a list of shortcut keys which is useful for you while sorting columns in excel.

Short cut Keys

Function

Alt+d,s

This will open the sort menu

Alt+h,s,s

This will simulate the click all the A-Z icon; ascending order

Alt+h,s,o

This will simulate the click all the A-Z icon; descending order

Note: Before using these shortcuts make sure that you have already selected your data in the spreadsheet..

Alphabetize a List of Data using Excel Formula (COUNTIF and VLOOKUP)

This is another method and I will use excel formulas as well to alphabetize a list of data? Here I have an example to alphabetize data in column B (Student Name). The two formulas that I am going to use are COUNTIF and VLOOKUP. However this method is not used frequently.

Many of you would be thinking that, “how we can sort a data list using a CountIf function?” Here I am using a simple trick to alphabetize the Student Name.

For example: I used a column A as “Sorting order” to the existing table. After this use a formula

=COUNTIF($B$5:$B$12,"<=​"&B5)

for the first student. And then drag this formula to fill it in the entire range. Here is the screenshot.

countif image

Click to enlarge

This formula gives the sorting order of each of the items in the list. Now, to arrange the following data based on the sorting order and for this I will use a VLOOKUP function.
Here I have Column D for sorted data. For cell D5, use the VLOOKUP formula as for D5 cell
           =VLOOKUP(1,A:B,2,0)
Similarly for the second and third items you can use the formula as
          =VLOOKUP(2,A:B,2,0)and =VLOOKUP(3,A:B,2,0) and so on…
After applying this VLOOKUP formula the list gets alphabetized. Please see the screenshot.

vlookup

Click to enlarge

Tip: In place of manually entering the 1-8 in the above-mentioned formula, you may also utilize the row function to help ease your task. Row() function gives you the row number of the current cell. So, with the use of row function, the formula will be: =VLOOKUP(ROW()-1,A:B,2,0). Please try once.

Sorting data table with multiple columns using Sort icon:

When you have a listing with two columns like “Roll number” and “Student Name. And you have to alphabetize this list based on “Student Names". Then you need to use the “Sort” button instead of the “A to Z” and “Z to A” buttons.

The sort button under the “Data” tab provides you with more control over how you would like the list to be sorted. It allows selecting just one column to be sorted, it manages your table headers and additionally, it may sort your data on the basis of text font or color. Follow below steps to utilize this technique as I have used in the example below:

  • check
    To begin with find the table to be alphabetized
  • check
    After this click the “Sort” button, on the “Data” tab
data tab

Click to enlarge

  • check
    This will open a “Sort” dialogue box, in the ‘Column' drop-down choose the column based which you wish to alphabetize your data.
sort dialog box

Click to enlarge

  • check
    In the ‘Sort On'drop-down choose the ‘values' option. Using ‘Sort On'dropdown you are able to sort important computer data centered on cell colour, font colour or cell icons. And In the ‘Order'field select “A to Z” for Ascending sort or “Z to A” for descending sort. Here in the example I have selected A to Z.
descending sort

Click to enlarge

  • check
    Finally click on the ‘Ok’ button and your data is sorted
data sort result

Click to enlarge

Conclusion


Done Successfully! Those are the basics methods on how to alphabetize and sort by values in Excel. Now if you want to dive deeper in to the brilliant COUNTIF formula, then sign up for 'The 27 Best Excel Formulas For Professionals' book for free below.

    Sohail Anwar

    Click Here to Leave a Comment Below
    How to Alphabetize in Excel 2010 – Earn and Excel - September 12, 2017 Reply

    […] post How to Alphabetize in Excel 2010 appeared first on Earn and […]

    How to Alphabetize in Excel 2010 – chambers Dixon - September 12, 2017 Reply

    […] post How to Alphabetize in Excel 2010 appeared first on Earn and […]

    How to Alphabetize in Excel 2010 – Ratliff Diaz - September 12, 2017 Reply

    […] post How to Alphabetize in Excel 2010 appeared first on Earn and […]

    Reshma - September 12, 2017 Reply

    Thank you for the helpful information. much appreciated.

    Leave a Comment: