When cleaning large customer lists or sales records, duplicate entries are common. Checking thousands of rows manually takes hours and leads to mistakes, while deleting entries blindly risks losing critical data. Here is how to isolate and clean duplicate values safely without corrupting your original data.
Quick Summary
- Visual check: Highlight cells in color via [Home] > [Conditional Formatting] > [Highlight Cells Rules] > [Duplicate Values]
- Permanent deletion: Always back up a copy to another sheet before running [Data] > [Remove Duplicates]
- Watch formatting: Mismatched formats like dates (3/8/2006 vs Mar 8, 2006) will not be detected as duplicates
- Formula extraction: In Excel 2021 and M365, extract unique lists safely with the =UNIQUE(range) formula
Highlight Duplicates First: Using Conditional Formatting
If you want to check where duplicates appear before deleting anything, use Conditional Formatting. It works just like highlighting overlapping records with a marker before throwing paper away.
Select the target range, then go to [Home] tab > [Styles] group > [Conditional Formatting] > [Highlight Cells Rules] > [Duplicate Values]. Select your preferred highlight color in the dialog box and click OK to fill duplicate cells with color.
Note that duplicate highlighting in Conditional Formatting does not apply to data in the Values area of a PivotTable report, so run it on standard data tables.
Permanently Remove Duplicates: Always Back Up First
To delete duplicate rows completely, use Excel’s built-in removal tool. Unlike filters, this permanently removes rows from the worksheet rather than hiding them, so make sure to back up the sheet by copying it to another tab first.
Click any cell inside your data table, then go to [Data] tab > [Data Tools] group > [Remove Duplicates]. In the popup window, select the columns to check for duplicates and click OK to delete matching rows.
If subtotals or outlines are active on your sheet, this feature will not function properly. Remove all subtotals and outlines before proceeding.
Related Articles
How to Mask Data in Excel in 1 Second Without Complex Formulas
How to Fill Blank Cells in Excel All at Once Using Shortcuts
Why Are Matching Values Not Detected? Formatting Rules
Excel compares both the underlying raw data and the visible display format. If values look different on screen, Excel treats them as distinct unique values.
| Source 1 | Source 2 | Result | Reason |
|---|---|---|---|
3/8/2006 | Mar 8, 2006 | Unique (Not removed) | Same date, different display format |
1.00 | 1 | Unique (Not removed) | Same number, different decimal formatting |
=2-1 | =3-2 | Duplicate (Removed) | Different formulas, but identical result and format |
Before running Remove Duplicates, standardize the cell format across each column (such as General or Short Date) to prevent missed duplicates.
Non-Destructive Alternatives: Advanced Filter and UNIQUE Formula
If you want to extract a unique list without modifying your original dataset, there are better alternatives.
Go to [Data] tab > [Sort & Filter] > [Advanced] and select ‘Unique records only’ to temporarily hide duplicate rows or copy unique values to another location.
In Excel 2021 and Microsoft 365, entering =UNIQUE(range) into an empty cell automatically outputs a clean dynamic array of unique values without modifying the original data.
Always duplicate your worksheet to a backup tab and inspect overlapping rows with Conditional Formatting before deleting.
Related Guides
- How to Instantly Repeat Your Last Action in Excel (F4 Shortcut)
- How to Add Hyphens to Phone Numbers in Excel in 3 Seconds Without Formulas
- How to Recover Unsaved Excel Files After Clicking ‘Don’t Save’
- How to Repeat Header Rows on Every Page When Printing Excel Sheets
답글 남기기