How to Sort by Last Name in Excel

Sorting is a fundamental function in Microsoft Excel. However, sorting by last name can be a bit tricky, especially when dealing with a long list of names. Fortunately, Excel offers several methods to sort by last name, making the process more convenient. In this article, we’ll explore some of the most straightforward ways to sort by last name in Excel, whether you’re using the desktop application or the online version.

Method 1: Separate the First and Last Names

The first step in sorting a list of names by the last name is separating the first and last names. This is a necessary step, as Excel cannot determine the last name without separating the first and last names into distinct columns. Here’s how to do it:

1. Select the entire column that contains the list of names.
2. Click the Data tab in the Excel ribbon.
3. Click the Text to Columns button.
4. A Text to Columns Wizard will open. Choose Delimited and click Next.
5. Select the delimiter that separates the first and last names (usually a space) and click Next.
6. In the next window, you can choose the format of each column. As our goal is to extract the last names, select the second column and choose the Text format.
7. Click Finish and watch as Excel separates the first and last names into distinct columns.

Method 2: Use the LEFT and RIGHT Functions

If you don’t want to use the Text to Columns feature, you can use the LEFT and RIGHT functions to extract the first name and last name, respectively. Follow these instructions:

1. Insert two new columns next to the column containing the full names.
2. In the first new column, enter the formula =LEFT([Cell containing the full name],FIND(” “,[Cell containing the full name])-1), where “Cell containing the full name” is the cell containing the full name.
3. Press Enter, and the formula will extract the first name.
4. In the second new column, enter the formula =RIGHT([cell containing the full name],LEN([cell containing the full name])-FIND(” “,[cell containing the full name])).
5. Press Enter, and Excel will extract the last name from the full name.

Method 3: Use the Sort Feature

The easiest method to sort names by last name in Excel is to use the Sort feature, which you can access from the Data tab in the Excel ribbon. Here’s how to do it:

1. Select the entire list of names.
2. Click the Sort button in the Excel ribbon.
3. A Sort dialog box will appear. Choose the column containing the last names and set the sorting order to A to Z (Ascending).
4. Click OK, and Excel will sort the list by last name.

Additional Tips

Here are some useful tips to keep in mind when sorting a list of names by last name:

• If there are middle names or initials in the list, make sure to include them while separating the names, or use the MID function to extract them.
• Always make a backup of the original list before sorting. This way, if something goes wrong, you can always go back to the original list and start over.
• Remember to adjust the formulas or text to columns feature if your data already contains separated columns.
• If you are creating a database and entering names, it is better practice to separate the first and last names first, and then enter them in separate cells. That way, you won’t have to go back and separate them later.

Sorting a list of names by the last name is a useful skill that will save you time and effort. Excel provides several ways to sort by last name, so choose the method that works best for you and your list. Remember to make a backup of the original list, and keep in mind the additional tips we’ve provided here. With these tips and methods, you’ll be able to sort by last name like an Excel pro.

FAQ

Here are some common questions related to sorting by last name in Excel:

Can I sort a list of names that includes titles like Mr. or Mrs.?

Yes, you can. To do so, you need to separate the titles from the first names using either the Text to Columns feature or formulas. Once you’ve extracted the last names, you can sort the list by last name as usual.

What do I do if there are repeated last names in my list?

If your list contains people with the same last name, you can sort the list further by first name. To do so, select the entire list and access the Sort feature as usual. In the Sort dialog box, choose the column containing the first names and set the sorting order to A to Z (Ascending).

What if I have a list of names in reverse order, with last name first?

Excel can also sort a list of names that has the last name first. To do so, separate the last name from the first name using the Text to Columns feature or formulas. However, this time, choose the first column as the column containing the last names and then use the Sort feature to sort by last name.

Can I sort by last name in Excel Online?

Yes, you can sort by last name in Excel Online. The process is similar to sorting in the desktop application. Select the list of names and then access the Sort feature from the Home tab in the Excel Online ribbon. From there, choose the column containing the last names and set the sorting order to A to Z (Ascending).

What if my list contains non-English names or unusual characters?

Excel can sort lists containing non-English names or unusual characters, but you need to ensure that the sorting is set to the correct language format. In the Sort dialog box, make sure that the sorting language is set to the desired language, such as Spanish or French. If the language isn’t listed, select the Unicode option to sort based on the Unicode values.