You can use a simple two-step process to sort rows based on last name in Excel:
Step 1: Create new column that contains last name.
Step 2: Use Sort feature to sort by last name.
The following step-by-step example shows how to sort rows by last name in Excel in practice.
Step 1: Enter the Names
Suppose we have the following list of names in Excel:
Step 2: Create New Column that Contains Last Name
We can use the following formula to extract the last name from each full name:
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
We can type this formula into cell B2, then drag and fill it down to the remaining cells in column B:
Column B now contains the last name of each full name in column A.
Notice that the formula works whether or not the name contains a middle name.
Note: This formula works by replacing the last space in the name with an asterisk ( * ) and then uses the FIND function to locate the asterisk and extract a certain number of characters in the name from the right.
Step 3: Sort by Last Name
Lastly, we can highlight the range A1:B11, then click the Data tab and then click the Sort icon within the Sort & Filter group:
In the new window that appears, choose the column titled Last Name in the dropdown menu for Sort by:
Once you click OK, the rows will be sorted by last name from A to Z:
Note that you can also delete the column titled Last Name if you don’t actually want to keep a separate column with the last names of each person.
The following tutorials explain how to perform other common tasks in Excel: