This guide will teach how to create an alphabet in Excel by using the sort and filter feature as well as formulas that help organize and format the data in a specific order.
Microsoft Excel is an effective program for organizing data that is important like sales, finances information about customers, and so on. The ability to alphabetize the worksheet data is among the most efficient methods to arrange, access and refer to the data. The process of alphabetizing a cell is a way of sorting a range alphabetically in Excel. The data can be sorted in two ways either in ascending or descending order.
Let’s say, for instance, you have a huge database of details about their orders and you wish to track down a specific customer’s order record. The process of combing through all the information is a major task. Instead of wasting time scrolling throughthe list, you can sort your columns alphabetically to quickly locate your information.
There are a variety of methods you can employ to sort data using Excel according to the kind of data you’re working with. Excel lets you sort one column, one row, or a selected range , or even the entire worksheet, and also options to group several rows or columns together as they are sorted. Learn how to sort the data in Excel spreadsheets using columns or rows.
Table of Contents
Why do we need to alphabetize data in Excel? Excel
The process of arranging data in alphabetical order will provide many benefits:
- This makes the information more logical and makes it easier to comprehend.
- It is easier for users to search for the value or name of a customer within the Excel datasheet.
- It assists users to see duplicate records, which helps us avoid mistakes in data entry.
- It lets users easily organize lists or columns together, so that they can view them side-by-side.
The process of alphabetizing (sort by alphabet) is a simple method to speedily sort information. It is especially useful for large data sets. In total, there are four methods to sort data in Excel such as Z-A or A-Z button Sort option, Filter function, and formulas.
Alphabetizing a Column in Excel
The quickest method to sort by alphabet in Excel is to use the built-in Sort feature.
Then, open the spreadsheet that holds the data. Choose the column or cells you wish to sort, and make certain not to select empty cells. Go to the tab ‘Data’ and select either ‘A-Z’ or ‘Z-A to sort ascending or ‘Z-A’ for sorting downwards within the Sort and Filter groups.
If there are blank cells between them, Excel assumes that all the data is sorted and is stopped just above the cell that is black. If you want to arrange the whole column, simply choose any cell within the column and select the above method.
Similar options can be found under the ‘Sort and filter tool located within the Editing section of the tab ‘Home.
After you click the button, Excel will alphabetize your list right away.
How to Sort and Keep Rows Together in Excel
It is easy to sort a column when you just have one column on your worksheet, however it becomes a more complicated when you have several columns in addition to the column you wish to sort. Sometimes, we use spreadsheets in which we have to keep the corresponding rows in place when sorting the columns.
For example, if you have an excel spreadsheet with students’ marks lists. If we arrange the column of students’ names alphabetically and we want to ensure that the marks that are entered in the rows that follow them to move in the same manner.
In these instances in these situations, you can utilize the ‘A-Z’ or ‘Z -A’ button to sort one of the columns alphabetically and then it will arrange the data into other columns, while keeping the rows in order. Let’s look at how this works by using a different instance.
Then, choose the first column that you wish to arrange other columns. Select either ‘A-Z or ‘Z-A’ on the Home tab or Data tab, to arrange the information into one column. Excel will automatically arrange the other columns in accordance with the.
When you have clicked the button to sort after which a sort Warning dialogue window will open. Choose the radio button that says Expand the selection’, then select the ‘Sort’ option.
This keeps the rows in order while sorting columns, without causing any mismatches (as as shown below).
If you choose to continue using your current choice’ option, Excel will sort only the column you have selected.
How to Sort Alphabetically by Multiple Columns
If you’d like to sort your data alphabetically with multiple columns, it is possible to accomplish this using the Sort function in Excel.
For instance, we would like to arrange the table alphabetically, first by Country, and after that, by First Name:
Then, choose the entire table that you wish to sort alphabetically first, then click on the tab ‘Data’ and , in the group titled Sort and Filter Click on Sort.
Click the ‘Sort and Filter’ icon located in the Editing section of the Home tab’ and choose the option ‘Custom Sort for access to the same option.
The dialog box for Sort will appear and display one column (parameter) that data will get sortable. In the ‘Sort By dropdown, choose the column that you want to sort by. you wish to sort the data first, which is ‘Country in this case and then in the dropdown ‘Order’, select either ‘A to Z or ‘Z to A’.
Click the “Add Level” button to add the second level of sorting. Choose the column (in the case of First Name) with which you would like to sort the data alphabetically within the Sort By field and choose either ‘A to Z or ‘Z to A’. If the table contains headers at the top make sure to make sure to tick the “My Data has Headers’ checkbox to ensure that it will skip headers when sorting them. If required, then select OK.
The table is organized alphabetically starting with Country and the next by First Name, as illustrated below.
How to Sort Rows Alphabetically in Excel
Sometimes, you might want to sort rows in Excel instead of columns. This is possible making use of Excel’s sort feature. Let’s take an example. that you’ve got a spreadsheet in which the first row column B to T includes the city names, where there are stores in different categories of retail and column A includes the retailers’ types. The cells are used to keep track of the number of stores are in each city within various categories of retailers.
The first step is to select all rows you wish to alphabetize. If you do not want to change the row’s label remove them from your list. Then, go to the tab ‘Data’ and select ‘Sort’ within the Filter and Sort section.
In the dialog box for Sort Click the button ‘Options.
A second small box will pop up, offering you the choice of selecting “Sort left to right” or Sort top to bottom’. Choose the ‘Sort left towards right’ choice to sort rows instead, and then select OK to go returned to the dialog box for Sort.
Select the Row you wish to alphabetize from the drop-down ‘Sort By’ listing (Row 1. in the case). In the second field, leave the Cell Values in the ‘Sort On box and then select A to Z (ascending the order) in the box for Order. After that, click “OK” for the dialogue box to be closed.
The first row of our table is sorted into the alphabetical (ascending) order and the other rows are arranged in the same manner as follows.
How to Alphabetically Sort a Column by Using a Filter
Another way to sort alphabetically within Excel is to use the filter feature. When you have applied filters to columns and sorting options, all columns are accessible by a single mouse click.
To apply a filter to columns, choose the column or columns, and select the filter option within the Sort & Filter group on the tab ‘Data. If you wish for the filter to be applied to the entire table, choose any cell of the table and then click the ‘Filter’ button.
You can also access the filter option by selecting the “Sort and Filter” tool within the Editing section of the tab ‘Home by selecting Filter..
An arrow with a small dropdown will be displayed within each column headers. Simply click on it in the column you wish to sort alphabetically, and then select ‘Sort A to Z or ‘Sort Z into A’.
The column will be arranged you wish to arrange in the alphabetical order. If you select ‘Sort From A To Z’, then a tiny upward arrow will be displayed on the filter button , indicating the order of sorting (ascending):
Advanced Sorting in a Custom Order
Sorting by alphabet isn’t the best method for all data that is alphabetical. Sometimes, data can be alphabetized, but this is not the best method of sorting it.
Let’s consider a scenario where you have a list that contains all the dates of month and weekdays. Sorting them alphabetically doesn’t work in this scenario. It is more sensible to sort the list in a chronological order. If you sort it from the alphabet from A-Z, it will order the months alphabetically The month of April will be first, followed by August and February, then June, and the list goes on. However, this isn’t the way you’d like it to be. It is, however, simple to organize chronologically in Excel by using Excel’s Advanced Custom Sort option.
Begin with selecting the table you wish to sort. Select the option ‘Custom Sort’ under ‘Sort and Filter’ under The Editing tab of Home.
In the dialog box for Sort select the column that includes the months of the year. In the column section. Sort using ‘Values’ like usual, and then in the section for Order, select the option ‘Custom List’.
Within the custom list dialog, you can make an individual list. There are a variety of default lists that include days of the weeks, abbreviated months calendar months and more. Select the sorting option based on your needs (in the case of January and February ..) then then click “OK”. After that, click ‘OK’ for sorting your data chronologically.
You’ll notice that you’ve sorted the list according to the month’s order.
How to Sort Alphabetically in Excel Using Excel Formulas
If you’re a lover of formulas, you could make use of Excel formulas to create an alphabetized list. Two formulas that can be used to alphabetize data include COUNTIF as well as VLOOKUP.
For instance, we have an alphabetical list of names that we would like to sort alphabetically using a formula.
To resolve this issue to sort this out, we’ll create a new column called”Sorting Order” to the table we already have.
Within the column (A2) adjacent to the data, apply the COUNTIF formula as follows:
The formula above evaluates the text value in cell B2 against all the different text value (B3:B20) and calculates its rank relative to the other text values. For instance B2 in cell the formula returns 11 since the cell contains 11 values that are less than or equivalent to the text "Nancy" by alphabetical sequence. This results in the sorting order of the employee's name in cell B2.
Use the fill handle to move this formula around to fill it up in all of the range. This will give the sorting order of all the names on the list.
We now need to organize the data in the above image according to the number of the order in which it was sorted To do this, we will make use of the VLOOKUP function.
The word "sort number" represents the numbers that are in ascending order starting from 1 to 20. In order to descend, the numbers should be between 20 to 1.
In this case, we have Column D for names that are sorted. In cell D2, type this VLOOKUP formula:
Similar to the third and second cells, you must apply the formula as follows :
=VLOOKUP(3,A:B,2,0) and the list goes on...
After applying this VLOOKUP formula into each cell that is next to data entry, the list is alphabetized.
Instead of entering manually the formula mentioned above (1-20) for each cell, you may also employ Row function in order to make the work simpler. The row () function will return the row number for the current cell. Therefore, with the aid by the function row, the formula would be:
This formula will yield exactly the same results as the formula above.
Then use your fill lever to move this formula until it fills all of the range.
How To Alphabetize Entries by The Last Name
Sometimes, we have to sort datasheets alphabetically using names that end in. In these cases, regardless of the fact that names begin with the first name it is necessary to sort them according to their last names. This can be done using Excel Text formulas.
To accomplish this first, you'll need to separate the first and last names of full names into two columns. After that, reverse these names and sort them, before reversing them back to their original forms.
With the full name in A2, type in the formulas below in two different cell (B2 as well as C2) Then duplicate the formulas in the columns (using the fill handle) until you reach the final cell that contains information:
To determine the first name, type the following formula into cell C2
To determine your last name type the following formula into cell D2:
Then, in cell E2, we concatenated the names of the first and last in reverse order, separated by a comma
We've separated the names of first and last and reversed the reverse. We now have to sort them alphabetically and reverse them back. You can observe above, when we extract names with the formula columns D, D, and E contain formulas, but they are formatted to appear like values. Therefore, we must transform the formula into values.
To do this, first select all the Formula cells (E1:E31) and then press
Ctrl+C to copy the formula cells. Click on the cells you want to copy and select the Values button under the 'Paste Options then hit the Enter key.
Select a cell within the column, and then click the A to Z or the 'Z to A' button under the 'Data' tab or 'Home' tabs as according to your requirements.
The Sort Warning dialog appears after you click on the button. Select the Expand Selection option, then select the 'Sort' option.
You can clearly see the whole column is alphabetized according to the last name.
If you want to reverse it to the original format of 'First Name First Name format You just need to use the same formulas, but using different references to cells.
It is necessary to break E2 (E2) in two components by applying the following formulas:
In G2 In G2, extract one of the names:
In G2 In G2, pull your last name.
Then join the two parts for the original full name:
Each time you enter the formula into the cell that you first enter it in, be sure you duplicate the formula in the column (using the fill handle) until you reach the final cell that contains information.
All you have be doing is to convert your formulas into values once more like we did in the previous example then you're completed. This is done.