How to sort alphabetically in excel by last name

Answer

To sort alphabetically in Excel by last name, you must first ensure that Excel can recognize the last name separately from the first name. Excel sorts data based on the cell content, so separating last names is necessary when names are in a "First Last" format.

Step 1: Separate Last Names

1. Suppose the full names are in column A, starting from cell A2.

2. Insert a new column B next to the full names.

3. In cell B2, enter the following formula to extract the last name:

   =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

4. Press Enter. This formula finds the last word in the full name, assuming the last word is the last name.

5. Drag the formula down to apply it to all rows in column B.

Step 2: Sort by Last Name

1. Select all the data, including both columns (full names and last names).

2. Go to the Data tab on the Excel ribbon.

3. Click Sort.

4. In the Sort dialog box:

   • For Sort by, choose the column containing the last names (column B).

   • Choose Sort On → Cell Values.

   • Choose Order → A to Z for ascending order or Z to A for descending order.

5. Click OK. The data will now be sorted alphabetically by last name.

Step 3: Remove Helper Column (Optional)

1. After sorting, you can delete the helper column B if you no longer need it.

2. The full names in column A will remain sorted by last name.

Notes

• If names contain middle names or multiple spaces, the formula still captures the last word as the last name.

• On Mac, the steps are the same, but the Sort dialog may appear slightly different. Ensure the correct column is selected for sorting.

• This method works for lists with hundreds or thousands of names efficiently.

This approach ensures accurate alphabetical sorting by last name without manually separating each entry.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report