How to Sort in Excel by Name (3+ Methods)

Data sorting in Excel is an essential part of data analysis. We need to use the sorting technique every now and then in our workplace. In this article, I am going to show you 3 simple and quick ways that will help you to sort in Excel by name alphabetically. Besides, you will learn some additional methods relating to sorting in Excel. So, let’s get started.

Sort in Excel by Name through the Data Tab

To sort in Excel by name through the Data tab, follow these steps below:

  1. Select your data first.
  2. Go to the Data tab.
  3. Click on the A to Z or Z to A icon to sort data by name alphabetically.

Click on the A to Z or Z to A icon to sort data by name alphabetically in Excel

The result is in the following image:

Showing result after sorting by name in Excel

Note: If you are looking for keyboard shortcut keys to sort in Excel by name,

  • Press ALT > A > S > A sequentially to sort in A to Z order (ascending).
  • Press ALT > A > S > D sequentially to sort in Z to A order (descending).

Sort in Excel by Name in Excel Using Sort & Filter Command

You can also sort data in Excel from the Home tab. Check out the following usage guide to learn in detail:

  1. Go to the Home tab.
  2. Then hit Sort & Filter drop-down from the Editing group.
  3. Now select Sort A to Z or Sort Z to A as per your preference.

Use the Sort & Filter to Sort in Excel by Name Alphabetically



Sort in Excel by Name Alphabetically Use the Filter Command

Applying a Filter will also assist in arranging a column with names in Excel. To do that, go through these steps below:

  1. Select any column header by clicking any one of them.
  2. Then click on the Home tab.
  3. Now select the Sort & Filter drop-down.
  4. Finally, click on the Filter command.
    Use the Filter Command to Sort in Excel by Name Alphabetically

    Note: You can also find the Filter command from the Data tab > Sort & Filter group > Filter.
    Choosing Filter command from Data tab You will see drop-down arrows that will show up beside your column header(s).

  5. Click on the drop-down arrow of your selected column.
  6. Now choose either Sort A to Z or Sort Z to A.

Dropdown list with the Filter icon and choosing Ascending or Descending

How to Sort Alphabetically in Excel and Keep Rows Together

If you want to rearrange the rows alphabetically in your data table, here is how to do that: 

  1. Select the data table and go to the Data tab.
  2. Then under the Sort & Filter group, click the A-Z or Z-A command.

This will sort your data alphabetically in Excel and keep all the rows together.

Sort Alphabetically in Excel and Keep Rows Together



How to Sort in Excel by Last Name with RIGHT, LEN & FIND Functions

Sorting text data by their last words can be done very easily using a formula. I will use the RIGHT function, LEN function, and FIND function to create the formula.

Formula

=RIGHT(C2,LEN(C2)-FIND(" ",C2))

Formula Explanation

  • C2 is the cell address where you’ve stored the full name.
  • LEN(C2) calculates the total number of characters in cell C2.
  • FIND(” “,C2) finds out the position of the first space in cell C2.
  • RIGHT(C2,LEN(C2)-FIND(” “,C2)) returns only the right part of the whole text in cell C2 after the first space is encountered.

Here are the steps below:

  1. Select a cell to insert the sorting formula.
    In this case, I choose cell F2 for that.
  2. Now write this formula in that empty cell: =RIGHT(C2,LEN(C2)-FIND(” “,C2))
    Sort in Excel by Last Name
  3. Now, press the ENTER key and drag the Fill Handle icon from cell F2 to F10 to apply the above formula all around the cell range.

Using Fill Handle to drag down the formula with RIGHT, LEN & FIND functions

After following the above steps the resulting table will look like the image below:

Storing output sorted by last name in Excel

Conclusion

In this article, I tried to assist you in sorting in Excel by names. If you find any issue regarding sorting in Excel, feel to let me know in the comment section. I will try to come up with solutions. Thanks.

Frequently Asked Questions

How do I sort by name and keep rows together in Excel?

To sort by name and keep rows together in Excel:

  1. Highlight the columns containing the names you want to sort, including any related data in other columns that you want to keep together.
  2. Press ALT + D + S sequentially to open the Sort dialog box.
  3. In the Sort dialog box, select the column by which you want to sort under Sort by. Choose Values from the drop-down menu to indicate that you are sorting by names.
  4. Choose the desired sorting order, either A to Z for ascending order or Z to A for descending order.
  5. If you have additional columns you want to sort by, click on Add Level in the dialog box and repeat steps 3-4 for each level.
  6. Check the Preserve cell formatting box if you want to maintain the formatting of the cells while sorting.
  7. Once you’ve configured your sorting options, click OK to apply the sort.

This will arrange the rows alphabetically based on the names in the specified column while keeping related data together. By following these steps, you can sort your data in Excel by name and ensure that the rows stay together, maintaining the relationships between different sets of data.

How do I sort alphabetically in Excel formulas?

In Excel formulas, you can use the SORT function to sort data alphabetically. Here’s a simplified example: =SORT(range)
range: The range of cells containing the data you want to sort. For example, =SORT(A2:A10)
This formula sorts the values in cells A2 to A10 alphabetically in ascending order. Adjust the range according to your data.

Remember to enter the formula in a cell outside the specified range to avoid overwriting your original data. Additionally, ensure your Excel version supports the SORT function, as it was introduced in later versions like Excel 365 and Excel 2019.

Note: This formula might not work in older Excel versions that do not support the SORT function.

How do I group Data by name in Excel?

To group data by name in Excel, follow these steps:

  1. Highlight the columns containing the data you want to group, including the column with names.
  2. Click on the Data tab in the Excel ribbon.
  3. Under the Data Tools group, find and click on the Group or Subtotal button.
  4. A dialog box will appear. Select the column by which you want to group, typically the one with names.
  5. After selecting the grouping options, click OK.

This will create groups based on the names in the selected column, collapsing the rows and allowing you to easily expand or collapse groups to view or hide the associated data.

Remember, this process works well when your data is already sorted by the column you want to group. If not, consider sorting the data before applying the grouping for better organization.

Rate this post

Leave a Reply

Your email address will not be published. Required fields are marked *