To highlight duplicate columns, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula used to highlight duplicate columns is: Note that row references are fully absolute, while cell references are mixed, with only the row locked.
With a helper row
If you don’t mind adding a helper row to your data, you can simplify the conditional formatting formula quite a bit. In a helper row, concatenate all values in the column. Then you can use COUNTIF on that one row to count values that appear more than once, and use the result to trigger conditional formatting in the entire column.
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.
