Microsoft office Excel Tips – Use Conditional Formatting To Find Duplicate Data

There may be many reasons to look for and eliminate duplicate data in Excel. If you use Excel as lots of people do for managing lists or tables of data you may manage a list of customers, employees or an inventory then duplicate records need to be eliminated. Also as a database grows duplicate entries can be a problem, which in themselves can cause issues for data quality and analysis.

You also may have inherited an Excel workbook or a Comma Separated Value (CSV) and are unaware of the quality of the data list. It is usually good practice to review data quality and cleanse the data. One the first stages of this cleansing process is to find and remove duplicate data entries.

One effective way to identify duplicate data in Excel is to use Conditional Formatting.

First of all, you need to set the conditional formatting rules.
Select the range of cells that contain your data you want to analyze i.e your data table or list.

Select the Home Tab in Excel Hit Conditional Formatting Select the option New Rule to display the Conditional Formatting Dialog Box Hit the option Use a formula to determine which cells to format Under Format values where this formula is true to type following formula =COUNTIF($A$1:$C$3), A1)>1 This formula will count how many values in the range A1:C3 is identical to the value in cell A1 in the first instance and appear more than once. As you have applied this formula to all cells in the data range it looks at all cells in the range and applies the same formula. Hit the Format button and select the formatting you want Excel to show if it finds duplicate data i.e if the condition specified is true. I usually specify a color to highlight duplicate values. Just choose whichever formatting you want to so that duplicates stand out for you. Hit OK to finish and highlight the duplicate values specified.

Let’s look at it this formula in action using an example.
Here is my data range in cell A1:C3
150 170 180
170 123 176
176 170 176
Applying my formula, with formatting to BOLD any duplicate values my result would look like the below
150 170 180
170 123 176
176 170 176
As you can see the values 170 and 176 are easily seen as duplicates in my data range.
A quick and easy way to highlight duplicate data in Excel!

Robert Morris a Microsoft Office expert has been working in the technology industry from the last 5 year. As a technical expert, he has written technical blogs, manuals, white papers, and reviews for many websites such as office.com/setup

Content Source :http://office-comoffice.com/blog/2019/04/24/microsoft-office-excel-tips-use-conditional-formatting-to-find-duplicate-data/