Then the IF function will show “ Duplicates” for TRUE and “ No duplicates” for FALSE. Then it will show TRUE for greater than 1 otherwise FALSE.

The SUMPRODUCT function will check the array whether it is greater than 1 or not. You will notice that the duplicate rows are now remarked with “ Duplicates”. ➤ Then hit the Enter button and use the AutoFill option. ➤ Write the given formula in the formula box. ➤ Then select “ Use a formula to determine which cells to format” from the Select a Rule Type bar. ➤ Then click Conditional Formatting > New Rule.Ī dialog box named “ New Formatting Rule” will open up. The COUNTIF function counts the number of cells in a range that meets a given criterion.

In this method, we’ll again use Conditional Formatting with the COUNTIF Function. Method 2: Apply Conditional Formatting and COUNTIF Function to Find Clone Rows in Excel Read more: Excel Find Duplicate Rows Based on Multiple Columns From that, we can easily detect our duplicate rows. Now you will notice that the duplicate combined values are highlighted with the selected color. ➤ Then select your desired color from the color selection drop-down bar. ➤ Click as follows: Conditional Formatting > Highlight Cell Rules > Duplicate ValuesĪ dialog box named “ Duplicate Values” will appear.

➤ Then hit the Enter button and use the Fill Handle tool to copy the formula for the other cells.

Find and merge duplicate rows in excel