excel find duplicate values in multiple columns

Excel automatically copies the formula to the other cells. Always write the formula for the upper-left cell in the selected range (A1:C10). Excel highlights the duplicate rows.Įxplanation: if COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1) > 1, in other words, if there are multiple (Leopard, Africa, Zambia) rows, Excel formats cell A1. =COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1) counts the number of rows based on multiple criteria (Leopard, Africa, Zambia). Note: the named range Animals refers to the range A1:A10, the named range Continents refers to the range B1:B10 and the named range Countries refers to the range C1:C10. Enter the formula =COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1)>1Ħ. Select 'Use a formula to determine which cells to format'.ĥ. To find and highlight duplicate rows in Excel, use COUNTIFS (with the letter S at the end) instead of COUNTIF.Ĥ. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight names that occur more than 3 times.

Notice how we created an absolute reference ($A$1:$C$10) to fix this reference. Excel highlights the triplicate names.Įxplanation: = COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. Select 'Use a formula to determine which cells to format'.Ħ. On the Home tab, in the Styles group, click Conditional Formatting.ĥ. First, clear the previous conditional formatting rule.ģ. Execute the following steps to highlight triplicates only.ġ. Triplicatesīy default, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), etc. Note: select Unique from the first drop-down list to highlight the unique names.

Click Highlight Cells Rules, Duplicate Values.Ĥ. On the Home tab, in the Styles group, click Conditional Formatting.ģ.

Excel find duplicate values in multiple columns