How To Expose And Correct Duplicates Using Excel


Handling duplicates can be very tasking, most especially when you have a large database to deal with.

Data duplication can set your budget spinning, introducing irregularities, inconsistencies, wastage of resources, and perhaps lack of trust.

However, there’s a way out. You can prevent duplicates before they happen by combining a simple function with data validation.

Microsoft Excel has built-in tools to expose and, even delete duplicates in a simple way.

Today, we’ll only focus on how to expose duplicates using Excel with the Excel Data Validation Feature + COUNTIF() function.

This feature checks values as you enter them and depending on the rules you specify, reject or accepts the value.

ALSO READ: Microsoft Launches A New Tablet Called “Surface Go”

How it works

The COUNTIF() function uses the following SYNTAX, COUNTIF(range, condition) to count the number of cells in a range that meet a specific condition.

What you’ll do is supply the range and a condition as arguments using the syntax COUNTIF(range, condition)

For instance,

Let’s use this function to count the number of times a phone no. occurs within a column.

The phone no. column of the sheet shown in Figure 1 allows duplicates

No duplicates detected


Right now, we’ll use data validation to prevent duplicate number in the phone no. column.

First, enter the following function cell D4
= COUNTIF(TABLE1[ phone no.],C4)

The function uses structured referencing because the data is formatted as a Table object. Because the value +1(716)532-2150 occurs only one time within the column, the function returns 1. However, if you repeat one of the values, the respective functions return 2 as shown in figure 2 below


this figure 2 exposes data duplications