ExcelInsider Team
While working with name-based Excel datasheets, sorting the data depending on the names is one of the most common processes. But usually, this sorting is done based on the first name basis. However, it is not possible to alphabetize the datasets using the last names in the same way. We will need to extract the last name from the full name list first and insert it in a separate column. Once that is done, we can easily sort by the last name in Excel in just a few steps.
➤ Extract the last name into a new column from the full name by using a specific method. For instance, you can use Flashfill to do this.
➤ Name the new column as “Last Name”.
➤ Go to the Data tab from the ribbons and select Sort.
➤ Choose Sort By > Last Name and press OK.
This article covers four different methods of sorting the dataset in alphabetical order using the last names. Utilizing Find and Replace, Text to Columns, Formulas and Flashfill are the ways discussed in this article along with proper directions.
Sort by Last Name using Find and Replace
The first and foremost step will be to create a separate column containing only the last names of the existing dataset of full names. We are going to use the Find and Replace method in order to complete this action. Suppose you have the following dataset that you want to sort by last name:
Steps:
➤ Select the dataset column including the header. In this sheet, it will be from A1 to A13.
➤ Copy the names.
➤ Create a new column just beside column A by clicking on Column B header and pressing on Insert.
➤ A new column will be created. Right click on column B now and click on paste. The full name list from column A will now be copied to column B.
➤ Rename the copied column as “Last Name”.
➤ Now, select all the copied names from column B without the header.
➤ Press and hold the Ctrl + H key at the same time to open a Find and Replace dialogue box.
➤ In the Find box, put an asterisk (*) sign and then press on the space bar. Keep the Replace box completely empty. This is to remove everything before the last name, including the first names and the spaces after the first name in column B.
➤ Now, click on Replace All to automatically replace all the names in column B with only last names.
➤ Select all the columns in the sheet.
➤ On the tabs, go to Data and select Sort. A sorting box will now pop up.
➤ In this box, go to the drop down button beside Sort by and choose Last Name.
➤ Beside the order drop down box, select A to Z or Z to A according to your preference of sorting by ascending or descending order respectively.
➤ Click on OK. Now the entire dataset will be sorted by the last names of the list of column A. You can now Delete column B if you don’t want only the last names to be showing in the sheet.
This method is the best option to sort by last name as it will work even if there are middle names in the full names. As long as the last name is the last word in each cell, this method will be the perfect and simplest to complete the action.
Sort by Last Name Using Text to Columns
This method will only work when all the cells either have first and last names or first, middle and last name. If some cells include first, middle and last names and the other cells don’t, then this method will not work correctly. In order to use this method in case of our sample datasheet, follow the steps below.
Steps:
➤ First, create a new empty column just beside column A by clicking on the Column B header and pressing on Insert.
➤ Select the names in column A.
➤ Go to the Data tab in the ribbons and click on Text to Columns option. This will open the Text to Column wizard box which has 3 steps.
➤ Choose the File Type as Delimited and press Next.
➤ In the next step, you will need to select the Delimiter where you will tick on the Space box. Then go to the Next step.
➤ In Step 3, select the Do not Import Column option under the Column Data Format section. You can see in the Data preview section the selected first names that we will remove.
➤ Now, in the same step, in the Destination box currently it will show $A$2, but take the cursor to the B2 cell and click on it. The destination box will change to $B$2 now, which is our targeted column B.
➤ Click Finish and column B will now show all the last names of the column A full names.
➤ Select all the columns in the sheet.
➤ On the tabs, go to Data and select Sort. A sorting box will now pop up.
➤ In this box, go to the drop-down button beside Sort by and choose Last Name.
➤ Beside the order drop down box, select A to Z or Z to A according to your preference of sorting by ascending or descending order respectively.
➤ Click on OK. Now the entire dataset will be sorted by the last names of the list of column A. You can now Delete column B if you don’t want only the last names to be showing in the sheet.
Sort by Last Name using Formula
This method is the most dynamic one as after establishing the formula, we could insert more names in column A, and the formula will automatically extract their last names into a separate column. We don’t have this privilege with the first two methods as the names need to be pre-existing in those cases.
Type 1: Data With No Middle Names
In this type of data, only first and last names are present in all cells. No middle name is in the dataset so we will be using the previous data and follow the steps below:
Steps:
➤ First, create a new empty column just beside column A by clicking on Column B header and pressing on Insert.
➤ Type in cell B2 the necessary formula
➤ Now select the B2 cell and pull it down all the way to the B13 cell to apply the formula to the whole column.
➤ All the cells of column B will now have only the last names.
➤ Select all the columns in the sheet.
➤ On the tabs, go to Data and select Sort. A sorting box will now pop up.
➤ In this box, go to the drop down button beside Sort by and choose Last Name.
➤ Beside the order drop down box, select A to Z or Z to A according to your preference of sorting by ascending or descending order respectively.
➤ Click on OK. Now the entire dataset will be sorted by the last names of the list of column A. You can now Delete column B if you don’t want only the last names to be showing in the sheet.
Type 2: Data with Middle Names
When the names include their middle names, we will need to use a different formula. We have taken some data this time which have middle names in their full names.
Steps:
➤ First, create a new empty column just beside column A by clicking on Column B header and pressing on Insert.
➤ Type in cell B2 the necessary formula
➤ And press Enter.
➤ Now select the B2 cell and pull it down all the way to the B13 cell to apply the formula to the whole column. The last names will be extracted in column B.
➤ Select all the columns in the sheet.
➤ On the tabs, go to Data and select Sort. A sorting box will now pop up.
➤ In this box, go to the drop-down button beside Sort by and choose Last Name.
➤ Beside the order drop down box, select A to Z or Z to A according to your preference of sorting by ascending or descending order respectively.
➤ Click on OK. Now the entire dataset will be sorted by the last names of the list of column A. You can now delete column B if you don’t want only the last names to be showing in the sheet.
Sort by Last Names Using Flash Fill
This method, by far, is the simplest method to sort last names on excel. However, older versions of Excel 2010 and earlier do not have the Flash fill feature in it. So, this is not applicable to all Excel users. If you are using an Excel version older than 2010, you can easily sort the last names using the following steps.
Steps:
➤ Create a new empty column just beside column A by clicking on Column B header and pressing on Insert.
➤ For the first name in A2, go to the adjacent cell B2 and type the last name. For instance, in this sheet, type Davis in the B2 cell.
➤ Similarly, for cell A3, type in Thompson in the B2 cell.
➤ Now, select both of these cells.
➤ Hover the cursor at the corner of the cell and drag and select all the cells in column B.
➤ There will be an Autofill box at the corner of the last cell. Click on it and select the Flash fill option.
➤ Now, all the adjacent cells will be filled up with the respective last names.
➤ Select all the columns in the sheet.
➤ On the tabs, go to Data and select Sort. A sorting box will now pop up.
➤ In this box, go to the drop-down button beside Sort by and choose Last Name.
➤ Beside the order drop down box, select A to Z or Z to A according to your preference of sorting by ascending or descending order respectively.
➤ Click on OK. Now the entire dataset will be sorted by the last names of the list of column A.
FAQ
Can I Sort by the Last Names Without Splitting the Columns?
No, it is not possible in Excel to sort your full names and data without splitting the last names into different columns. You will need to choose any method to extract the last name temporarily and sort according to it. Afterwards, you can delete the column containing last names if you don’t want it there anymore. It won’t change the sorting.
How can I Sort the List by Last Name and Then First Name?
You need to split the full names into two different columns and name them “Last Name” and “First Name” accordingly. After that, select the whole dataset, go to Data>Sort. From the Add Level option, add two Sort Levels here and choose Sort By>Last Name at the first level, then choose Sort by>First Name at the second level.
How To Sort by Last Name When the Name Includes a Middle Name?
In case of full names containing a middle name as well, the best way to sort by last name is to use a specific formula. This formula only targets and extracts the very last word from the cell which will be the last name. Use the formula:
=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,” “,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))
Here, A2 cell needs to hold the first name of the list.
Will Excel Automatically Update the Sorting by Last Name if I Enter a New Full Name?
No, if you have used Flash fill, Text to Columns or Find and Replace method to sort by the last name, Excel won’t update the sorting of the new entries. You will need to redo the sorting method once again for the sorting condition to apply to the new data. However, if you have used a formula for sorting by last name, it will automatically update the sorting by last name within the selected cells the formula is applied to.
Wrapping Up
In this article, we have covered four different methods of how to sort by last names in Excel. In all cases, the last names need to be in a separate column in order to sort them alphabetically. For this, we have explored the Find and Replace, Text to Column, Flash fill options as well as using specific formulas for the action.
Hope this article was helpful for you to sort your data by the last names. Feel free to leave a comment and suggestion.
