Sorting data by last names in Google Sheets is so easy. You can use the Filter tool and then the “Sort A to Z” option within it to sort the names in ascending order.
Things may get complicated when you have full names entered in a single column. In this case, you need to separate the first name from the last name in two different columns, then use the sorting feature and functions in Google Sheets.
Welcome to this blog!
We will discuss sorting by last name using different features and functions prebuilt into Google Sheets.
Here is the list of methods that can be used for sorting the data by last name. You can click on the links to jump to that particular section:
But before using any of these methods, we need to ensure that the last names are available in a separate column. If yes, then skip the following section.
How do you separate the last name from the full name?
If the full name is entered in a column, as shown below, you need to separate the last name from the first name to apply sorting.
Here are the steps to separate last names,
- Open the Google Sheets
- Select all the cells containing the full names
- Hover to the main menu and click on the “Data” tab
- Select the option “Split text to columns” from the dropdown
- Now you will see a dialog box as shown in the following image
- Click on the dropdown beside the separator and choose “space.”
It will instantly separate first and last names into two different columns.
Now, without further ado, let’s begin to learn how to sort by last names.
Method #1: Sort by last name in Google Sheets using Filters
It is the quickest method to sort by last names in Google Sheets.
- Open the Google Sheets
- Select any cell of the table or last name column
- Hover over the toolbar section sitting below the main menu and click on the Filters icon
- This will enable a filter option for each column of the table, as shown below
- Click on the small filter icon beside the Last Name column header to see various filtering and sorting options available in Google Sheets
- From the dropdown, select the first option named “Sort A to Z” to sort the data in ascending order (oldest to newest). Also, note the second option is “Sort Z to A,” which allows you to sort in descending order (newest to oldest)
Using filters is often suggested while sorting data by last names in Google Sheets.
In the case of tables, sorting should affect all of the columns. Filters are the best way in this regard.
Method #2: Sort by last name in Google Sheets using the SORT function
The SORT function allows you to create a dynamic table that automatically updates whenever you change something in the main table.
With other methods, adding new records to the main data table will mess up the sorting applied to it previously.
But with the SORT function, this is not the case. The newly created table is dynamic; every time you change something in the main table, it will be automatically updated.
The general syntax for the Sort function,
=SORT(range, sort_column, is_ascending)
Each argument needs to be replaced with proper information,
- “range” needs to be replaced with table range or entire column
- “sort_column” needs to be replaced with the column number
- “is_ascending,” here put “TRUE,” if you wish to sort the column in ascending order
Let’s use the SORT function and create a new dynamic table in the same spreadsheet.
- Open the Google Sheet
- Create a new table and add headers to the table
- Select the first cell right below the first header of the new table, as shown in the following image
- Type “=SORT”
- Select the first option from the popup or press the “Tab” on your keyboard
- If you have the table, you need to select the entire table or else go for the entire column containing the last names
- Now, you must provide the column number where you wish to apply the sorting. In the case of a single column, put the number as “2.”
- Type “TRUE,” as we are about to sort by last name in ascending order
- Next, you need to close the brackets by typing in “)”
- Press “Enter” on your keyboard
Using this method, you will preserve the primary dataset and use a new dynamic table for further analysis.
Q. What is the common mistake everybody makes while using the Sort function?
It is not keeping all the cells empty in the newly created table.
Ensure all cells where the new dynamic table will be created are empty.
In the above example, one of the cells contains “ab-.”
After using the Sort function it will display the error “Array result was not expanded because it would overwrite data in D9.”
Method #3: Sort by last name in Google Sheets using Sort Range
It is your final option to sort by last name in Google Sheets.
To use the Sort Range feature, you need to follow the steps below:
- Open the Google Sheet
- Select the entire table by pressing “CTRL + A” on your keyboard
- Go to the main menu and click on the “Data” tab
- Choose the second option, “Sort Range,” from the popup
- Click on the “Advanced range sorting option.”
- A new dialog box will appear. You need to tick the box before “Data has header row.”
- Next, you will see a dropdown beside “Sort by,” Click on that dropdown and choose “Last Name.”
- Select “A to Z” for sorting in ascending order and “Z to A” for sorting in descending order
- Click on the “Sort” button in the green
Make sure to select the entire table, as mentioned in the second step. Otherwise, the Sort Range feature will only apply sorting to the selected column and won’t affect any data from the remaining columns.
Moreover, you need to select the entire column while using the Sort Range feature for a single column. Or else the Sort Range feature will be grayed out, as shown below.
I hope this quick guide taught you all the sorting options available when it comes to sorting the data by last name.
All you need to ensure is that the first and last names are present in separate columns for the above methods to work.
In case you have any doubts, feel free to comment below.
Related Google Sheets Tutorials
- How to Sort Alphabetically in Google Sheets
- How to sort by Date in Google Sheets
- How to Custom Sort in Google Sheets (With Examples)
- How to Sort Rows in Google Sheets (With Examples)
- How to Sort By Number in Google Sheets (Sort by Value)
This page may contain a few affiliate links, which means if you buy something through them, we may get a commission (without any extra cost to you).