How to Organize Things Alphabetically in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Dataset 1


1.1 Organizing 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.

How to Organize Things Alphabetically in Excel by Column

Just like that, your column is now arranged.

Arranged by Column


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.

How to Organize Things Alphabetically in Excel by Column

  • 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).

How to Organize Things Alphabetically in Excel by Column

  • Lastly, click OK to confirm your choice and this completes the process of organizing alphabetically.

Arranged by Column


1.3 Organizing 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.

Dataset 2

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.

How to Organize Things Alphabetically in Excel by Column

  • Next, use the Fill Handle tool to drag down and copy the formula.

Finally, we get the resulting table as depicted in the picture below.

Eventually, the data will be organized in ascending order as shown below.

Arranged by Column

In a similar fashion, you can organize the F5:F13 cells (Project Beta) alphabetically.

How to Organize Things Alphabetically in Excel by Column

Finally, we get the resulting table as depicted in the picture below.

Arranged by Column

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.

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.

Dataset 3

Steps:

  • Firstly, select the entire dataset as shown in the picture below.
  • Then, locate the Data tab, and click the Sort button.

How to Organize Things Alphabetically in Excel in a Range of Cells

  • 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.

How to Organize Things Alphabetically in Excel in a Range of Cells

Finally, click on OK to close the dialog box and your data will be organized.

Arranging a Range of Cells


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 Organizing 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.

Dataset 4

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.

How to Organize Things Alphabetically in Excel by Row

  • Similar to the previous method, a dialog box appears where you have the select the boxes shown in numbered steps in the picture below.

How to Organize Things Alphabetically in Excel by Row

  • Secondly, choose Row 4 (header row) for the Sort by option and press OK to close the dialog box.

How to Organize Things Alphabetically in Excel by Row

Consequently, the data is now organized in ascending order based on the names of the students.

Arranged by Row


3.2 Organizing 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.

Dataset 5

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.

How to Organize Things Alphabetically in Excel by Row

  • In turn, copy the formula for the other cells using the Fill Handle tool.

Arranged by Row

Likewise, repeat the same procedure for the second row.

How to Organize Things Alphabetically in Excel by Row

Eventually, we get the following results as shown in the picture below.

Arranged by Row

Read More: How to Organize Raw Data in Excel


4. Organizing 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.

Dataset 6

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)

How to Organize Things Alphabetically in Excel by Last Name

Step 02: Extract Last Name

  • Secondly, extract the last name using the formula below.

=RIGHT(C5,LEN(C5)-SEARCH(" ",C5,1))

How to Organize Things Alphabetically in Excel by Last Name

Step 03: Combine the Names

  • Thirdly, concatenate the two names in the format Last Name, First Name using the formula below.

=E5&" "&D5

How to Organize Things Alphabetically in Excel by Last Name

  • 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.

How to Organize Things Alphabetically in Excel by Last Name

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.

How to Organize Things Alphabetically in Excel by Last Name

This concludes the process of arranging the groups in alphabetical order.

Arranged by Last Name

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.

Download Practice Workbook

You can download the practice workbook from the link below.


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.


Related Articles


<< Go Back to Organize Data in ExcelData Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo