How to sort by last name in Excel (Complete Guide)
A free Office suite fully compatible with Microsoft Office
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
Start out by entering all of your clients, customers, or students into an Excel spreadsheet.
Enter their first names and their last names where applicable.
Extract last name from full name using formula =RIGHT(A2,LEN(A2)-FIND( ,A2))
Click Sort from the Data menu to start sorting them alphabetically.
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
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:
Select your data and go to data and then to Text to Columns:
Click on text to Columns and then select delimited and go to next:
Click on next then write value of the cell and click finish to sort by last name
Finish and see the project:
Sorting by Last Name Manually:
Select all data entered and make two columns by simply copy pasting
Open find and replace using Ctrl+H keys:
Write * along with a space in the first tab and leave second tab blank:
Click on replace all to delete first names
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.
- 1. Create unique name tags with a name tag template Word
- 2. How to Merge First and Last Name in Excel with Comma (Step by Step)
- 3. How to make First Name and Last Name in Excel Using Formula
- 4. How to Split First and Last Name in Excel
- 5. Name Box: How to add table name in Excel
- 6. Name Manager: A Quick Way to Name a Cell in Excel