———————————————————————————————————
Introduction
Alphabetizing a set of data means to sort alphabetically in Excel. Basically, you sort out a list in Excel alphabetically. It can be carried out both ways, either in ascending or descending order. The application has still another function prematurely to easily sort data.
This includes alphabetizing that provides strings (i.e. getting them in alphabetical order) and ordering exact values — both from biggest to smallest and smallest to biggest.
A lot of people know how to alphabetize 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 until the end!
How To Alphabetize In Excel & 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 easily and quickly. Alphabetizing will have numerous benefits:
-
It could make it simpler for an individual to appear up a particular price or person’s title
-
As it can help other people who utilize the spreadsheet understand it quicker and with less energy and
-
It creates the data more sensible.
-
It’s offers you the ease to search prices based on alphabetical order.
-
Additionally, it helps it be simpler for you yourself to creatively identify copy documents in important computer data set.
The 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).
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.
Alphabetizing a column using Shortcut keys
If you are an individual who wants to use the keyboard significantly more than doing the exact tasks with the mouse, then here I’ll share a list of shortcut keys which is useful for you while sorting columns in excel.
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 on Excel 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 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;
This formula gives the sorting order of each item in the list. Now, to arrange the following data based on the sorting order, 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.
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 the row function, the formula will be as =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 given below:
-
To begin with find the table to be alphabetized.
-
After this click the “Sort” button, on the “Data” tab.
3. This will open a “Sort” dialogue box, in the ‘Column’ drop-down choose the column based which you wish to alphabetize your data.
4. 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.
5. Finally click on the ‘Ok’ button and your data is sorted.
Conclusion
————————————————————————————————————————————–
Done Successfully! Those are the basics methods on how to alphabetize and sort by values in Excel.
