There some times that you want to have duplicate data. And sometimes you want to eliminate duplicates because it makes it harder to understand your data.
If you want to find out if there are duplicate data in your excel sheet, you can use conditional formatting to find and highlight duplicate data.
Doing so will highlight your data that is duplicate and then you can review the duplicates and decide if you want to remove them.
However, that is a lot of work and also not as efficient if you have large amount of data. The remove duplicate feature allows you to find and remove duplicates in one step and is efficient for large amount of data.
Below I am going to show you both methods
Find and remove duplicates using Conditional Formatting
- Select the cells you want to check for duplicates.
Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.
- Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.
- This will highlight the duplicate values based on the value you have selected. You can then decide if you want to delete the data
Find and remove duplicates values using ‘Remove duplicate values’
When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.
Select the range of cells that has duplicate values you want to remove.
Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.