To remove repeated entries from a list in Excel, select the list, choose Data > Remove Duplicates, confirm whether the first row is a header, and select the column or columns that define a duplicate. Excel keeps the first matching record and removes the other matching rows. If you need to clear only duplicate cells while keeping every other value on the same row, use the helper-column method below instead.
That distinction matters: Excel’s built-in command removes duplicate rows from the selected range, not isolated cells from a table. Make a copy of the workbook first, especially if the data is important or linked to other records. The steps below are for Excel for Microsoft 365 on Windows; Mac and web notes are included where they may differ.
What You’ll Need
- An Excel workbook with the data you want to clean, preferably with a clear header row.
- A backup copy or an available Undo option. Removing duplicates changes the selected data, and later edits can make undoing difficult.
- A clear rule for what counts as a duplicate: one matching value in a column, or a matching combination of values across several columns.
- For the cell-only method, an unused helper column beside the data.
Step-by-Step
- Save a working copy and inspect the data. Use File > Save As to create a copy, or duplicate the worksheet before editing. Check whether the first row contains headings such as Name or Email, and whether there are blank rows splitting the list. Remove or account for blank rows so Excel can identify the full data range. If the data is a formatted Excel table, click inside it; otherwise, click one cell in the continuous data block.
- Choose what “duplicate” means for your list. If a repeated email address means the entire record is redundant, use the email column as the matching field. If only records identical across several fields count as duplicates, use all those fields. For example, two rows with the same customer name but different order numbers may be separate orders; selecting only Name would cause one row to be removed even though the records are not identical in every respect.
- Use Remove Duplicates when whole records should go. Click inside the dataset and select Data > Remove Duplicates. Excel normally detects the surrounding continuous range. Review the range in the dialog before proceeding. If the data is not a table and Excel has selected too much or too little, cancel, select the exact range including its headings, and open the command again. Do not include unrelated columns or data below the list.
- Set the header and matching columns. In the Remove Duplicates dialog, check My data has headers if the first selected row contains column names. Then use the checkboxes to choose the columns Excel should compare. Select one column to treat repeated values in that field as duplicates; select several to compare the combination of fields. Selecting every column removes only rows that match across all selected columns. Click OK to run the removal.
- Review Excel’s result and verify the records. Excel reports how many duplicate values were found and how many unique values remain. Check the first and last records and compare the result with your copy. The first occurrence is kept, while later matching rows are removed from the selected data range. If the wrong rows disappeared, press Ctrl+Z immediately, reconsider the matching columns, and run the command again.
- For a single column where only duplicate cells should be cleared, add a helper column. This approach preserves each row and its other information. Suppose the values to check are in A2:A100, with a heading in A1. Put a heading such as Duplicate? in B1. In B2, enter
=IF(A2="","",COUNTIF($A$2:A2,A2)>1), then fill the formula down beside all the records. The formula marks later occurrences TRUE and leaves the first occurrence unmarked. Its blank check prevents empty cells from being flagged. - Turn the helper results into fixed values. Formulas can recalculate when you clear the original cells, so do not rely on a live formula while clearing. Select the helper results, copy them, and use Home > Paste > Paste Values to replace the formulas with their current TRUE, FALSE, or blank results. Confirm the TRUE indicators remain in place. This also makes the next filtering step predictable.
- Filter to show only the duplicates. Select a cell in the data and choose Home > Sort & Filter > Filter, or use Data > Filter. Open the filter arrow on the helper column and select only TRUE. Excel now displays the later repeated entries. Verify that the visible rows are the ones you intend to alter before clearing anything.
- Clear the duplicate cells, not the whole rows. Select the visible cells in the original value column that correspond to the TRUE rows. Use Home > Find & Select > Go To Special > Visible cells only to avoid selecting hidden rows as well. Then choose Home > Clear > Clear Contents, or press Delete after confirming only the intended visible cells are selected. This empties those cells but leaves the rows and their other columns in place. Do not use Delete Sheet Rows unless you mean to remove entire records.
- Finish and check the cleaned list. Clear the filter to view all records, inspect the formerly repeated entries, and confirm that no unrelated cells changed. You can delete the helper column once you are satisfied. If you want the list compacted with no blank cells, do that as a separate operation on a copy: sorting the entire dataset may change record order, and deleting cells with a shift option can misalign related columns.
On Excel for Mac, the Remove Duplicates command is also on the Data tab in current desktop versions, though the ribbon layout can vary. Excel for the web may present a simplified ribbon; look for Data > Remove Duplicates if available. For clearing filtered cells, confirm that only visible cells are selected before using Clear Contents. If a command is missing in your edition, work in the desktop app or use the helper-and-filter approach available in your version.
Common Mistakes
- Selecting the wrong columns. If you select only a name column, Excel may remove a row for someone with the same name even if the rest of the record differs. Conversely, selecting every column will not identify rows as duplicates if one field differs, such as a date or a trailing space. Choose the fields that represent the identity of a record.
- Assuming Remove Duplicates clears isolated cells. The command removes duplicate records from the range. When several columns are included, the other values in a matching row are removed too. Use the helper method if you need to preserve the rest of each row.
- Leaving the header option unchecked or checking it incorrectly. If a heading is treated as data, it may be compared with the records. If the first record is mistaken for a heading, it can be excluded from duplicate checking. Match the checkbox to the actual first row of your selection.
- Selecting only one column from a multi-column dataset. Remove Duplicates acts on the selected range. If you select just one column beside related data, Excel can remove values from that selection without removing the corresponding information in neighboring columns. That can make rows no longer line up. Select the full record range when deleting records, or use the cell-only process when keeping the rows.
- Deleting entire rows when only contents should be removed. Clear Contents removes the value but leaves the row structure intact. Delete Sheet Rows removes the whole row, including data in other columns. Check the command and selection before confirming.
- Expecting text matching to be case-sensitive. Excel’s Remove Duplicates and COUNTIF-based helper approach generally treat uppercase and lowercase text as equivalent. Values such as Alex and ALEX may therefore be considered duplicates. If case must distinguish entries, use a purpose-built comparison method rather than assuming the standard command separates them.
Tips
- Keep an untouched source. A separate worksheet or saved copy gives you a reliable way to restore records and check what was removed. This is especially useful before deduplicating customer, inventory, or transaction data.
- Normalize data before comparing. Extra spaces, inconsistent punctuation, and different spellings can make visually similar entries behave differently. You can clean a copy of the data first, then compare the cleaned values. Do not change identifiers automatically if the differences might be meaningful.
- Sort for easier review. Sorting the full dataset by the key column can group repeated values together before you remove or clear them. Select the entire dataset before sorting so related fields stay with their records. Sorting changes order, so preserve the original order in your backup if it matters.
- Use a table for a growing list. Excel tables make it easier to identify headers and keep formatting consistent as records are added. Click inside the table before choosing Remove Duplicates, and check the dialog’s column list carefully.
- Decide which occurrence to keep. Remove Duplicates keeps the first matching record in the current order. If you need to keep the newest or oldest entry, sort the whole dataset into the desired order first, then remove duplicates using the appropriate key columns.
- Check for near-duplicates separately. The built-in command finds matching values; it does not decide that “Jon Smith” and “John Smith” are the same person. Review possible spelling variations manually or create a separate review list rather than deleting records based on guesswork.
Troubleshooting
- The command finds no duplicates. Confirm that the comparison columns are checked and that the values really match. Leading or trailing spaces, nonbreaking spaces, different number formats, or one value stored as text and another as a number can cause apparent matches to differ. Inspect the raw cell contents and clean a copy before trying again.
- Excel removes too many records. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Select additional identifying columns so records are considered duplicates only when the relevant combination matches. For example, compare both email and account number instead of email alone if email addresses are shared.
- The wrong copy of a record remains. The first matching record in the current order is retained. Undo, sort the full dataset so the preferred record comes first, and run Remove Duplicates again. Keep the entire row selected during sorting so data stays together.
- The helper column shows unexpected TRUE results. Check that the formula begins at the first data row and was filled down without changing the anchored starting reference. The formula treats repeated values as duplicates without distinguishing letter case. It also ignores blanks through its initial check. If values contain wildcard characters such as an asterisk or question mark, COUNTIF can interpret those as wildcards; inspect those entries separately or use a different exact-match method.
- Clearing duplicates affects hidden rows too. Undo before making further edits. Reapply the filter, select the target cells, and choose Home > Find & Select > Go To Special > Visible cells only before clearing. Carefully check the selection; a filtered view does not by itself guarantee that a selection excludes hidden cells.
- The list is still not compact after clearing cells. Clear Contents leaves blank cells in their original positions by design. If you need a compact list, make a separate copy and use a safe, deliberate method to remove or relocate blanks. With multiple columns, never shift cells in only one column unless you intend to break the row relationships.
Frequently Asked Questions
Does Remove Duplicates delete cells or whole rows?
It removes duplicate rows from the selected range, keeping the first occurrence. If the selection contains several columns, the matching row’s values in those columns are removed together. To clear only repeated entries in one column while retaining the other row data, use the helper-column and filter method.
Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, select only the column that defines a duplicate. When removing records from a multi-column dataset, make sure the whole dataset is included in the selected range; otherwise, related columns may not stay aligned with the remaining values.
Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first occurrence in the current order and removes later matching records. Sort the complete dataset first if you want a particular record, such as the newest entry, to be the one that remains.
Can I highlight duplicates before deleting them?
Yes. Select the cells you want to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Review the highlighted entries before using Remove Duplicates or clearing cells. Highlighting identifies repeated values but does not remove them.
Will Remove Duplicates treat uppercase and lowercase as different?
Usually not: standard duplicate removal is not case-sensitive, so entries that differ only by letter case can be treated as duplicates. If capitalization is meaningful in your data, make a copy and use a case-sensitive comparison workflow before deleting anything.
Can I undo a duplicate removal?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. If you have made other changes or closed the workbook, undo may no longer be available, which is why a saved copy is the safest backup.
Related
- How to Copy Large Data in Excel Safely and Quickly
- Copy Only Visible Cells in Excel Without Hidden Data
- Copy Just the Fill Color in Excel Without Other Formatting
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Alphabetize Function: Sort Data A to ZGoogle Sheets does not have a function named ALPHABETIZE; use the SORT function to create an alphabetized result,…
- Excel Power Query Merge vs Append: Which Should You Use?In Excel Power Query, Merge joins two queries side by side using one or more matching columns; Append…
- Excel Date Without Year: Display a Date Without Its YearTo show an Excel date without its year, select the date cells, press Ctrl+1 on Windows, choose Number…
- How to Insert Citations in Microsoft Word: A GuideTo insert a citation in Microsoft Word for Microsoft 365 on Windows, open the References tab, choose a…
- How to Schedule an Email in Outlook on Desktop and WebTo schedule an email in Outlook, write your message, open the arrow or menu beside Send, choose Schedule…
- Can You Delete Duplicates in Excel? Methods and TipsYes. In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.