How to Sort by Name in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, as you add more information to a worksheet, it becomes increasingly critical to organize it. Sorting your data can help you rapidly restructure a worksheet. In this tutorial, we’ll explain to you how to alphabetically sort by name in Excel in a row, column, or by the last name. To do this, we’ll use both the Excel built-in command and Excel formulas.


How to Sort by Name in Excel (3 Examples)

In the following sections, we’ll walk you through 3 alternative ways to sort by name in Excel. To begin, we’ll use the Sort & Filter group, a built-in Excel command from the Data tab, in both rows and columns. After that, we’ll do this task by applying the formulas.

 Apply Formula


1. Use Sort & Filter Group to Sort Columns by Name

We’ll start by showing you an example of how to use the Sort & Filter group to sort data. For example, suppose you have a list of people’s names from various cities around the United States. For instance, you wish to sort people’s names alphabetically. Follow the instructions below to sort columns by name.

1.1 Sort a Column by Name

Step 1:

  • Firstly, select the first element in the column.

Use Sort & Filter Group to Sort by Name

Step 2:

  • From the Ribbon, choose the Data tab.
  • Then, click on the A → Z icon from the Sort & Filter group.

Use Sort & Filter Group to Sort by Name

As a result, you will get the column with a sorted value as shown in the below image.

Use Sort & Filter Group to Sort by Name

1.2 Sort Multiple Columns by Name

Step 1:

  • Click on the Data tab from the Ribbon.
  • From the Sort & Filter group, select the Sort.

Use Sort & Filter Group to Sort by Name

Step 2:

  • From the table, choose any of your preferred columns to sort by. We have chosen the City to sort by first.

Use Sort & Filter Group to Sort by Name

  • Therefore, you will get the sorted value according to the second column.

Use Sort & Filter Group to Sort by Name

Step 3:

  • To sort by another column later, click on the Add Level.
  • From the They by list, choose the column tagged as Names.

Use Sort & Filter Group to Sort by Name

Step 4:

  • Finally, press OK to see the results.

Use Sort & Filter Group to Sort by Name

Read More: How to Sort by Last Name in Excel


2. Use Sort & Filter Group to Sort Rows by Name

You may sort a row by names in addition to sorting by columns. For Example, we have reorganized the Names and City values in a row. Follow the instructions below to complete the sorting in a row.

Use Sort & Filter Group

Step 1:

  • After opening the Sort option from the Sort & Filter group, click on the Options marked option.
  • Select Sort left to right.
  • Then, press OK.

Use Sort & Filter Group

Step 2:

  • From the Sort by menu, choose Row 4.
  • Press Enter to see the results.

Use Sort & Filter Group

Step 3:

  • Press Enter to see the results.

Use Sort & Filter Group

Notes. You can sort the data according to any of your existing rows.

Read More: How to Sort Numbers in Excel


3. Apply Formula to Sort by Name

In addition to the preceding ways, we’ll use formulae to sort data in a column, row, and by their last or first name. Follow the procedures outlined below to complete the task.

 Apply Formula

3.1 Apply Formula to Sort Each Row by Name

Steps:

  • In a cell, type the following formula for the first row in range C4:G4.
=INDEX($C4:$G4, MATCH(COLUMNS($C4:C4), COUNTIF($C4:$G4, “<=”&$C4:$G4), 0))
  • The COUNTIF function will return by counting serially the match between C4:G4.
  • The MATCH function provides the match between columns.
  • Finally, the INDEX function will give us the matched result serially.
  • Importantly, to make it work as an array, press  Ctrl + Shift + Enter 
  • Use the AutoFill tool to fill up the required cells.

 Apply Formula

  • As a result, your rows will be sorted individually as shown in the below image.

 Apply Formula

3.2 Apply Formula to Sort Each Column by Name

Additionally, we have applied formulas for sorting in columns. Simple, follow the outlined steps below.

 Apply Formula

Steps:

  • In a different cell, type the following formula for the range B5:B9.
=INDEX($C4:$G4, MATCH(COLUMNS($C4:C4), COUNTIF($C4:$G4, “<=”&$C4:$G4), 0))
  • To make it work as an array, press Ctrl + Shift + Enter 
  • Finally, apply AutoFill to sort the required cells.

 Apply Formula

  • Therefore, data in columns will be sorted individually alphabetically by names.

 Apply Formula

3.3 Apply Formula to Sort by Last Name

In Excel, you may also sort data by first or last names. We’ll sort them by their last names in the following example. Follow the steps below to sort by last names.

Step 1:

  • To remain the First Names from the Names, type the following formula.
=LEFT(B5,SEARCH(” “,B5)-1)
  • Here the SEARCH function finds the space in B5
  • The LEFT function returns the remaining value after space found from the SEARCH.

Apply Formula to Sort by Last Name

Step 2:

  • To keep the Last Name, type the following formula.
=RIGHT(B5,LEN(B5)-SEARCH(” “,B5,1))

Apply Formula to Sort by Last Name

Step 3:

  • To add the Last Name first and the First Name last, type the formula below.
=D5&”, “&C5

Apply Formula to Sort by Last Name

Step 4:

  • In a different cell, copy the cells and paste them as Value & Number Formatting.

Apply Formula to Sort by Last Name

Step 5:

  • From the Data tab click on the icon below.

Apply Formula to Sort by Last Name

  • Therefore, your data will be sorted by their last names. Now to combine them to give regular shape follow the steps.

Apply Formula to Sort by Last Name

Step 6:

  • Type this formula to keep only the First Name.
=RIGHT(B12, LEN(B12) – SEARCH(” “, B12))
  • In cell C12, the RIGHT function provides the value from the B12 cell with the number of characters left from applying the SEARCH function after space.

Apply Formula to Sort by Last Name

Step 7:

  • Type the following formula to keep the Last Name.
=LEFT(B12, SEARCH(” “, B12) – 2)

Apply Formula to Sort by Last Name

Step 8:

  • Finally, just combine both cells with the following formula.
=C12&” “&D12

Apply Formula to Sort by Last Name

  • Therefore, in the below image, you can see the sorted data by their last names.

Apply Formula

Read More: How to Sort in Excel by Number of Characters


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To conclude, I hope this tutorial has shown you how to sort by name in Excel using the Sort & Filter command and applying formulas. All of these strategies should be taught to your data and used to it. Look over the practice book and put what you’ve learned to use. We are willing to reimburse programs like this because of your support.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.


Related Articles


<< Go Back to Sort in 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.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo