In Excel, as you add more information to a worksheet, it becomes increasingly critical to organize it. Sorting your data can help you rapidly restructure a worksheet. In this tutorial, we’ll explain to you how to alphabetically sort by name in Excel in a row, column, or by the last name. To do this, we’ll use both the Excel built-in command and Excel formulas.
How to Sort by Name in Excel (3 Examples)
In the following sections, we’ll walk you through 3 alternative ways to sort by name in Excel. To begin, we’ll use the Sort & Filter group, a built-in Excel command from the Data tab, in both rows and columns. After that, we’ll do this task by applying the formulas.
1. Use Sort & Filter Group to Sort Columns by Name
We’ll start by showing you an example of how to use the Sort & Filter group to sort data. For example, suppose you have a list of people’s names from various cities around the United States. For instance, you wish to sort people’s names alphabetically. Follow the instructions below to sort columns by name.
1.1 Sort a Column by Name
- Firstly, select the first element in the column.
- From the Ribbon, choose the Data
- Then, click on the A → Z icon from the Sort & Filter
As a result, you will get the column with a sorted value as shown in the below image.
1.2 Sort Multiple Columns by Name
- Click on the Data tab from the Ribbon.
- From the Sort & Filter group, select the Sort.
- From the table, choose any of your preferred columns to sort by. We have chosen the City to sort by first.
- Therefore, you will get the sorted value according to the second column.
- To sort by another column later, click on the Add Level
- From the They by list, choose the column tagged as Names.
- Finally, press OK to see the results.
Read More: Excel Auto Sort when Data is Entered
- How to Undo Sort in Excel
- Auto Sort in Excel Without Macros
- How to Add Sort Button in Excel
- How to Sort by Last Name in Excel
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- Advanced Sorting in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
- How to Remove Sort in Excel
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
2. Use Sort & Filter Group to Sort Rows by Name
You may sort a row by names in addition to sorting by columns. For Example, we have reorganized the Names and City values in a row. Follow the instructions below to complete the sorting in a row.
- After opening the Sort option from the Sort & Filter group, click on the Options marked option.
- Select Sort left to right.
- Then, press OK.
- From the Sort by menu, choose Row 4.
- Press Enter to see the results.
- Press Enter to see the results.
Read More: Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
- How to Sort Columns in Excel Without Mixing Data
- How to Sort Dates in Excel by Month and Year
- How to Sort Birthdays in Excel by Month and Day
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Excel Shortcut to Sort Data
- How to Sort Numbers with Letter Suffix in Excel
3. Apply Formula to Sort by Name
In addition to the preceding ways, we’ll use formulae to sort data in a column, row, and by their last or first name. Follow the procedures outlined below to complete the task.
3.1 Apply Formula to Sort Each Row by Name
- In a cell, type the following formula for the first row in range C4:G4.
- The COUNTIF function will return by counting serially the match between C4:G4.
- The MATCH function provides the match between columns COUNTIF
- Finally, the INDEX function will give us the matched result serially.
- Importantly, to make it work as an array, press Ctrl + Shift + Enter
- Use the AutoFill tool to fill up the required cells.
- As a result, your rows will be sorted individually as shown in the below image.
3.2 Apply Formula to Sort Each Column by Name
Additionally, we have applied formulas for sorting in columns. Simple, follow the outlined steps below.
- In a different cell, type the following formula for the range B5:B9.
- To make it work as an array, press Ctrl + Shift + Enter
- Finally, apply AutoFill to sort the required cells.
- Therefore, data in columns will be sorted individually alphabetically by names.
3.3 Apply Formula to Sort by Last Name
In Excel, you may also sort data by first or last names. We’ll sort them by their last names in the following example. Follow the steps below to sort by last names.
- To remain the First Names from the Names, type the following formula.
- Here the SEARCH function finds the space in B5
- The LEFT function returns the remaining value after space found from the SEARCH.
- To keep the Last Name, type the following formula.
- To add the Last Name first and the First Name last, type the formula below.
- In a different cell, copy the cells and paste them as Value & Number Formatting.
- From the Data tab click on the icon below.
- Therefore, your data will be sorted by their last names. Now to combine them to give regular shape follow the steps.
- Type this formula to keep only the First Name.
- In cell C12, the RIGHT function provides the value from the B12 cell with the number of characters left from applying the SEARCH function after space.
- Type the following formula to keep the Last Name.
- Finally, just combine both cells with the following formula.
- Therefore, in the below image, you can see the sorted data by their last names.
Read More: How to Sort in Excel by Number of Characters
- How to Sort and Filter Data in Excel
- How to Sort Two Columns in Excel to Match
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- How to Sort by Date in Excel
- Excel Sort Dates in Chronological Order
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
- How to Sort Excel Sheet by Date
- How to Sort by Month in Excel
- Sort IP Address in Excel
- Random Sort in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
To conclude, I hope this tutorial has shown you how to sort by name in Excel using the Sort & Filter command and applying formulas. All of these strategies should be taught to your data and used to it. Look over the practice book and put what you’ve learned to use. We are willing to reimburse programs like this because of your support.
If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.
Your questions will be answered as soon as possible by the Exceldemy team.
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
- How to Perform Custom Sort in Excel