How to Sort Excel by Last Name

To sort Excel by last name when the full name is in one column, first pull the last names into a helper column. Type the first person’s last name in the cell next to their name, press Ctrl + E to fill the rest with Flash Fill, then select your data and use Data > Sort to sort by the new column from A to Z.

Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016 on Windows and Mac. Checked against Microsoft Support on October 10, 2026.

If last names already have their own column

1. Click any cell in your data.
2. Go to Data > Sort.
3. Check My data has headers.
4. Under Sort by, choose the last name column and A to Z.
5. Select Add Level, choose the first name column, and select OK. People with the same last name are then sorted by first name.

For more on multi-level sorting, see how to sort by two columns in Excel.

Step 1: Get the last names (full name in one column)

Pick whichever method suits your version of Excel. Each puts the last name in a new column next to your names.

Option A: Flash Fill

1. Insert an empty column next to the names and give it a heading, such as Last Name.
2. In the first row, type the last name exactly as it appears, for example Smith for “Jane Smith”.
3. Press Ctrl + E, or go to Data > Flash Fill. Excel fills in the rest.
4. Scan the results. Flash Fill guesses from your example, so check names with middle names or suffixes.

Option B: A formula (Microsoft 365)

In the helper column, type =TEXTAFTER(A2," ",-1) and fill it down. The -1 tells Excel to take the text after the last space, so “Mary Ann Jones” returns “Jones”.

Option C: A formula (older versions)

Use =RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))). It finds the last space and returns everything after it.

Option D: Find and Replace

1. Copy the names into the helper column.
2. Select the helper column and press Ctrl + H.
3. In Find what, type an asterisk followed by a space (* ). Leave Replace with empty.
4. Select Replace All. Everything up to the last space is removed, leaving the last names.

Step 2: Sort by the helper column

1. Click any cell in your data.
2. Go to Data > Sort, and check My data has headers.
3. Choose Last Name under Sort by and A to Z.
4. Select OK.

Excel moves whole rows, so each person’s other details stay with them. You can hide the helper column afterward, or delete it once you’ve pasted the formulas as values.

Sort with a formula instead (Microsoft 365)

To create a sorted copy that updates automatically, use =SORTBY(A2:C50,TEXTAFTER(A2:A50," ",-1),1), where A2:C50 is your table and column A holds the full names. The original data stays as it is.

Names that need special care

• Suffixes like Jr. or III: the formulas return the suffix as the last name. Remove suffixes into their own column first, or fix those rows by hand.
• “Last, First” format: The last name is already first. Sort the column directly, or split it with Text to Columns using the comma.
• Double surnames like “García López”: the formulas only take the final word. Check these manually.
• Extra spaces: Clean the names first with =TRIM(A2), or the results may come out blank.

If the sort doesn’t work

• Only one column moved: You selected a single column before sorting and chose Continue with the current selection. Press Ctrl + Z and sort again with Expand the selection.
• The heading was sorted into the list: Check My data has headers.
• The order looks random: Some last names start with a space or are stored as numbers. Use TRIM and check the cell format.
• Flash Fill did nothing: Turn it on in File > Options > Advanced > Automatically Flash Fill.

Frequently asked questions

Can I sort by last name without adding a column?

Only with the SORTBY formula in Microsoft 365. The Sort dialog can only sort by columns that exist.

How do I put first and last names back together?

See how to merge first name and last name in Excel.

Does this work in Google Sheets?

The Find and Replace and helper column methods work there too. Google Sheets uses Data > Sort range instead of the Sort dialog.