Highlight duplicate rows

Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you'll need to use your own formula, as explained below. 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: =COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1 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: =COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1