Duplicate values can make an Excel spreadsheet difficult to analyze and may lead to inaccurate calculations. Fortunately, Excel provides simple tools to identify and remove duplicates from your data.
In this guide, you'll learn how to remove duplicate values safely and how to find duplicates without deleting them.
What are duplicate values?
Duplicate values are repeated entries in your dataset.
Duplicates can appear when:
Data is imported from multiple sources.
Information is copied and pasted multiple times.
Records are entered manually.
Different datasets are combined.
Removing unnecessary duplicates helps keep your data clean and reliable.
Remove duplicates using Excel's built-in tool
Excel includes a built-in Remove Duplicates feature that allows you to quickly delete repeated records.
Before removing duplicates, it's a good idea to create a copy of your worksheet. This gives you a backup in case you accidentally remove data you wanted to keep.
Select your data
First, select the range of data you want to check.
If your dataset is organized as an Excel table, you can click anywhere inside the table and Excel will usually detect the full data range automatically.
For example:
A1:D100Make sure your selection includes all the columns needed to identify whether two rows are duplicates.
Choose the columns to check
Go to:
Data → Remove Duplicates
Excel will display a list of columns.
You can choose which columns Excel should use when checking for duplicate records.
For example:
Select Email if every customer should have a unique email address.
Select Customer ID if each customer ID should appear only once.
Select multiple columns if the combination of values determines whether a record is a duplicate.
After selecting your columns, click OK.
Excel will remove the duplicate records and display a message showing how many duplicate values were removed.
Find duplicates without deleting them
Sometimes you may want to review duplicate values before deleting anything.
In this case, you can use Conditional Formatting.
Select your data, then go to:
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
Excel will automatically highlight repeated values.
This is useful when you want to:
Review duplicates manually.
Check whether repeated values are actually errors.
Identify potential problems before modifying your dataset.
Final tips
Before removing duplicates, keep these points in mind:
Always create a backup of important data.
Make sure you select the correct columns.
Remember that two rows may look similar but contain important differences.
Use Conditional Formatting when you want to review duplicates before deleting them.
Excel's Remove Duplicates feature is a quick and effective way to clean your data. For larger datasets, combining this tool with formulas, Power Query, or other data-cleaning techniques can provide even more control.
By understanding how Excel identifies duplicate records, you can keep your spreadsheets cleaner and improve the reliability of your analysis.