Google Sheets Filter by Last Name: Sort and Search

Yes. In Google Sheets in a web browser, select the full table, choose Data > Create a filter, and use the filter icon in the Last name column to show the surnames you want. For the clearest results, keep last names in their own column; if names are stored together, you can still search the full-name column, but matching and sorting may be less precise.

Set up a filter on the last-name column

A standard filter is the quickest way to display only records for one or more surnames without removing anything from the sheet. It works best when the data is organized as a table: one row of column headings followed by one record per row. For example, a contact list might have First name, Last name, Email, and City headings.

Check that the header row is directly above the records and that there are no blank rows or columns splitting the table.
Select the table, including its headings. If your cursor is already inside a continuous data range, Sheets may recognize the range, but selecting it explicitly helps prevent unrelated cells from being included.
Choose Data > Create a filter. Filter icons appear in the header cells.
Click the filter icon in the Last name heading. Under Filter by values, clear the current selection, then select the surname or surnames to display. You can use the search box in that menu to find a value in a long list.
Click OK or apply the selection. Rows that do not match are hidden from view, while the matching records remain in their original rows.

To show the complete list again, open the same column’s filter menu and select all values, or remove the filter using Data > Remove filter. The filter changes what is displayed, not the underlying records: hidden rows are not deleted. This matters if you later copy, edit, or share the sheet, because a filtered view can make it easy to overlook records that are still present.

Choose values or filter by a condition

The filter menu provides two useful approaches. Filter by values is convenient when you want to pick specific surnames already present in the data. It lists distinct values found in the filtered range, so you can select one surname, several surnames, or search the list. This is a good option for a known family name such as Smith or a short list of names for a mailing.

Filter by condition is better when you need a rule rather than a manually selected list. In the Last name column’s filter menu, choose a text condition such as Text contains, Text is exactly, or Text starts with, then enter the text to match. For instance, “Text starts with” and the letter M can show surnames beginning with M. A contains condition can be useful for locating a fragment when you are unsure of the complete spelling.

Value selection and text conditions answer slightly different questions. A value selection targets entries that appear in the list, while a condition evaluates the cell contents against a rule. If you choose a condition, check the results carefully: a contains search for “son,” for example, can match more than one surname and may also match text you did not intend if the column includes notes or other values.

When names are in one full-name column

If a cell contains a full name, such as “Jordan Lee,” you can create a filter on that column and search for a surname with a text condition. “Text contains” followed by Lee can find that name even though the cell also contains a first name. This is a practical shortcut for a small list, but it is not always equivalent to filtering a dedicated last-name field.

A contains search may match a surname that appears as part of another word, and it can also match the same text in a first or middle name. Searching for “Ann,” for example, could find a record where Ann is the first name rather than the surname. A condition that requires the full-name cell to be exactly “Jordan Lee” avoids that ambiguity, but it will not find other people whose surname is Lee.

Splitting full names into separate columns can help, but separating at every space is not a reliable way to identify surnames. People may have middle names, compound surnames, prefixes, suffixes, or different naming conventions. Google Sheets provides Data > Split text to columns, but that tool divides text using a chosen separator; it does not determine which part of a name is legally or culturally the last name. Review the result and correct it before relying on the new column for important lists.

Use a formula to display matching rows

A formula is useful when you want a separate, automatically updating list of matching records instead of hiding nonmatching rows in the original table. In Google Sheets, the FILTER function returns rows that meet a condition. Keep the formula in an empty area or on another sheet, with enough blank space for the returned rows to expand.

Assume the data occupies A2:C6, with first names in column A, last names in column B, and departments in column C. The sample records are Jordan Lee, Morgan Smith, Riley Smith, Alex Chen, and Casey Patel. Enter this formula in an empty cell:

=IFNA(FILTER(A2:C6,B2:B6="Smith"),"No matches")

The result is two rows: Morgan Smith with Marketing, and Riley Smith with Finance. If there are no Smith records, the formula displays “No matches” rather than the no-match error returned by FILTER. The formula does not hide or change the source data, and the result updates when the referenced cells change. It also does not provide the same interactive dropdown as a standard filter. In Excel for Microsoft 365, FILTER and IFNA are available too, although menu names and the surrounding interface differ; the steps above are for Google Sheets.

Filtering is not the same as sorting

Filtering narrows which rows are visible; sorting rearranges the order of rows. If you want every contact visible but alphabetized by surname, sort the full table by the Last name column instead of filtering it. In Sheets, select the data range and use Data > Sort range, choosing the last-name column as the sort key. Include all the related columns so that each person’s email, city, and other details move with that person’s name.

Sorting only the surname cells while leaving the rest of the table in place can detach names from their records. Before sorting, confirm that the selected range includes every column belonging to the records and indicate whether the range has a header row if that option is presented. If you only want to find and temporarily isolate certain surnames, use a filter instead; if you want a lasting alphabetical arrangement, sort the complete records.

Use filter views for different working views

A standard filter is useful for a shared, immediate view of the data, but it may not suit a sheet where several people need different criteria. Google Sheets filter views let users save distinct filtering and sorting arrangements. In the browser, open Data > Filter views > Create new filter view, then set the Last name filter and give the view a recognizable name if you want to return to it later.

Filter views are especially helpful for a shared contact list: one person can view Smith records while another uses a different filter view, rather than repeatedly changing the same standard filter. They change how the sheet is viewed, not the underlying data. Availability and collaboration behavior can depend on the document and account permissions, so users still need access to the sheet and the relevant range. These steps describe the Google Sheets browser interface, not Excel’s filter-view features or menus.

Common filter problems and how to fix them

The filter icon is missing: Make sure a filter has been created with Data > Create a filter, and check that your selection includes the header row. If the range is not continuous, select the intended table again.
Some records disappear unexpectedly: Look for other active filters in the table. More than one column can have a filter condition, and the visible rows must satisfy the active criteria together. Clear conditions in other columns to check whether they are narrowing the results.
A surname is not listed: The cell may contain a trailing space, a different spelling, or a value outside the range covered by the filter. Check the source cell and confirm that the filter range includes the relevant row.
Duplicate-looking surnames behave differently: Entries can vary because of extra spaces, punctuation, capitalization, or different characters that look similar. Compare the actual cell contents and standardize the source data if appropriate.
The header is being treated as a record: Recreate or adjust the filter range so the real headings are included as headings and the data begins beneath them. Avoid putting a title row or blank row between the headings and the records.
Editing a visible result seems to affect a hidden row: Check the row number and the active filter before editing or pasting. A filter hides rows rather than moving them out of the sheet, so verify the target records before making bulk changes.

Keep surname data consistent

Filtering is only as dependable as the values in the column. A consistent Last name field should contain surnames rather than a mixture of full names, email addresses, and comments. Keep one person or record per row, use a clear heading, and avoid merged cells within the data range. These habits also make sorting, exporting, and later analysis less error-prone.

Do not assume that every person’s final word is a surname. Compound surnames, hyphenated names, prefixes, suffixes, and naming practices that do not follow a first-name/last-name pattern can make automatic splitting misleading. If the data will be used for communication or reporting, preserve the original name and use a reviewed, explicitly labeled surname field where that distinction is needed. A filter can select text; it cannot decide the correct interpretation of a person’s name.

Frequently Asked Questions

Can I filter Google Sheets by one last name?
Yes. Create a filter on the table, open the filter menu in the Last name column, and select that surname under Filter by values. You can also use a text condition when that better fits the data.

Can I select more than one last name?
Yes. In Filter by values, select each surname you want included. The visible rows will contain the selected values; clear the selections and choose another set to change the result.

Can I filter a full-name column by surname?
Yes. Use a text condition such as Text contains on the full-name column. It can match text elsewhere in the name too, so a dedicated and reviewed last-name column is more reliable for repeated or precise filtering.

Does filtering delete the other rows?
No. A filter hides rows that do not meet the criteria. Remove or clear the filter to see the full data again. Check which rows are visible before copying or editing records.

How do I put last names in alphabetical order?
Use sorting rather than filtering. Select the whole table and sort by the Last name column so each person’s other details stay in the same row. Filtering is for temporarily showing only chosen records.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.