3 Ways To Sort By Surname In Excel

Sorting data by surname in Excel can be a valuable skill, especially when working with large datasets that include names. In this article, we will explore three ways to sort by surname in Excel, providing you with a comprehensive guide to tackle this task efficiently.

Sorting by surname is essential in various scenarios, such as organizing employee lists, customer databases, or student records. Excel offers multiple methods to achieve this, catering to different needs and preferences. Whether you are an Excel beginner or an experienced user, this article will walk you through the steps to sort by surname with ease.

Method 1: Using the Text to Columns Feature

The Text to Columns feature is an excellent way to separate names into different columns, allowing you to sort by surname efficiently. Here's how to do it:

1. Select the column containing the names.
2. Go to the Data tab in the ribbon and click on Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited Text and click Next.
4. Choose Space as the delimiter and click Next.
5. Click Finish to separate the names into two columns: First Name and Last Name.

Sorting the Data

Now that you have separated the names, you can sort the data by surname:

1. Select the column containing the surnames (Last Name).
2. Go to the Data tab and click on Sort & Filter.
3. Select Sort A to Z or Sort Z to A, depending on your preference.

By using the Text to Columns feature, you can easily sort your data by surname, making it a convenient method for managing names in Excel.

Method 2: Using the Formula Bar

Another way to sort by surname is by using the Formula Bar to extract the surname from a full name. Here's how to do it:

1. Create a new column next to the names column.
2. Enter the formula: =RIGHT(A2,FIND(" ",A2)-1) (assuming the names are in column A).
3. Press Enter to apply the formula.
4. Copy the formula down to the rest of the cells in the column.

Sorting the Data

Now that you have extracted the surnames, you can sort the data:

1. Select the column containing the surnames.
2. Go to the Data tab and click on Sort & Filter.
3. Select Sort A to Z or Sort Z to A, depending on your preference.

Using the Formula Bar to extract surnames is a flexible method that allows you to sort by surname without separating the names into different columns.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and transform data with ease. Here's how to use Power Query to sort by surname:

1. Select the column containing the names.
2. Go to the Data tab and click on From Table/Range.
3. In the Power Query Editor, click on Add Column.
4. Select Custom Column and enter the formula: =Text.AfterDelimiter([Name], " ") (assuming the names are in column Name).
5. Click OK to apply the formula.
6. Go back to the worksheet and click on Load & Close.

Sorting the Data

Now that you have extracted the surnames using Power Query, you can sort the data:

1. Select the column containing the surnames.
2. Go to the Data tab and click on Sort & Filter.
3. Select Sort A to Z or Sort Z to A, depending on your preference.

Using Power Query to sort by surname is a robust method that offers flexibility and customization options for managing names in Excel.

Excel Sorting Gallery

Sorting by surname in Excel is a valuable skill that can help you manage names efficiently. By using the Text to Columns feature, the Formula Bar, or Power Query, you can sort your data by surname with ease. Whether you prefer a simple or advanced approach, this article has provided you with a comprehensive guide to tackle this task.