Finding matches or differences in Google Sheets can be done by using a combination of CONDITIONAL FORMATTING and the COUNTIF function.
Step 1, select the area of interest.
Step 2, click “Format”, “Conditional formatting” and “Custom formula is”.
Step 3, write =COUNTIF and specify the range of interest as well as the starting cell for criterion within the parenthesis. Then make the formula true if it counts MORE THAN 1. Also, add dollar signs to the range for it to work properly! Now, you have a function that will find and format all duplicate values!
Step 4, while still in the conditional format rules window, press “Add another rule” to use the existing rule as a template!
Step 5, add a NOT statement before the COUNTIF function and enclose the existing formula within the parentheses of this new NOT statement. Finally, change the “Formatting style” so that it is easy to separate between unique and duplicate values.
5 окт 2024