Table of Contents
Are you looking for an efficient way to sort data in Excel by last name? Excel is a powerful spreadsheet program that can help you sort data quickly and easily. In this article, we’ll show you how to sort by last name in Excel using an example. We’ll also explain a few tips and tricks to make sorting data even easier. Keep reading to learn more!
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.