Buzz
When creating a table in Excel, the content you input often needs to be sorted alphabetically, especially for tables with columns like First Name and Last Name. Sorting names alphabetically in Excel is essential and recommended.
Sort names in Excel alphabetically from A to Z
When it comes to the Name column, users can either split it into separate First Name and Last Name columns or keep it combined in one column. Typically, when First Name and Last Name are split, sorting becomes simpler and quicker. However, even if you choose to keep them in one column, you can still sort them in the normal order and remain compliant.
How to Sort Names Alphabetically in Excel
1. Sorting when splitting First Name and Last Name into separate columns
Suppose you need to sort names from a dataset that has been split into First Name and Last Name columns as described in the image below:
Step 1: In the Student Name column -> select -> then click on the Home tab -> choose Sort & Filter -> select Custom sort.
Step 2: The Sort interface appears -> here, you can adjust the order according to the alphabet.
- In the Column section, choose Student Name.
- In the Order section, select A to Z -> then click OK to proceed.
Step 3: The result is that the Last Name and First Name columns will be sorted alphabetically by Excel.
However, the ID column will be disrupted due to sorting the data by Name. To fill in the ID again, simply enter 1 in the first cell -> then drag down to the subsequent cells -> and all ID cells below will be changed to 1.
Step 4: To correctly rearrange the order of numbers, click on the plus icon below -> choose Fill Series to number the column sequence in Excel.
The result is that the ID will be automatically renumbered correctly according to the regulations as shown below:
2. Sorting when the Name column is one column
Sorting names alphabetically with the combined First and Last Name column will be more challenging than splitting this column into two: First Name and Last Name.
Step 1: First, you need to add another Name column -> click on the adjacent column as described in the image below -> then right-click and select Insert.
Step 2: The newly added column appears. Rename this column to New Full Name -> then copy the entire Name into this new column.
Step 3: Select (highlight) the New Full Name column -> then press the shortcut Ctrl + H to open the Find and Replace interface.
In Find what, enter * space -> select Replace All -> press Close to close the interface.
An Excel notification will appear -> press OK to agree.
The result is that you will have the New Full Name column as shown below:
Step 4: Select the New Full Name column -> click on the Data tab -> choose Sort A to Z as described in the image.
A Sort Warning notification appears -> choose as described in the image below -> then select Sort.
In the Sort by section, choose New Full Name -> then also sort from A to Z. Press OK to sort.
The result is that the content in the Name column will be automatically sorted alphabetically in Excel. You just need to delete the New Full Name column, and for the disrupted ID, follow the same process as instructed in section 1.
3. How to create filters in Excel for quick sorting
This method will help users quickly filter and sort data more efficiently. To do this, simply select the column to be sorted -> then choose the arrow icon to bring up the sorting table as shown in the image below. You can sort from A to Z or from Z to A.
So now you know how to sort names alphabetically in Excel. To sort in ABC order in Excel, you can apply one of the two methods just introduced by Mytour above. You can separate First and Last names into 2 separate columns for sorting, or if left intact, you need to create an additional column and perform a few small tricks to sort according to your preferences.
Similarly, if you want to sort data on a spreadsheet for data other than text, such as numbers for example. Refer to Mytour for more insight into the process!
