In this article, you will learn how to find duplicates in Excel. Ms. Excel is a powerful tool for almost every business or organization. We work with data daily and what better tool to use than Excel? It is a simple tool but at the same time full of advanced features which we can use to enjoy better record keeping. As business records expand, there’s a high chance of data being duplicated. Luckily, Excel comes with its built-in feature of finding duplicate data. Here's the step-by-step guide on finding and handling duplicate entries in your Excel spreadsheets.
a) Using Conditional Formatting
Step 1: Select the Range of data
- Select the cells in which you want to find duplicates.
Step 2: Apply Conditional Formatting
- Visit the Home tab.
- Find the Conditional Formatting in the Styles group.
- Click Highlight Cells Rules > Duplicate Values.
Step 3: Select Formatting Options
- In the Duplicate Values dialogue box, you can select the formatting you'd like to apply on the duplicates (e.g., Yellow fill with dark yellow text).
- Click OK.
The duplicate values in the selected range should be shaded by now.
b) Using the Remove Duplicates Tool
Step 1: Select the Data to check
- Select your range of cells.
Step 2: Open the Remove Duplicates Tool
- Move to the Data tab.
- Select Remove Duplicates in the Data Tools group. This will delete duplicates so don’t use it if you only want to view your duplicates.
Step 3: Select Columns
- A pop-up will come up titled Remove Duplicates, choose the columns you want to check for duplicates.
- Click OK.
- Excel will then get rid of the duplicate rows, giving a summary of how many duplicates were found and deleted.
c) Using Formulas
Step 1: Use the COUNTIF Function
Using this formula, you are going to count records only if they meet certain criteria. Once you input the formula =COUNTIF(range, criteria) in a new column, the ‘range’ is the group of cells being searched through, and the ‘criteria’ is the particular cell being used to test for duplication on other cells.
Examples:
=COUNTIF(A:A, A2)
This formula then counts how many times the value in cell A appears in the column.
Step 2: Filter or Sort by the Formula Result
To identify the duplicates(which will be the values with a count greater than 1), simply filter or sort the column with the COUNTIF results.
Tips for Handling Duplicates in Excel
- Backup Your Data: Backing up data is essential in every single step whether It’s before or after removing the duplicates.
- Review Before Deleting: Run a careful review of the duplicates identified to ensure you do not remove unnecessary data.
- Use Pivot Tables: Pivot tables can also be used for summarizing and identifying duplicates in large datasets.
Using these methods, you will be able to find and control duplicates in your Excel sheets with ease; this will keep your data clean and accurate.
A note from Apexlistings
This website brings together verified freelancers who have different skill sets to help you perform online tasks with ease. If you would like to hire someone to clean your Excel records by finding and removing duplicates or doing any other online tasks such as article writing, this is the right platform. Create a free account and post your task. You can also reach us via Whatsapp at 0799353440 and tell us about your task.
Written by Antony, an independent contributor on Apexlistings.