To filter a list so repeated full rows are hidden, select the data, go to Data > Sort & Filter > Advanced, choose Filter the list, in-place, and check Unique records only. Excel keeps the first instance of each unique record visible and hides exact duplicate rows; it does not delete them. If you mean repeated entries in one column while keeping their full rows, use the helper-column method below instead.
What You’ll Need
- A worksheet in Excel for Microsoft 365 on Windows, with the list you want to filter.
- A single header row, such as Name, Order ID, or Date, and preferably no blank rows or columns inside the data.
- A copy of the workbook if the list is important. Filtering is reversible, but keeping a backup is sensible before changing how data is displayed.
You do not need to remove duplicates or convert the range to a table first. The key decision is what counts as a duplicate: an identical whole row, or a repeated value in a particular column. Advanced Filter compares complete records in the selected range. The helper-column method marks first occurrences based on one chosen column.
Step-by-Step
- Check the shape of your list. Put one field name in each header cell, with records directly below. For example, a list with Name, Department, and Email should have those three headers in row 1 and its records in rows 2 onward. Remove any completely blank rows or columns within the list, because Excel may treat them as boundaries and detect only part of the data.
- Decide what Excel should treat as a duplicate. If two rows should be considered duplicates only when every selected field matches, use Advanced Filter on the full table. If you want one row per customer name, email address, or other key while preserving the rest of each row, use the helper-column method in steps 8–10. These approaches produce different results when two records share one value but differ in another.
- Select the complete range for exact duplicate rows. Click a cell inside the list and check that Excel has detected the full block, including every field and the header row. You can also select the full range yourself, for example A1:C100. Do not select only the Name column if you want Excel to compare complete records.
- Open Advanced Filter. On the Windows ribbon, choose Data > Sort & Filter > Advanced. In the Advanced Filter dialog, the List range should show your data range. Correct it if Excel selected too few or too many columns or rows. Include the header row in the range.
- Filter the list in place. Select Filter the list, in-place. This keeps the data in its current location and hides records Excel identifies as duplicates. Choose Copy to another location instead if you want a separate unique-results list while leaving the original display untouched. For that option, set Copy to to an empty cell or area with enough room for the output.
- Turn on the unique-record setting. Check Unique records only, then select OK. Excel leaves one visible instance of each unique record in the selected list and hides exact repeats. For example, if two rows have the same Name, Department, and Email, one remains visible. If their Email values differ, both rows remain because the complete records are not identical.
- Review the visible results. Look through several records, especially ones you expected to be duplicates. The rows are hidden, not erased, so their row numbers may skip. If you chose Copy to another location, check the output range and its headers. Advanced Filter is a snapshot operation: if you later add or edit source data, run it again to refresh the results.
- For duplicates in one column, add a helper field. Suppose names are in A2:A6, with the header Name in A1. Put Keep first in B1. In B2, enter
=COUNTIF($A$2:A2,A2)=1and fill the formula down beside every name. The formula returns TRUE for the first occurrence of a name and FALSE for later occurrences. In the example Ana, Bo, Ana, Cy, Bo, the results are TRUE, TRUE, FALSE, TRUE, FALSE. - Filter the helper field to TRUE. Select a cell in the list and choose Data > Filter. Use the dropdown in the Keep first column, clear Select All, select TRUE, and choose OK. Excel now displays the first row for each name and hides later rows with the same name. Because this method keeps full rows, it is useful when a repeated key appears beside different dates, amounts, or other details.
- Remove the filter when you need the full list again. For the helper-column filter, open its dropdown and choose Clear Filter From, or use Data > Sort & Filter > Clear to clear worksheet filters. For an Advanced Filter applied in place, use Data > Sort & Filter > Clear. The hidden rows reappear; the underlying records have not been deleted.
Excel for Mac has an Advanced Filter feature as well, though its ribbon layout can vary by release; look on the Data tab for Advanced. Excel for the web may not offer the same Advanced Filter dialog. If you cannot find it, the helper-column technique works wherever the worksheet supports COUNTIF and ordinary filters.
Common Mistakes
- Selecting just one field when you mean whole-row duplicates. Advanced Filter compares the fields in its List range. If you select only one column, distinct records that share that value may be treated alike for the filtered list. Select all columns that define a complete record.
- Expecting duplicates to be deleted. Filter the list, in-place hides rows; it does not remove data. Use the separate Copy to another location option for a unique-results copy. If you deliberately use Remove Duplicates, remember it deletes records from the selected range, so make a copy first.
- Confusing a repeated key with an identical record. Two sales rows for the same customer may be different records if their dates or order numbers differ. Advanced Filter on the whole table will retain both. To keep only the first row per customer, use the helper column and filter TRUE.
- Leaving gaps in the data. A blank row can cause automatic range detection to stop early, while a blank header can make the list harder to identify. Keep one continuous block with a distinct header for every column.
- Assuming text differences are always obvious. Extra spaces, inconsistent spelling, or values stored as different types can make entries appear alike to a person but not behave as expected. Check and standardize the source values before filtering.
Tips
- Make a copy of the original list or save a new workbook version before experimenting. This is especially useful if you are comparing several definitions of duplicate.
- Use a descriptive helper-column header such as Keep first, and retain it while reviewing the filtered data. You can delete the helper column later if you no longer need the flag.
- When working with a large list, convert it to a table with Insert > Table. Tables make headers and records easier to identify and typically extend when you add rows, although you should still confirm the range in the Advanced Filter dialog.
- Advanced Filter compares the selected records as a whole; it is not a way to choose which of several different rows sharing a key is the best one. If you need the latest date or highest amount per key, first define how to choose the preferred record, then sort or use a suitable formula or query.
- If you only want a separate distinct list and have Microsoft 365 or Excel 2021, the UNIQUE function can return distinct values in another area. It is a formula-based result, not an in-place filter, and it does not hide source rows.
- Filtering is a display change. If you share the workbook, tell recipients that some rows are hidden, or provide a copied results range when you want the visible list to stand alone.
Troubleshooting
Advanced Filter is hiding too many or too few rows: Reopen Data > Sort & Filter > Advanced and inspect List range. Make sure it includes the header and all fields needed to define a unique record. If you are deduplicating by one field but selected the entire table, switch to the helper-column method; otherwise, rows that differ elsewhere will correctly remain visible.
The expected rows did not change: Confirm that Unique records only is checked and that Filter the list, in-place is selected. If you used Copy to another location, look at the destination rather than expecting the source list to change. Also check whether a previous filter is still active and clear it before applying the new one.
The helper formula shows unexpected TRUE or FALSE values: Verify that the formula starts in the first data row and that the first reference stays fixed at $A$2 while the final reference advances as you fill down. The example assumes the values are in column A beginning in row 2. Change the column letter and starting row to match your worksheet. COUNTIF is not case-sensitive, so names differing only by capitalization count as the same. Leading or trailing spaces can make otherwise similar text behave as separate entries.
Blank cells are appearing as a retained value: A blank can be treated as an item by a first-occurrence formula. If blank keys should not appear, filter the key column to remove blanks as well as filtering the helper field, or clean the source data first. Be cautious with formulas that return an empty string: a cell that looks blank may still contain a formula.
The filter controls are missing or the range is incomplete: Click inside the data and check that the worksheet is not protected in a way that prevents filtering. Remove blank rows or columns inside the list, then select the complete range manually and try again. In Excel for the web, use a helper column and Data > Filter if the Advanced Filter command is unavailable in your interface.
Frequently Asked Questions
Does filtering duplicates delete any data?
No. Advanced Filter in place and ordinary column filters hide rows from view. Clear the filter to show them again. Copy to another location creates a separate result list and leaves the source data in place.
How do I hide duplicate values but keep the first row?
Add the COUNTIF helper formula beside the data, fill it down, and filter that helper column to TRUE. This keeps the first occurrence according to the current row order. Sort the list first if a particular row should be kept first.
Why does Advanced Filter keep two rows with the same name?
When the List range includes several columns, Excel treats rows as duplicates only if the selected record fields match. If the names match but another field differs, both records remain. Use a helper column based on the name if that is the key you want to filter by.
Can I filter duplicates in just one column without hiding other columns?
Use the helper-column method beside the full table, then filter its TRUE/FALSE results. Filtering the helper field hides or shows entire worksheet rows, so the related columns stay aligned with each record.
Can I do this in Excel for the web or on a Mac?
Excel for Mac includes Advanced Filter, but the command location can vary slightly by version. If Advanced Filter is not available in Excel for the web, use the helper-column formula with Data > Filter. Microsoft 365 and Excel 2021 also support UNIQUE for returning a separate distinct list.
Related
- Duplicate a Sheet in Excel: Keyboard Shortcut and Steps
- How to Double Freeze Panes in Excel: Rows and Columns
- How to Drag Serial Numbers in Excel: Fill a Series
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- AVERAGEIF Excel Range: Syntax, Examples, and Common FixesIn Excel, the range in AVERAGEIF is the group of cells Excel checks against your condition; the optional…
- Divide a Cell by a Number in Excel: Formula GuideTo divide a cell by a number in Excel, enter a formula such as =A2/5 in an empty…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- Excel Conditional Formatting: Create a Formula RuleTo create a formula-based conditional formatting rule in Excel for Microsoft 365 on Windows, select the cells to…
- Excel ISBLANK Formula Examples and Common MistakesExcel’s ISBLANK formula checks whether a cell is truly empty: enter =ISBLANK(A2) to get TRUE if A2 has…
- Excel XLOOKUP With Wildcards: Examples and TipsTo find a value when you know only part of the text, use XLOOKUP with wildcard match mode…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.