Fix Incorrect Alphabetical Sorting of Names in Excel
Question details
The user needs to sort a large list of names alphabetically by last name and then first name.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sorting a list that contains separate last-name and first-name columns.
- Observed behavior
- Names do not sort as expected, potentially because leading or trailing spaces make similar-looking entries different.
Identify the last-name and first-name columns and check whether the list has headers. Make sure there are two empty columns available for cleaned names.
Remove extra spaces and sort by cleaned name columns
Use helper columns to remove ordinary extra spaces before sorting the entire list by last name and first name.
In an empty cell, enter =A173=A174 to compare adjacent last names in column A, then fill the formula down. TRUE means the values match under Excel's case-insensitive comparison; FALSE indicates a difference.
If last names are in column A and first names are in column B, enter =TRIM(A2) and =TRIM(B2) in two empty columns. Give the helper columns clear headers, such as Clean Last Name and Clean First Name.
Select each formula cell and drag its fill handle down to the last row of names.
Select the entire table, including the helper columns, and choose Data > Sort. Select Clean Last Name with A to Z order, then use Add Level to sort Clean First Name from A to Z. Confirm that the header option is selected if the table has headers, and click OK.

Clean and sort name lists in WPS Spreadsheets
Use TRIM helper columns in WPS Spreadsheets to remove extra spaces, then sort the full table by cleaned last and first names.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Create helper columns: In two empty columns, enter =TRIM(A2) for last names and =TRIM(B2) for first names, adjusting the references to match your sheet. Add headers to both columns.
- 3. Fill down: Drag the fill handle from each formula cell down through the name list.
- 4. Sort the table: Select the full table and choose Data > Sort. Set the cleaned last-name column to A to Z, add the cleaned first-name column as the next sort level, and confirm the sort.

Frequently Asked Questions
Why do identical-looking names sort separately in Excel?
Leading, trailing, or repeated spaces can make names different. Use =TRIM(A2) in a helper column to remove ordinary extra spaces before sorting.
How can I compare two adjacent last names?
Enter =A173=A174 in an empty cell and fill it down. TRUE indicates a match using Excel's case-insensitive comparison; FALSE means the values differ.
How do I sort by last name and then first name?
Select the entire table and choose Data > Sort. Set the last-name column to A to Z, then select Add Level and set the first-name column to A to Z.
What if TRIM does not remove the spaces?
The cells may contain nonbreaking spaces. Try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in a helper column, then sort using the cleaned values.




