Search code examples
google-sheetsgs-conditional-formatting

Color a cell if it matches any other cell in a Google sheet

I'm trying to use conditional formatting (and maybe the MATCH function?) in a Google Sheet to color any cells from A1 to G10 which have the same contents. For example, if B3 says "Go Seahawks" and so does F5, I would like both of those cells to be colored.

I looked at this question which seemed close (Conditional formatting cells if their value equals ANY value of another column), but my formula skills are weak and I couldn't figure out how to make it work the way I want it to. I'd be very appreciative if anyone here can help me figure it out.

Solution

  • MATCH only operates on a one-dimensional array, so COUNTIF is a better approach.

    Add a new conditional format to the range A1:G10, with a custom formula as follows:

    =COUNTIF($A$1:$G$10, A1)>1

    This will highlight any value that occurs in the range more than once.