How to Sort by Last Name in Excel (Step by Step)

July 22, 2022

A free Office suite fully compatible with Microsoft Office

Free Download

A free Office suite fully compatible with Microsoft Office

Free Download

Information can be sorted sequentially according to the full name fairly easily, but Excel sorts by the first person of the name.

However, consider a situation where you need to sort data in Excel by the last name.

Even though it's not quite evident, it should still be doable (a ton likewise relies upon how names information is organized).

Whatever method you use, you should somehow split the last name from the whole name and place it in a new part. Following that, you can use this part to order your data by the last name. You can use the segment that finally triumphs the last name to use the segmented components in separate sections to organize the material. When the results are available, sort data by the last name. When using Text to Columns and using a comma as the separator, you can also separate first and last names.

Extract and Sort data by Last Name Using Find and Replace

Putting the last name in a distinct segment is the first step in ordering by last name.

To accomplish this, simply replace everything up to the last name with a clear space, ultimately winning only the last name remaining.

Let's say you have the dataset shown below, and you need to order the information using last names.

Below are the steps to sort data by the last name:

  1. Pick the dataset with the heading (in this example, it would be A1:A10)

  2. Paste it in the subsequent column (if the adjacent column is not empty, insert a new column and then copy these names).

  1. Change the copied column heading's name. I'll use Last Name as an example.

  2. Choose every name that was copied; do not select the heading.

  3. While holding down the Control key, press the H key. The Find and Replace dialogue box will then be displayed.

  1. In the Find what field, enter * (asterisk symbol followed by a space character).

  2. Leave the Replace with field empty.

The procedures mentioned above would maintain the last name and eliminate anything else. Even if you have middle names or prefixes (like Mr. or Ms.), this still works nicely.).

Below are the steps to sort data by the last name:

  1. Select the entire data set with headers (including the full names and the extracted last names). You can also include other columns that you want to sort data by the names

  2. Click the Data tab

  1. Click on Sort

  1. In the Sort dialog box, make sure ‘My data has headers is selected.

  2. In the ‘Sort by; option, select the name of the column that just has the last name

  1. In the ‘Sort On, select Cell Values

  1. In the Order option, select o;A to Z

  2. Click OK

The aforementioned procedures would use last names to order the full specified dataset.

Once finished, remove the column containing the last name.

Did you learn about how to Sort data by Last Name in Excel Spreadsheets? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.