If you want to highlight duplicate rows in an unsorted set of data, and you don’t want to add a helper column, you can use a formula that uses the COUNTIFS function to count duplicated values in each column of the data. For example, if you have values in the cells B4:D11, and want to highlight entire duplicate rows, you can use rather ugly formula:
Named ranges for a cleaner syntax
The reason the above formula is so ugly is that we need to fully lock each column range, then used a mixed reference to test each cell in each column. If you create named ranges for each column in the data: col_a, col_b, and col_c, the formula can be written with a much cleaner syntax: The helper column option “cheats” by combining all values in a row together in single cell using concatenation. Then COUNTIF simply counts the number of times this concatenated value appears in column D.
Helper column + concatenation
If you don’t mind adding a helper column to your data, you can simplify the conditional formatting formula quite a bit. In a helper column, concatenate values from all columns. For example, add a formula in column E that looks like this: Then use the following formula in the conditional formatting rule: This is a much simpler rule, and you can hide the helper column if you like. If you have a really large number of columns, you can use the TEXTJOIN function (Excel 2016 365) to perform concatenation using a range: You can then use COUNTIF as above.
SUMPRODUCT
If you’re using a version of Excel before 2007, you can use SUMPRODUCT like this:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
