Yes—but Excel’s regular table filter does not have a built-in “show only unique rows” switch. To keep one copy and delete repeats, select a cell in the table and choose Data > Remove Duplicates; to hide or list unique values without changing the source, use a helper column or the UNIQUE function.
What “filter out duplicates” can mean
The phrase can describe several different jobs, and choosing the right one matters because some methods change your source data while others only change what you see. Decide first whether you want to remove repeated records permanently, hide them temporarily, or create a separate list of distinct entries.
- Remove duplicates: delete repeated rows from the table and keep one occurrence.
- Filter duplicates out of view: leave the source intact but hide rows that meet a duplicate test.
- Make a unique list: return one instance of each value or row somewhere else in the workbook.
Also decide what counts as a duplicate. Two rows might be duplicates only when every selected field matches, or they might count as duplicates whenever one field—such as an email address or order number—matches. Excel can do either, but you must specify the relevant columns for the built-in removal command.
Remove duplicate rows from an Excel table
In Excel for Microsoft 365 on Windows, click a cell inside the table, then choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare. If every field must match for a row to count as a duplicate, leave all columns selected. If an email address alone defines a duplicate, select only the Email column. Click OK to remove the repeats.
- Make a backup or confirm that the workbook can be restored if the result is not what you intended.
- Click inside the table and open Data > Remove Duplicates.
- Review the column checkboxes. Clear any columns that should not be part of the duplicate test.
- Click OK, then read Excel’s message showing how many duplicate values were removed and how many unique values remain.
Excel keeps one occurrence of each matching record and deletes the others from the selected data range. It does not merely hide those rows. The table’s remaining rows stay in the table, and you can undo the operation immediately with Ctrl+Z on Windows. On Mac, use Command+Z. If the workbook is saved, closed, or otherwise changed after the operation, undo may no longer be a dependable recovery plan, so a backup is the safer choice.
The column selection is the key decision. Suppose a table contains Date, Customer, Email, and Amount. If only Email is selected, two rows with the same email count as duplicates even if their dates or amounts differ. Excel retains one row, so other details in the deleted row may be lost. If you want to remove only fully identical records, select all four columns.
Use a helper column to hide repeats, not delete them
If you need to retain every record but show only the first occurrence of each value, add a helper column to the table and filter it. For example, assume the values to check are in A2:A100, with the first data row in A2. In a helper column beside row 2, enter =COUNTIF($A$2:A2,A2)=1 and fill it down. The first occurrence returns TRUE; later occurrences return FALSE. Filter the helper column to TRUE to show the first instance of each value.
This method is order-dependent: it marks the first occurrence from the top of the current list. If you want a particular row to survive—such as the newest record—sort the table into the desired order before applying the test. The formula does not delete anything, and clearing the helper-column filter reveals all rows again. In a table, Excel may automatically fill a formula down the calculated column; check that it has been applied to every data row.
COUNTIF is not case-sensitive, so entries such as Alex@example.com and alex@example.com are treated as matching for this test. It also compares the values, not their formatting. If a column contains extra spaces, inconsistent punctuation, or different spellings, those differences can prevent records from matching. Clean or normalize the data first if those variations should count as the same value.
Return a separate unique list with UNIQUE
For a non-destructive result in Excel for Microsoft 365, use the dynamic-array function UNIQUE. Suppose a table named Table1 has a column named Email. In a blank cell outside the table, enter =UNIQUE(Table1[Email]). Excel returns one instance of each distinct email in a spilling list, leaving the original table unchanged. For example, if the column contains sam@example.com, lee@example.com, and sam@example.com, the result contains sam@example.com and lee@example.com.
UNIQUE can also return distinct complete rows. If Table1 contains the records you want to compare, enter =UNIQUE(Table1) in an empty area outside the table. Excel returns one copy of each distinct row across the table’s columns. This is different from removing duplicates based on just Email: two records with the same email but different dates or amounts remain separate because their full rows differ.
UNIQUE is available in Microsoft 365 and Excel 2021 or later. Its results spill into neighboring cells, so leave enough empty space below and, for full-row results, to the right. Dynamic-array formulas cannot spill from inside an Excel table; place the formula in a worksheet cell outside the table. If the spill area is blocked, Excel displays a #SPILL! error. The source table can still grow, and a structured reference such as Table1[Email] adjusts as table rows are added.
Use Advanced Filter for a one-time unique result
Advanced Filter is another built-in option when you want a static copy rather than a live formula result. On Windows, click in the data range and choose Data > Advanced. In the dialog, choose whether to filter the list in place or copy the results to another location, confirm the list range and any criteria range, and check Unique records only. For a separate result, specify a copy-to cell and make sure the destination area is clear.
This option is useful for an occasional extract or for workbooks where a dynamic-array function is unavailable. The copied output is a snapshot: it does not automatically update when the source changes. Filtering in place hides records rather than deleting them, but it is easy to confuse a filtered view with a shortened list, so inspect the row numbers or clear the filter to verify the underlying data is still present.
Choose the method that fits your goal
Use the tool that matches the outcome you need, not just the word “filter.” A permanent cleanup, a reversible view, and a reusable report are different tasks.
- Use Remove Duplicates when you have checked the comparison columns and truly want repeated rows removed from the data.
- Use a helper column when you want to filter the original table to one occurrence per key and retain the hidden rows.
- Use UNIQUE when you want a live, separate list of distinct values or complete records in a supported Excel version.
- Use Advanced Filter for a built-in, one-time unique extract or an in-place filtered view.
For recurring reports, UNIQUE is generally easier to refresh because it recalculates as the source changes. For a controlled cleanup before importing data into another system, Remove Duplicates may be more appropriate—but only after confirming what constitutes a duplicate and which copy should remain. If records with the same key contain conflicting information, do not remove rows until you have a rule for deciding which record is authoritative.
Common problems and checks before removing rows
A common surprise is selecting too few columns. If you select only Customer, Excel may remove a second transaction from the same customer even though the dates and amounts differ. Conversely, selecting every column can leave what looks like a duplicate in place when one row has an extra space, a different date value, or another small difference. Check the data and the selected comparison fields before trusting the result.
- Check for spaces and inconsistent entries. “Northwind” and “Northwind ” can look alike but contain different text. Clean the source if those should match.
- Check the table boundary. Make sure all intended records are inside the table or selected range; otherwise, rows outside it will not be included.
- Check which copy remains. Removal keeps one occurrence, but you should not treat that as a rule for choosing the most complete or most recent record. Sort or review the data first if the choice matters.
- Check filters and hidden rows. A filter can make it harder to see the full dataset. Clear filters or inspect the visible range before cleanup.
- Check the result message. Excel reports the number of duplicates removed. If that count seems unexpected, undo at once and review the column selection.
Duplicate detection is not the same as data validation. Two people may share a name, two orders may have the same amount, and one customer may legitimately appear in many transactions. A repeated value is only an error when it violates the rules of your data. Identify a reliable key—often an ID or a combination of fields—rather than deleting records merely because one visible value repeats.
Frequently Asked Questions
Can I filter an Excel table to show only unique values?
Yes, but the ordinary table filter menu does not provide a general unique-records toggle. Use a helper column to identify first occurrences and filter it, use Advanced Filter with Unique records only, or return a separate list with UNIQUE.
Does Remove Duplicates delete the whole row?
It removes duplicate records from the selected range, not just the repeated cell value. The columns you select determine whether Excel considers two rows duplicates. If only one column is selected, rows matching in that column can be removed even when their other fields differ.
How do I keep the latest duplicate record?
Sort the table so the record you want to retain appears first, then run Remove Duplicates using the key column or columns. Alternatively, use a helper formula that marks the first occurrence after sorting and filter to that result. Check the sort order carefully before deleting data.
Can UNIQUE remove duplicates from an Excel table?
It can return a separate list of distinct values or rows, but it does not remove records from the source table. Put the formula in a blank worksheet area outside the table because a dynamic array cannot spill inside the table.
Why does Excel still show rows that look like duplicates?
The rows may differ in a field you selected, or contain hidden differences such as extra spaces, distinct underlying values, or inconsistent formatting of stored data. Review the comparison columns and clean the source if those variations should be treated as equivalent.
Keep a recoverable source
For important data, preserve an untouched copy before cleaning, especially when duplicates may contain conflicting details. You can duplicate the worksheet, save a separate workbook copy, or use UNIQUE or Advanced Filter to create a separate result first. That gives you a chance to compare the proposed unique records with the original before committing to a destructive change.
Related
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
- Excel Shortcut to Select to the End of a Column Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter by Multiple Colors: What WorksIn Excel for Microsoft 365 on Windows, a table’s color filter can show rows for one selected fill…
- ABS Function in Excel: Examples and Practical UsesExcel’s ABS function returns the absolute value of a number: its distance from zero, without a negative sign.…
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH…
- Excel NPV With Different Rates: Formula, Steps, ExampleExcel’s NPV function accepts one discount rate, so it cannot directly calculate net present value when the rate…
- Sumifs with date range?Learn how SUMIFS handles dates, boundaries, months, extra criteria, and common errors, with clear Excel examples to help…
- How to Find a Percentage in Excel: Formulas and ExamplesTo find what percentage one value is of another, divide the part by the whole and format the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.