Excel: How to Automatically Highlight Duplicate Values Across Multiple Columns Using Conditional Formatting?
Hey everyone, I'm running into an issue that's driving me crazy... This might be a silly question, but I'm stuck on something that should probably be simple... I am working on a project in Excel 365 where I need to highlight duplicate values found across several columns in a single sheet. I have columns A to C filled with data, and I want to apply conditional formatting to highlight any duplicates across these columns. My current attempts using the built-in conditional formatting rules don't seem to work as expected, especially when it comes to checking the duplicates across non-contiguous columns. Here’s what I tried: 1. I selected the range A1:C100 and went to Conditional Formatting > New Rule. 2. I chose ‘Use a formula to determine which cells to format’ and used the formula `=COUNTIF($A$1:$C$100, A1) > 1`. 3. I set the format to fill the cells with a red background. However, this only highlights duplicates in the first column, not across all three columns. I also tried modifying the formula to `=COUNTIF($A$1:$C$100, A1) > 1`, but it still doesn’t seem to work correctly for duplicates that are present in different columns. I expect cells with values like 'Test' in both A1 and B2 to highlight, but it doesn't happen. Is there a better way to implement this? Should I be using a different approach or formula to make sure that duplicates across all specified columns are highlighted correctly? Any help would be greatly appreciated! Any help would be greatly appreciated! Am I missing something obvious? This is happening in both development and production on Windows 11. Any ideas how to fix this?