How to sort by last name in Excel (Complete Guide)

Sorting by the last name may be one of the most common operations in WPS Excel, and yet many still don't know how to do it effectively. However, an easy solution will allow you to sort your data in Excel by last name (or any other value) and get back the results you want every time. Follow these steps to learn how to sort in excel by last name with WPS Academy!

How to sort by last name using Formula

1. Start out by entering all of your clients, customers, or students into an Excel spreadsheet.

2. Enter their first names and their last names where applicable.

3. Extract last name from full name using formula =RIGHT(A2,LEN(A2)-FIND( ,A2))

4. Click Sort from the Data menu to start sorting them alphabetically.

5. The Sort menu is located at the top left corner of your screen next to Home and Insertso click on sorting after you select all your columns

6. You will see a list that includes A-Z, which you can use to sort your list alphabetically by the last name.

Sorting by Last Name using Text to Column Method:

1. Select your data and go to data and then to Text to Columns:

2. Click on text to Columns and then select delimited and go to next:

3. Click on next then write value of the cell and click finish to sort by last name

4. Finish and see the project:

Sorting by Last Name Manually:

1. Select all data entered and make two columns by simply copy pasting

2. Open find and replace using Ctrl+H keys:

3. Write * along with a space  in the first tab and leave second tab blank:

4. Click on replace all to delete first names

5. Since now all surnames are separated follow sorting option in WPS Excel like in previous functionality to achieve sorting by last name.

The final results!

We are done sorting! Now, let's go to our sort results tab to have a look at what we have accomplished. First of all, you can see that there are still many duplicates as you scroll down because we didn't do anything about those yet. Second of all, it looks like this data should be sorted alphabetically instead of numerically because the person's name will be different from the person who has 1st place for example. However, once again I want to point out that it really depends on what kind of report you want for your analysis or research project so sometimes this might not be ideal. You can always use these methods to adjust things later on but if you don't need an alphabetical order just keep scrolling down until you see How To Sort In Excel By Last Name- A Wrap-Up.

All instructions work best for 2016/2019/mac/online.

Did you learn about how to sort by last name  in excel using different methods 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 word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.