How to Find Duplicate in Excel with Conditional Formatting

Find Duplicate in Excel

When you are dealing with many amounts of data, the possibility you will confront with duplicate data. Meanwhile, work demands require you to be free from this kind of thing. If that’s the case, there is no other way you can do than look for or find the duplicate data. Then you can eliminate it. How to find duplicate in excel? It is easy. Why? Fortunately, there is a future named conditional formatting feature in excel to complete this problem. Besides, you can find duplicate in excel using formula too.

How to Find Duplicate in Excel Using Conditional Formatting Feature?

There are some ways to find duplicate in excel. You can use the conditional formatting feature as the solution on how to find duplicate in Excel. Besides, you can use formula and columns to solve this problem. Continue to the point, here some ways to find duplicate in Excel using conditional formatting.

1. Mark your columns or data ranges. It may be one column or an entire column

2. The next way, you can click the home menu and choose conditional formatting. Continue to highlights cell rules, then click duplicate values.

3. Congratulations, you have done it. Your duplicate data will mark in a different color. So, you can specify other colors in the popup window that appears.

Furthermore, after multiple data are obtained, you can make further modifications as needed. In addition to mark two date ranges at once, you can duplicate data in only one column of data. Just mark one column of data, then repeat the steps above.

Criteria in Coloring Conditional Formatting

There are some rules in coloring conditional formating. This function is to give color in conditional formatting in some rules.

1. Greater than

Greater than in Microsoft Excel will provide highlights in the form of colors with the criteria that the value of the cell is greater than what has been determined by these conditions.

2. Less than

Less than give Highlight in a cell with the criteria less than criteria which we plan before

3. Equal to

Equal to in Microsoft Excel will give a highlighted member whose value is the same as what we have specified. For example, we give a value of 10, then all cells whose value is 10 will be colored

4. A date occurring

A date occurring will give highlight cell which days happen as the date we plan before

5. Between

Between in Microsoft Excel will give a Highlight with criteria between what we have set with this condition. For example, we determine a value 4 to 10 then it will highlight members with a value 4 to 10.

How to Avoid Duplicate Data in Ms. Excel

You can use data validation to avoid duplicate data in Ms. Excel. With the function of the data validation menu, data has been inputted will be validated if you input them again. So, you can use data validation to avoid double input in Excel, there are several ways you can follow.

1. Choose range or cell. Then, you can block the range or cell you choose.

2. Choose data, and then click data validation

3. Then, choose setting and validation criteria in allow menu. After that, you can fill the formula.

4. Click tab alert. Find the title and error message and fill them. After that, you will see that the inputted data appears to have existed before.

5. Click okay. When you enter data in the cell that turns out to be available, it will be displayed in the massage

With the steps given above, of course, you can immediately practice. The function of this feature is simple. It will make you easy when you are doing data input, where it is very much and takes time if you do it manually.

It is easy, right? Make sure in practicing how to avoid this double data input you do it right without anyone step you miss. Also, pay attention to every sign given in the tutorial above. It is because the error sign that you do certainly will not give results as you want. By following the steps properly and correctly, your work will be more effective and efficient and can save enough time.

So, it is not a problem again to work on data through Ms. Excel. You can use conditional formatting as a solution. Well, do not forget the steps on how to find duplicate in Excel above. Your work will become easier and more comfortable.