Google Sheets: How to Sort by Last Name

Yes. Google Sheets can alphabetize a list by last name, but it does not automatically identify surnames inside a full-name cell. Add a helper column that extracts each name’s final word, then sort the entire table by that column—or use a sort formula to display a sorted copy.

Why a helper column is usually needed

If a cell contains a full name such as “Maya Chen,” Sheets treats it as one text value. An A-to-Z sort of that column compares the text from the beginning, so Maya Chen would be sorted by Maya, not Chen. Sheets has no built-in “sort by last name” option that reliably interprets every person’s name.

A separate last-name column makes the sort key explicit. It also lets you check the values before rearranging the data. This matters because real names do not all follow the simple first-name, last-name pattern: a surname can have multiple words, a person may use a suffix, and some lists put the surname first.

For a straightforward list with one full name per cell, a formula can extract the final word. For a more complicated directory, entering or maintaining surnames in a dedicated column is often more dependable than trying to infer them.

Extract the final word into a helper column

Suppose full names are in A2:A and column B is available. Put a heading such as Last name in B1. With the name in A2, enter this in B2:

=IF(A2="","",REGEXEXTRACT(TRIM(A2),"[^ ]+$"))

This formula trims extra spaces at the beginning and end, then returns the last space-separated part of the name. For example, “Maya Chen” returns “Chen,” and “ Luis García ” returns “García.” The IF check leaves the helper cell blank when A2 is blank. Copy the formula down the helper column, or use the fill handle to apply it to the rows you need. The formula uses commas between arguments; some spreadsheet locales require semicolons instead.

The formula is a convenience, not a universal name parser. For “Mary Ann Smith,” it returns Smith, which is suitable if Smith is the surname. For “Ludwig van Beethoven,” it returns Beethoven and leaves “van” out of the sort key. For “Smith, Mary,” it returns Mary, which is wrong if the surname is written first. It will also treat a suffix such as Jr. or III as the final word. Review the helper values and correct exceptions before sorting.

Sort the names and their related information

Once the helper column is ready, select the full data range—not just the name column. Include every column that belongs to each record, such as email address, department, or phone number. In Google Sheets in a web browser, choose Data > Sort range > Advanced range sorting options. If the first row contains headings, select the option indicating that the data has a header row. Choose the Last name column as the sort key, select A to Z, and apply the sort.

Sorting the whole range keeps each person’s details on the same row. Sorting only column A can put one person’s name beside another person’s email address, creating a misleading directory. If your data has a filter, you can also use its column menu to sort A to Z by the helper column; make sure the filter covers the complete table.

After checking the result, you can keep the helper column for future sorts or hide it. If you delete it, the existing row order remains, but you will need to recreate a sort key the next time you want to alphabetize by surname. Avoid deleting the helper before checking whether any formulas or other processes depend on it.

Use a formula to show a sorted copy

If you want the original table to stay in place, use a formula to generate a sorted view elsewhere. For example, if names are in A2:A100 and their extracted surnames are in B2:B100, enter this in an empty area:

=SORT(A2:A100,B2:B100,TRUE)

The result is the names from A2:A100 arranged in ascending order using the corresponding surname values in B2:B100. The two ranges must cover the same rows. This approach does not rearrange the source data; changes to the source can update the displayed result. Leave enough empty space below the formula for the results to expand, and do not type into cells where the output needs to appear.

For a table with more than one displayed column, use the same idea with the complete table range. For example, if names and email addresses are in A2:B100 and helper surnames are in C2:C100, use =SORT(A2:B100,C2:C100,TRUE) in an empty area. It returns both table columns, ordered by the helper values. The helper range is the sort key and does not have to be included in the displayed result. A formula-generated view is useful for reporting, while the menu sort is more suitable when you need to change the order of the original records.

Choose between a menu sort and a formula

Use the menu when you want to reorder the actual rows, share the sorted table as the main working copy, or make a one-time alphabetized list. Use SORT when you want a live sorted display without changing the source. A helper column supports either method and makes the sort logic visible to other people using the sheet.

Menu sort: rearranges the selected records in the source range. Select all columns that belong together.
SORT formula: displays an ordered result in another location and leaves the source range untouched.
Manual surname column: takes more upkeep, but gives you direct control over compound surnames, suffixes, and unusual name formats.

For a short, predictable contact list, extracting the last word may be enough. For a staff, membership, or customer directory that needs accurate surname ordering, a separate surname field is the safer long-term design. If you can change the way the data is collected, store given name and surname in separate columns from the start.

Handle compound names, suffixes, and duplicates

Decide what “last name” means for your list before sorting. A person with a compound surname may expect both words to stay together as the surname. A final-word formula will sort “De la Cruz” under Cruz, while sorting by a manually supplied surname field could keep “De la Cruz” together. Likewise, decide whether a suffix belongs in the surname sort key or should be ignored for ordering.

Duplicate surnames are normal. A sort by surname alone does not promise a particular order among people with the same surname. If you want those rows ordered by given name as a secondary key, add another sort level in the advanced range sort options. With a formula, SORT can accept additional sort keys. For instance, if the full table is A2:C100, surnames are in D2:D100, and given names are in E2:E100, use =SORT(A2:C100,D2:D100,TRUE,E2:E100,TRUE). This orders by surname first, then given name. Make sure both key ranges align row for row with the table.

Names containing extra spaces or inconsistent punctuation can also create unexpected-looking keys. TRIM helps with ordinary repeated spaces, but it cannot decide how a name should be indexed. Check the extracted column for blanks, suffixes, and exceptions rather than assuming every row has been interpreted correctly.

Check the sort range, headings, and locale

Before applying a sort, verify that the selected range includes all records and related columns, but excludes unrelated notes or a second table. Mark the header row when the range-sort dialog offers that option so that a heading such as Last name is not treated as a person’s record. For large sheets, it is worth checking the first and last few rows after sorting and confirming that sample names still match their original details.

Alphabetical ordering can vary with the spreadsheet’s locale, especially for accented characters and language-specific conventions. If the order of names with accents matters, check the spreadsheet’s locale under File > Settings and use a setting appropriate for the data. Locale affects more than sorting, so changing it can influence other spreadsheet behavior too. It is usually better to confirm the intended convention before adjusting a shared sheet.

Google Sheets and Excel both allow sorting a range by a separate key column, but their menus and formula behavior are not identical. The steps and formulas here are for Google Sheets in a web browser. In Excel, use its own sort controls and function documentation rather than assuming the Google Sheets menu path applies.

Common mistakes to avoid

Sorting the full-name text directly: this alphabetizes by the first part of the cell, usually the given name.
Selecting only the name cells: related details may no longer stay with the correct person.
Trusting every extracted value: a formula cannot reliably interpret all naming customs, suffixes, or surname-first formats.
Putting a SORT formula inside its own output area: the result needs a clear, empty area in which to expand.
Forgetting the header: include and identify the header row appropriately so it stays a heading.

If the result looks wrong, first check the helper column, then confirm that the sort range and key ranges cover the same records. A correct sort key cannot fix a misaligned range, and a correctly selected range cannot fix a surname that was extracted incorrectly.

Frequently Asked Questions

Can Google Sheets sort by last name automatically?
No. It sorts the values you provide, but it does not reliably recognize which word in a full name is the surname. Create a surname helper column or keep surnames in their own field, then sort by that column.

How do I alphabetize full names by surname without changing the original list?
Make a helper column with extracted surnames, then use SORT in an empty area with the original names as the data range and the helper cells as the sort key. The sorted result appears separately, leaving the source list in place.

Can I sort by last name and then first name?
Yes. In the range-sort options, add given name as a second sort column after surname. With SORT, provide the surname range and then the given-name range as additional sort keys, in that order.

Will a formula handle names like van der Meer or Smith, Jr.?
Not reliably. A formula that returns the final word will use Meer for “van der Meer” and Jr. for “Smith, Jr.” If those cases need a particular order, maintain a dedicated surname field or correct those helper cells manually.

Can I sort names by last name when the surname comes first?
Yes, but use a key that reflects the actual format. If a cell reads “Smith, Mary,” extracting its final word returns Mary, not Smith. A separate surname column or a formula designed for a consistent comma-separated format is more appropriate.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.