Comments on: How to highlight duplicates in Google Sheets

There are different ways to deal with duplicates in Google spreadsheets. But highlighting them is the best way to spot them instantly. Here you'll learn how to color duplicate cells, columns, and rows while ignoring or taking their first occurrences into account. Continue reading →

Table of contents

Comments page 2. Total comments: 53

  1. Thank you

  2. Hello
    Formula =COUNTIF($A$2:$A$10,$A2)>1 doesn’t seem to be working.. i put the correct range but it is not highlighting any cells what might be an issue?

    1. Hello Pam,

      Make sure the Apply to range contains the correct range.

      Also, your spreadsheet locale may require a semicolon instead of comma. Try entering your formula into any cell and Google Sheets will suggest the symbol to use.

      If none of these help, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com), I'll look into it.
      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, reply to this comment and let me know what cells contain your conditional formatting rule.

  3. Hello.

    is it possible to highlight duplicate between multiple sheet ?

    for example i want to highlight duplicate sheet1 column A & sheet2 column A

  4. Hello,
    Is it possible to exclude blanks for duplicates highlights in the same row? In other words, I no need the blank cells to be highlighted.

      1. Hello Natalia,
        I used =And(countifs(A:A,A1) > 1,A1"") to exclude blanks from highlighting. Now it is working well.

  5. =countif($D2:$D, $D2)>1, this formula is not working for exact value, its highlighting all the similar in a cell. please help

  6. awesome, thank you!

  7. Hi! I've come across this great tutorial while searching for a way to higlight a row when there are at least two "x" in the row. Sadly, I'm already stuck. I've gone all the way to recreate the first example in order to test this out, but google says that the formula that's written here is wrong. In the formula "=COUNTIF($A$2:$A$10,$A2)>1" it doesn't let me put the ",$A2" after the "$A$10". In fact, it doesn't let me put a comma in there at all without showing an error. Does anybody know why that is the case?

    1. Hi Cane,

      You can make a copy of the spreadsheet that contains all formula examples I used by clicking the link in the related section at the end of the blog post.

      As for Google Sheets not understanding a comma in your formulas, this is dictated by your spreadsheet locale (File > Spreadsheet settings > General > Locale). When you start typing any formula, look at the Google Sheets hint about it. Perhaps, you need to use a semicolon instead.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)