Sorting data in Microsoft Excel by last name can often be necessary, especially when managing lists like customer databases or employee directories. While Excel does not have a built-by-last name function, there are several methods you can use to achieve this. Here's how to sort by last name efficiently:
Method 1: Using Formulas
One of the most effective ways to sort by last name when names are in one column:
- Create Separate Columns: Insert two new columns next to the column containing names.
- Extract First Name: Use the formula
=LEFT(A2,FIND(” “,A2)-1)to extract the first name. This formula finds the first space in the name and extracts everything before it. - Extract Last Name: Use the formula
=RIGHT(A2,LEN(A2)-FIND(””,SUBSTITUTE(A2,” “,””,LEN(A2)-LEN(SUBSTITUTE(A2,” “,”“)))))to get the last name. This complex formula extracts the last word in the cell by replacing spaces with asterisks and finding the last asterisk. - Sort by Last Name: Select your list and sort by the last name column.
🔎 Note: Ensure your data range does not include blank cells or additional spaces in the names, which could affect the formulas.
Method 2: Using Power Query
Power Query provides a more automated solution:
- Import Data: Go to “Data” > “Get & Transform Data” > “From Table/Range”.
- Extract First Name: Add a column using
=Text.BeforeDelimiter([Name], “ “)to get the first name. - Extract Last Name: Use
=Text.AfterDelimiter([Name], ” “)for the last name. - Sort: Select the last name column to sort.
- Load Data: Click “Close & Load” to apply changes back to Excel.
🔍 Note: Power Query can sometimes be a bit heavy if you’re dealing with large datasets; ensure your system has enough memory.
Method 3: Text to Columns
If your names follow a consistent pattern:
- Select the Column: Choose the column with names.
- Text to Columns: Use Data > Text to Columns, choose “Delimited” and separate by space.
- Sort by Last Name: After splitting, select the last column (which should now contain last names) and sort.
| Step | Description |
|---|---|
| Select Column | Click on the column header to select all cells in the column with the full names. |
| Choose Delimiter | In the wizard, select “Delimited” and click “Next”. |
| Separate by Space | Check the “Space” option to split names into different columns. |
Each of these methods has its strengths; choose the one that best fits your dataset and workflow. Whether you opt for formula-based solutions for a quick sorting job or leverage the power of Power Query for more complex data manipulation, Excel provides various tools to ensure your data is organized as needed.
Can you sort by last name without splitting the names into separate columns?
+
Directly in Excel, you need to split names into separate columns to sort by last name. However, you can use a helper column to achieve this without permanently altering your original data.
What if the names have middle names or suffixes?
+
Formulas and Power Query will need to be adjusted. For instance, you might need to extract names differently or use additional steps to handle middle names or suffixes correctly.
How can you preserve the original data order after sorting by last name?
+
Add a column with sequential numbers to track the original order, sort by last name, and you can then return to the original order if needed by sorting by the number column.
