Eshrak Kader
Organizing data alphabetically is one of the most basic tasks in Excel. Indeed, it is also a valuable tool when working with large sets of data. Keeping this in mind, this article aims to explain 4 useful methods on how to organize things alphabetically in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
4 Methods to Organize Things Alphabetically in Excel
Fortunately, Microsoft Excel offers built-in tools and options to organize data alphabetically. So, without further delay, let’s see them in action.
1. Organizing Columns Alphabetically in Excel
Let’s consider the following dataset shown in B4:C14 cells. Here, the first column shows the Employee Name, while, the second column displays the Sales amount in USD.
1.1 Organize a Single Column
Organizing a column alphabetically can be done very quickly and easily. Just follow these steps.
Steps:
- Firstly, select any cell in the column you want to organize.
- Next, go to the Data tab, in the Sort & Filter group and click A-Z for organizing in ascending order or Z-A to arrange in descending order.
Just like that, your column is now arranged.
1.2 Using Filter Option to Organize a Column
Another easy way to organize data is to use Excel’s Filter Option. For one thing, the Filter Option allows you to organize multiple rows in the dataset. Let’s see how.
Steps:
- At the very beginning, select any of the column headers in your dataset.
- Secondly, navigate to the Data tab and select Filter.
- Following, a small drop-down arrow appears to the right of each of the column headers.
- Next, click the drop-down arrow and select Sort A to Z (ascending order).
- Lastly, click OK to confirm your choice and this completes the process of organizing alphabetically.
1.3 Organize Each Column Alphabetically with Formula
Those of you, who want to employ Excel functions to organize your data, you’ve come to the right place. This is because you can combine the INDEX, MATCH, ROWS, and COUNTIF functions to organize the data in separate columns. Allow me to explain.
Suppose we have the following dataset shown in B4:C13 cells. Here, the first column shows the Employee Names designated in Project Alpha, and, the second column depicts the personnel in Project Beta.
Steps:
- Initially, make a copy of the columns without the data.
- Then, select the first cell and insert the following expression.
=INDEX(B$5:B$13,MATCH(ROWS(B$5:B5),COUNTIF(B$5:B$13,"<="&B$5:B$13),0))
- As a note, please remember to press CTRL + SHIFT + ENTER if you’re not a Microsoft 365 user.
- Next, use the Fill Handle tool to drag down and copy the formula.
Eventually, the data will be organized in ascending order as shown below.
In a similar fashion, you can organize the F5:F13 cells (Project Beta) alphabetically.
Finally, we get the resulting table as depicted in the picture below.
Formula Breakdown
- Firstly, the ROWS function uses the B$5:B5 cells (array argument) and assigns a number to each row.
- Secondly, the COUNTIF function considers the array B$5:B$13 (range argument) and based on “<=”&B$5:B$13 (criteria argument) determines the number of values less than and equal to the number that is going to be ranked.
- Thirdly, the MATCH function matches the row number (lookup argument) based on the ranked row numbers (lookup array). Here, the output of the ROWS and COUNTIF functions are used as lookup_value and lookup_array arguments respectively. Also, 0 (match_type argument) refers to the exact match of the string of text.
- Lastly, the INDEX function takes the B$5:B$13 (reference argument) array and MATCH(ROWS(B$5:B5),COUNTIF(B$5:B$13,”<=”&B$5:B$13),0) (row_num argument) to return the string of text in alphabetical order.
Read More: How to Organize Data in Excel from Lowest to Highest (with Quick Steps)
2. Organizing a Range of Cells Alphabetically in Excel
In case you’re data consists of several columns and you want to arrange them. Then, our method has you covered. Let’s see the step-by-step process.
Assuming we have the dataset shown below in B4:D13 cells. Here, the first column shows the Employee Names, followed by the second column which displays the Sales amount in USD, and lastly, the third provides the Country the store is located.
Steps:
- Firstly, select the entire dataset as shown in the picture below.
- Then, locate the Data tab, and click the Sort button.
- Next, you’ll be greeted with a dialog box where we can order the Employee Name column in ascending order.
- As a note, it is important to check the My data has headers option else, Excel will arrange the headers too.
Finally, click on OK to close the dialog box and your data will be organized.
3. Organizing Rows Alphabetically in Excel
So far in this article, we’ve only discussed organizing columns but, you can also organize rows if your dataset is arranged horizontally. Let me demonstrate.
3.1 Organize Multiple Rows
The process of organizing rows is remarkably similar to that of columns. Let’s discuss it in detail.
Let’s consider the following dataset shown in the B4:I8 cells. The table shows the marks of each student in 4 of the subjects i.e., Physics, Chemistry, Biology, and Math.
Steps:
- Firstly, select the entire dataset (including the column headers) as portrayed below.
- Next, on the Data tab, in the Sort & Filter group, select the Sort button.
- Similar to the previous method, a dialog box appears where you have the select the boxes shown in numbered steps in the picture below.
- Secondly, choose Row 4 (header row) for the Sort by option and press OK to close the dialog box.
Consequently, the data is now organized in ascending order based on the names of the students.
3.2 Organize Each Row with Excel Formula
Similar to the formula for arranging columns, we can arrange rows alphabetically using the INDEX, MATCH, COLUMNS, and COUNTIF functions. It’s a simple process, so, just follow along.
Let’s assume, the dataset shown in B4:H5 cells. the first row shows the Employee Names designated in Project Alpha, and, the second row depicts the personnel in Project Beta.
Steps:
- To begin, select the first cell and insert the following expression.
=INDEX($C4:$H4,MATCH(COLUMNS($C2:C2),COUNTIF($C4:$H4, "<="&$C4:$H4), 0))
- Additionally, the COLUMNS function provides the column number in the given reference.
- As a note, please remember to press CTRL + SHIFT + ENTER if you’re not a Microsoft 365 user.
- In turn, copy the formula for the other cells using the Fill Handle tool.
Likewise, repeat the same procedure for the second row.
Eventually, we get the following results as shown in the picture below.
Read More: How to Organize Raw Data in Excel (6 Easy Methods)
4. Organize Last Names Alphabetically in Excel
Our last method discusses how to arrange a column alphabetically by the last names of the people. This method combines the LEFT, RIGHT, and SEARCH functions to organize the names alphabetically.
Suppose we have the following dataset shown in B4:C13 cells. The table shows the Project name and the Full Name of the employees assigned to each Project respectively.
Step 01: Extract First Name
- To begin, insert a column to store only the first name.
- Next, extract the first name using the expression given below.
=LEFT(C5,SEARCH(" ",C5)-1)
Step 02: Extract Last Name
- Secondly, extract the last name using the formula below.
=RIGHT(C5,LEN(C5)-SEARCH(" ",C5,1))
Step 03: Combine the Names
- Thirdly, concatenate the two names in the format Last Name, First Name using the formula below.
=E5&" "&D5
- Now, copy the names in the F5:F13 cells using CTRL+C and paste only the values using Paste Options as shown in the image below.
- As a note to the previous step, we want to organize the names alphabetically not the formulas in the cell.
Step 04: Organize Last Names Alphabetically
- Finally, select the F5:F13 cells and go to the Data tab ribbon at the top.
- Now, click the A-Z icon to organize the Last Names alphabetically.
This concludes the process of arranging the groups in alphabetical order.
Formula Breakdown
- In this formula, the SEARCH function locates the space character(find_text argument) in the C5 cell (within_text argument) and returns its starting position.
- Then, the LEFT function extracts text from the left side of the C5 cell (text argument) up to the position SEARCH(” “,C5)-1 (num_chars argument) which is just before the space character.
- Lastly, the RIGHT function works in the same way as the LEFT function except that it extracts the string of text from the right side.
Things to Remember
- Make sure there are no blank entries in the list to sort. Blank entries occur when importing data from other applications. If there are blank rows present then delete them before sorting.
- If your table has headers it’s important to distinguish them from the data using formatting otherwise, Excel will include the header as an item to be sorted.
Conclusion
To conclude, I hope this article helped you understand how to organize things alphabetically in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
