The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Excel offers five ways to handle duplicate entries, from highlighting matches for review to permanently removing repeated rows. The right choice depends on what you mean by “duplicate”: a repeated value in one column, or a repeated record across selected columns. If you plan to delete anything, make a copy first and choose the comparison columns carefully.
First decide what counts as a duplicate
A repeated customer ID is not necessarily a duplicate record. If you compare only the ID column, rows with the same ID can count as duplicates even when names, dates, or other fields differ. If you compare every column, rows count as exact duplicates only when the corresponding values match across the selected columns.
As an Amazon Associate I earn from qualifying purchases.
Excel evaluates duplicate values using what cells display, including formatting; dates that appear differently may be treated as unique. In Power Query, the columns selected for comparison likewise determine which rows are duplicates. Choose the key columns that match your cleanup goal before applying any method.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Choose a method based on what you need
| Your goal | Use | What happens to the source |
|---|---|---|
| Permanently clean the selected data | Remove Duplicates | Matching rows are deleted; make a copy first. |
| Create a separate list of unique records | Advanced Filter | Copying results leaves the original data unchanged. |
| Review matches before acting | Conditional Formatting | Duplicates are highlighted, not removed. |
| Maintain a formula-driven distinct result | UNIQUE | Returns results separately and can update as source data changes. |
| Remove repeated rows while shaping imported data | Power Query | Removes rows according to the selected comparison columns. |
1. Remove duplicates permanently
Use Excel’s Remove Duplicates command when you intend to delete repeated entries from the selected range or table. Microsoft advises copying the original data first: removing duplicates permanently deletes data from the selected range.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
- Select the range or table you want to clean.
- Choose Data > Remove Duplicates.
- Select the columns Excel should use to identify duplicates. Select one column to compare by that field, or multiple columns to compare combinations of values.
- Confirm the operation and review the remaining data.
When you select only some columns as the comparison key, Excel can remove an entire matching row—including values in columns that were not selected. For example, comparing only customer IDs may remove one of two rows with the same ID even if their other details differ. Microsoft also says outlined or subtotaled data must be handled before using this feature. See Microsoft’s instructions for finding and removing duplicates and its guidance on filtering or removing duplicate values.
2. Filter or copy unique records with Advanced Filter
Advanced Filter lets you show unique records without deleting duplicates, or copy those unique records to another location. Filtering in place temporarily hides records; it does not remove them.
- Select the range or table, including its headers.
- Choose Data > Advanced in the Sort & Filter group.
- Choose to filter the list in place or copy the results to another location.
- Check Unique records only, then apply the filter.
If you want to preserve the original view and work from a distinct list, choose the copy option and specify where the results should go. Microsoft explains the distinction: “When you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values.” Read the Microsoft Advanced Filter guidance.
3. Highlight duplicates for review
Conditional Formatting is the safest starting point when you need to inspect matches before deciding what to keep. It marks duplicates visually and does not delete data.
- Select the cells you want to check.
- Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a formatting style and confirm.
Review the highlighted cells in context before removing rows; a repeated value in one column may be valid when other fields differ. Excel cannot highlight duplicates in the Values area of a PivotTable report. On systems using a double-byte character set, the built-in rule may treat halfwidth and fullwidth characters as equivalent. Microsoft documents a COUNTIF-based formula rule as an alternative when those forms need to be distinguished. Details are in Microsoft’s duplicate-finding guidance and conditional-formatting instructions.
4. Return distinct results with UNIQUE
The UNIQUE function produces a distinct list without deleting the source records. Microsoft documents it for Microsoft 365 and Excel 2021/2024 on supported platforms; it is not available in every Excel release.
Rank #4
For distinct values or rows, use:
=UNIQUE(array)
The optional by_col argument controls whether Excel compares columns rather than rows. The optional exactly_once argument returns only values or records that occur exactly once; without that setting, the result includes each distinct value or record once, including those that appeared repeatedly in the source.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteDynamic-array results spill into neighboring cells, so leave room for the output. Tables and structured references can help the formula’s source adapt as data changes. Consult Microsoft’s UNIQUE function documentation for argument details and supported versions.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.5. Remove duplicate rows in Power Query
Power Query is useful when duplicate removal is part of preparing or shaping data. Select the columns that define a duplicate, then use the duplicate-row removal command in the Power Query editor. Selecting a subset compares rows by that subset; selecting all relevant columns compares the fuller record.
Decide which fields form the comparison key before removing rows, especially when records share an ID but differ elsewhere. Microsoft’s Power Query instructions for keeping or removing duplicate rows describe the workflow.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
