Data hygiene is one of the most critical aspects of working with large spreadsheets. Whether you have exported a mailing list from a CRM system or consolidated data from multiple departments, duplicate rows can ruin your statistical analysis and cause embarrassing administrative errors. Learning how to remove duplicates in Excel is an essential skill for anyone dealing with data on a daily basis.
In this guide, we will provide expert advice on how to use Excel’s built-in deduplication tool safely. We will show you how to highlight duplicates before deleting them, ensuring you never accidentally destroy valuable data.
Step 1: Highlight Duplicates Before Deleting
Before you permanently delete any rows, it is highly recommended that you visually inspect your data. We can do this using Conditional Formatting.
- Highlight the columns where you suspect duplicate data exists (for example, Column A containing Email Addresses).
- Navigate to the Home tab on the Excel ribbon.
- Click on Conditional Formatting in the Styles group.
- Hover over Highlight Cells Rules and select Duplicate Values…
- A dialogue box will appear. You can choose the colour formatting (the default is usually light red fill with dark red text). Click OK.
Scroll through your data. You can now clearly see which cells Excel considers to be exact matches. This visual check prevents you from accidentally deleting rows that only share a first name but have different email addresses.
Step 2: How to Remove Duplicates Safely
Once you are confident that your dataset contains true duplicates that need to be removed, you can use the automated removal tool.
- Select your entire dataset by clicking any cell within your data table and pressing Ctrl + A.
- Navigate to the Data tab on the Excel ribbon.
- In the Data Tools group, click the Remove Duplicates button (it looks like a small blue and white table with a red cross).
- A dialogue box will open. Crucial Step: Ensure the box labelled “My data has headers” is checked if your first row contains titles (like “Name”, “Email”, “Phone”). This prevents your headers from being deleted.
- You will see a list of all your columns. Select the columns that dictate a duplicate. For example, if you want to delete rows only if the Email Address is identical, untick everything except the Email column. If you want to delete rows only if the Name AND Email are identical, tick both.
- Click OK.
Excel will instantly delete the duplicate rows (keeping only the first instance it found) and present a pop-up message telling you exactly how many duplicate values were found and removed, and how many unique values remain.
Frequently Asked Questions
Does the tool delete the original row or the duplicate row?
Excel works from top to bottom. It will always preserve the first instance of a row it encounters and delete any subsequent matches found further down the spreadsheet.
What if I accidentally delete the wrong rows?
If you realise you have made a mistake immediately after running the tool, simply press Ctrl + Z (or click the Undo arrow at the top left of the screen) to restore your deleted data.
By incorporating these data validation steps into your workflow, you can clean up messy spreadsheets with complete confidence.
