5 Easy Ways to Sort Last Names in Excel

Sorting data by last names in Excel is an essential skill for anyone dealing with lists of names, be it for organizing customer databases, mailing lists, or employee records. Whether you're working with a small group or an extensive list, Excel provides several straightforward methods to streamline this task. Here's how you can efficiently sort names by last names in Excel.

1. Using the Sort Feature with a Helper Column

The simplest approach involves adding a helper column to extract the last name before sorting:

• Insert a new column next to your list of names.
• In the new column, use the formula =RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(””,SUBSTITUTE(TRIM(A2),” “,””,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,”“))))) to extract the last name from the full name.

💡 Note: This formula assumes names are entered as “FirstName LastName”. Adjust the formula if names have different formats.

• Copy this formula down the column to apply it to all names.
• Select the entire data range including headers, then go to ‘Data’ > ‘Sort’ > ‘Sort by’ your helper column.

2. Using Text-to-Columns to Split Names

Excel’s Text-to-Columns feature can also be used to sort by last names:

• Highlight the column with full names.
• Go to ‘Data’ > ‘Text-to-Columns’, choose ‘Delimited’.
• Select ‘Space’ as the delimiter, then click ‘Next’.
• Set the ‘Destination’ to the next available columns.
• Sort using the newly created last name column.

3. Employing Flash Fill for Name Sorting

Flash Fill, available in Excel 2013 and later, can automatically recognize patterns:

• Enter the last name of the first person in the list manually.
• Excel might suggest the rest. If it doesn’t, click ‘Data’ > ‘Flash Fill’ or press Ctrl+E to enable it.
• Once Flash Fill has filled in all the last names, sort the sheet by this column.

🔍 Note: Flash Fill might need some manual correction for unusual name formats.

4. Advanced Filtering with Array Formulas

For larger datasets or when you need more control over how names are split:

• Use an array formula to extract last names: =INDEX(A2:A1000, MATCH(TRUE, ISERROR(FIND(””, SUBSTITUTE(A2:A1000, “ “, “”, LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000, “ “, “”))))), 0)).
• Press Ctrl+Shift+Enter to enter the array formula.
• Sort based on this new column with last names.

5. Using VBA for Automated Sorting

If you frequently need to sort by last names, you might consider using a VBA macro:

• Press Alt+F11 to open the VBA editor.
• Insert a new module and paste the following code:

  Sub SortByLastName()
      Dim rng As Range
      Set rng = Selection
      rng.Sort key1:=rng.Cells(1, rng.Columns.Count), order1:=xlAscending, Header:=xlYes
  End Sub
  

• Run this macro after selecting your data range to sort by the last name column.

Summing Up Key Strategies

Sifting through lists of names in Excel for organizing or sorting purposes need not be tedious. By employing these five strategies, you can effortlessly sort names by their last names. From the simplicity of a helper column to the efficiency of VBA macros, Excel offers tools for every level of expertise. Remember that each method has its benefits, so choose the one that best aligns with your dataset size, complexity, and frequency of sorting. Efficient sorting not only saves time but also ensures better organization and data management, enhancing your productivity in Excel.