Why Should You Find Duplicates in Excel
Before we dive into the methods to find duplicates in Excel, let's first see why to do that.
Duplicate value indicates the information that appears more than once in a datasheet. Its existence places a negative effect on the quality, accuracy, and reliability of your data. As a result, the generated conclusion will be misleading and useless.
Duplicates will slow down productivity since it may lead to overlapping work. Using tools to find and remove duplicates in Excel is also time-consuming. If duplicates exist in customer contact information, there’s the potential to irritate customers by sending duplicated messages. That will also hurt your brand image.
Now, let’s dive into how to find duplicates in Excel.
Find Duplicates in Excel Using Conditional Formatting
Conditional formatting is a built-in Excel tool that enables you to change the appearance of cells according to specified conditions. You can resort to this feature to find duplicates in Excel.
Step 1: Open your Excel document and select the cells that contain duplicate values;
Step 2: Click Conditional Formatting under the Home tab, hover over Highlight Cells Rules, and select Duplicate Values;
Step 3: Choose Duplicate from the drop-down list, select the formatting styles of duplicated cells as you prefer, and hit OK to find duplicates in Excel;
This process will highlight duplicates in Excel. After that, you can apply a filter to display only the rows that contain duplicates to remove them.
Step 1: Tap Filter under the Data tab or use the Ctrl + Shift + L hotkey to enable the filter function;
Step 2: Click the filter drop-down button in the header and select Filter by Color;
Step 3: Select the font or fill color applied by the conditional formatting above to make only rows containing duplicates visible.
Find Duplicates in Excel with the COUNTIF Formula
The COUNTIF formula can help you find duplicates in Excel by counting the appear times of every cell.
Step 1: Open your Excel file, insert a new column next to the column with duplicates;
Step 2: Input =count in an empty cell and double-click COUNTIF to enable the formula;
Step 3: Input the range and criteria in the bracket to check for duplicates in Excel, and hit Enter;
Range refers to the cells you want to find duplicates in Excel while criteria indicates the value you want to count for. For instance, the formula in the picture below will count the appearance times of B4 value in B4 to B18.
Step 4: Drag the formula to autofill all the results.
While using this method, make sure your data is not interrupted by a blank row. Otherwise, there may be mistakes.
The range must be fixed with the dollar sign so that the cell reference won’t change while autofilling cells. You can do this quickly by pressing “F4” after you select the range. That’s how to find duplicates in Excel using the COUNTIF formula.
Find Duplicates in Excel with the VLOOKUP Formula
If you want to check for duplicates in two columns, VLOOKUP will be an excellent choice. VLOOKUP is a built-in function in Excel to compare two sheets or more. It will search for a value in the first column of a table range and returns a value from another column in the same row.
Here’s how to use the “=VLOOKUP(List1,List2,TRUE,FALSE)” formula to find duplicates in Excel.
Step 1: Setting up your columns containing repeated data;
Step 2: Input =VLOOKUP(A2:A10,B2:B10,TRUE,FALSE) and then press Enter;
Step 3: Drag the formulated cell downwards to show duplicates in Excel.
The “#N/A” values indicate that the value in column A is unique. Otherwise, it means the value is duplicated.
Save Excel as PDF after Finding Duplicates
After you find duplicates in Excel and use tools to remove them, it’s suggested to save your Excel document as PDF. That’s because PDF files is safer and more widely used. Simply click “File > Save As” and select “PDF” will be OK. If you want to convert PDF to Excel you can do that with the assistance of SwifDoo PDF.
After saving Excel as PDF, you can search for duplicates using a PDF editor as an alternative way to find duplicates in Excel.
In addition to conversion tasks, this PDF program also lets you to locate specific content in a PDF file using its searching tool. You can find the tool under the “Home” tab or use the “Ctrl + F” shortcut key to invoke it.
Conclusion
Learning how to find duplicates in Excel is necessary since it is one of the most common problems in our daily work today, especially as data grows. The built-in Excel tools can help you do that in a breeze. The most straightforward method is using the Conditional Formatting feature. COUNTIF will count duplicates in Excel for you while VLOOKUP will be excellent choices if you need to process a large amount of data.