How to Highlight Duplicates in Microsoft Excel
Using conditional formatting one may easily show duplicate values in any chosen range of data. Find all duplicate values, and highlight any values that appear more than once.
=COUNTIF(A:A,A2)>1
This is the meat, and here is how:
- Highlight the cell to work on.
- Click Format > Conditional Formatting
- Set Formula Is
- Enter the formula above, replacing the ranges as follows:
- (A:A means that it will look inside the entire A range. Replace this with your own range, e.g. B3:B5 or CC:CC
- ,A2) means to count how many times the value in A2 appears in the designated range. This needs to match the cell you are currently working on.
- Then set the format you want to see when the count of identical values existing in the range is greater than one. This value can be altered to a higher number in order to highlight cells that have more than two duplicates, e.g. change it to be >5 to highlight the cell when at least 5 cells have the same value as the current cell, including this cell itself.
- Copy the formula only to other cells in the range. To do this, copy the current cell, then Paste Special and choose Formulas and paste into the other cells in the range.
An excellent tutorial is at MREXCEL.COM, and thanks to that site for this knowledge and info.
Posted under Excel, Microsoft, Office
This post was written by Content Curator on November 16, 2009
