Excel Formula for Name Arrangement: Sort Names A to Z

Formulas & functions · Updated November 18, 2026

For Microsoft 365, use =SORT(A2:A20) to arrange a list of names alphabetically; replace the range with your own. If you mean changing “Maya Chen” to “Chen, Maya,” use a different formula, such as =TEXTAFTER(A2," ")&", "&TEXTBEFORE(A2," ") for names with one space between first and last names. The right formula depends on whether you want to sort names, reverse their parts, or sort by surname.

What name arrangement can mean in Excel

“Arrange names” can describe several different tasks. The most common is sorting a list from A to Z or Z to A. Another is changing the display order from first name followed by last name to last name followed by first name. You might also need to sort whole records by a person’s surname while keeping each person’s department, email address, or other details on the same row.

These are not interchangeable operations. Sorting “Maya Chen” as a complete text entry puts it according to the first letter of Maya, not Chen. Reversing the words changes how the name appears but does not necessarily sort a list. And sorting just the name column in a table can separate names from the other information in their rows. Decide which result you need before choosing a formula.

The examples below use names in column A, beginning in row 2. They assume ordinary text entries rather than separate first-name and last-name fields. If your workbook already stores each name part in its own column, sorting by the appropriate column is usually simpler and more reliable.

Alphabetize a list with SORT

For a live alphabetical list, use Excel’s SORT function. With names in A2:A6, enter =SORT(A2:A6) in an empty cell outside that range. The result spills into the cells below in ascending order: Ava Brooks, Liam Patel, Maya Chen, Noah Kim, and Zoe Alvarez. The formula sorts each complete text entry, so it uses the first character of each full name.

SORT is available in Microsoft 365 and Excel 2021 or later. The formula returns a dynamic array: Excel displays the results in adjacent cells, and the list updates when the source values change. Keep the cells where the results will appear empty; otherwise, Excel may show a #SPILL! error. You do not need to copy the formula down.

To sort in descending order instead, use this formula with names in A2:A6: =SORT(A2:A6,1,-1). The result is the same names in reverse alphabetical order, from Zoe Alvarez to Ava Brooks. In this formula, the second argument identifies the first column of the supplied range, and -1 requests descending order. For a single-column range, the column argument is optional.

For a one-time sort that replaces the current order rather than creating a separate live list, use the ribbon instead: select the list, then choose Data > Sort A to Z or Data > Sort Z to A. If the names are part of a table with related details, select a cell in the table and sort the table so the complete rows move together.

Sort names by surname

A basic alphabetical sort orders full names by their first visible character. To sort by the final word in each name, use SORTBY with TEXTAFTER. With names in A2:A6 and a blank output area, enter =SORTBY(A2:A6,TEXTAFTER(A2:A6," ",-1),1). For the example names above, the result is Zoe Alvarez, Ava Brooks, Maya Chen, Noah Kim, and Liam Patel, ordered by Alvarez, Brooks, Chen, Kim, and Patel.

SORTBY sorts one range according to values in another range or calculated array; here, the calculated sort values are the last words in the names. The -1 argument tells TEXTAFTER to return text after the last space, which is useful if a name includes a middle name. TEXTAFTER and SORTBY are available in Microsoft 365 and Excel 2024. This method assumes the surname is the final word and names contain a space. It will not correctly interpret every naming convention.

If you need a more transparent or compatible setup, create a surname helper column. With full names in A2:A6 and one-space first-and-last names, enter =TEXTAFTER(A2," ",-1) in B2, then fill down. B2 returns Chen if A2 contains Maya Chen. You can then sort the full table by column B. In Microsoft 365 or Excel 2024, you could also sort a separate name list using the helper values; for a permanent sort, the Data tab is often easier to review.

Change “First Last” to “Last, First”

To rearrange the words within a name, put the source name in A2 and enter =TEXTAFTER(A2," ")&", "&TEXTBEFORE(A2," ") in B2. If A2 contains Maya Chen, the result is Chen, Maya. Fill the formula down for the rest of the list. This version uses TEXTAFTER and TEXTBEFORE, which are available in Microsoft 365 and Excel 2024.

The formula treats the first space as the boundary between the two parts. It works for a simple first-and-last-name pattern, but it does not understand titles, suffixes, or every compound surname. If A2 contains Maya Lin Chen, it returns Lin Chen, Maya—not Chen, Maya Lin. Extra spaces can also affect the result. For these reasons, a formula that splits text is a convenience for consistently formatted data, not a universal name parser.

For a worksheet that must work in older Excel versions, with a single space separating first and last names, put the name in A2 and enter =RIGHT(A2,LEN(A2)-FIND(" ",A2))&", "&LEFT(A2,FIND(" ",A2)-1). For Maya Chen, the result is Chen, Maya. This formula finds the first space and takes the text on either side. It can return an error if the cell has no space, and it does not handle middle names as a full-name parser would.

Keep related information with each name

When names sit beside other data, sort the entire record, not just the name column. With names in A2:A5 and departments in B2:B5, enter =SORTBY(A2:B5,A2:A5,1) in an empty area. If the rows are Maya Chen—Sales, Liam Patel—Operations, Ava Brooks—Sales, and Noah Kim—Finance, the result is Ava Brooks—Sales, Liam Patel—Operations, Maya Chen—Sales, and Noah Kim—Finance. Each department remains attached to its original name.

This is useful for creating a sorted view while leaving the source table in its existing order. The output range must have room for both columns and all four rows. If instead you use the Data tab, make sure Excel includes the whole table or range in the sort. When Excel asks whether to expand the selection, choose the option that includes adjacent data. Sorting only one column can assign the wrong department or email to a person.

For more control in a permanent sort, choose Data > Sort. In the Sort dialog, select the column to sort by and choose the order. You can add another level—for example, department first and name second—so that names are alphabetized within each department. If your range has headers, confirm that the data has a header row so Excel does not treat a heading such as “Name” as a person’s entry.

Choose a formula or Excel’s Sort command

A formula is especially helpful when you want a separate, automatically updated view, or when you need to transform name text into a different display format. The source remains unchanged, and a formula-based result can be placed on another worksheet. This is convenient for reports, but it requires enough empty space for the results to spill and a version of Excel that supports the functions used.

The Sort command is a better choice when you want to rearrange the original records, use multiple sort levels, or work with a workbook that must support older Excel versions. It also makes the chosen sort keys visible in a dialog. A manually sorted list does not automatically reorder itself when you edit a name; you would need to sort it again.

  • Use SORT for a simple live list ordered by the full name.
  • Use SORTBY when the sort key differs from the displayed value, such as sorting a full name by its extracted surname.
  • Use Data > Sort to rearrange a table, sort on multiple levels, or avoid dynamic-array formulas.
  • Store first names and surnames in separate columns when accurate sorting and consistent reporting matter.

Prepare names and troubleshoot common problems

Unexpected results often reflect inconsistent source data rather than a problem with the sort formula. One entry may have a leading space, while another has two spaces between its words. Excel sorts the actual text it receives; it does not automatically standardize spelling, spacing, accents, or name structure. Inspect a few source cells when a name appears in an unexpected position.

Common issues and practical checks include:

  • Leading or trailing spaces: clean the source values before sorting. A helper column using TRIM can remove ordinary extra spaces; check the cleaned results before replacing original data.
  • Names that appear twice: a sort changes order, not uniqueness. Identical names remain separate entries unless you separately remove duplicates or use a function designed to return unique values.
  • Middle names and compound surnames: sorting by the final word may not match the surname people use. Separate name fields or a reviewed surname column are safer for formal lists.
  • Numbers or blanks in the range: check whether the selected range includes headings, empty cells, or unrelated values. A formula only sorts the range you give it.
  • #SPILL!: clear cells in the destination area, including merged cells that obstruct the results, then check whether the formula has enough room to return its full output.

Names also do not always follow a simple first-name/last-name pattern. Some people use multiple family names, particles, suffixes, or different ordering conventions. A spreadsheet cannot infer the intended surname from arbitrary text with certainty. For contact lists used in mailings or records, storing the components separately is more dependable than repeatedly splitting a single full-name field.

Frequently Asked Questions

What is the formula to alphabetize names in Excel?
With names in A2:A20, enter =SORT(A2:A20) in an empty cell for an automatically updated ascending list. The function is available in Microsoft 365 and Excel 2021 or later. For a one-time sort, use Data > Sort A to Z.

How do I sort names by last name in Excel?
If each full name has a surname as its final word, Microsoft 365 and Excel 2024 can sort names in A2:A20 using =SORTBY(A2:A20,TEXTAFTER(A2:A20," ",-1),1). For irregular names, use a checked surname column and sort by that instead.

How do I switch first and last names in Excel?
With a simple two-part name in A2, enter =TEXTAFTER(A2," ")&", "&TEXTBEFORE(A2," ") in another cell. Maya Chen becomes Chen, Maya. The formula assumes one space separates the first and last names.

Can I arrange names without a formula?
Yes. Select a cell in the list or table and choose Data > Sort A to Z. For a table with related columns, sort the whole table so all the details in each row stay together.

Why does SORT show #SPILL!?
A dynamic-array formula needs an unobstructed output area. Clear cells where the results should appear, check for merged cells, and make sure the formula is not placed inside a table column where the spill range cannot expand.

Make future name lists easier to sort

For a quick, uniform list, SORT is the simplest answer to “Excel formula for name arrangement.” Use SORTBY when the ordering should follow a different key, and use a text formula only when the names follow a consistent pattern. For records that require dependable surname sorting, keep first and last names in separate columns, preserve related details in the same rows, and check the sorted output before using it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.