Articles Blog

Google Sheets – Highlight Duplicate Data in a Column or Row

Google Sheets – Highlight Duplicate Data in a Column or Row


If you’re using Google Sheets and you have
a column of values and you want to highlight just the duplicate values, there’s a pretty
easy way to do that. Here I have some example data. It’s all in one column. If you’ll notice, it starts in A1. That’s important to the formula. If it didn’t start in A1, you’d have to adjust
it a bit. We’re going to use Conditional Formatting. Go to Format and then Conditional Formatting. It’ll pop-up this dialog box on the right-hand
side. If you highlighted the range before-hand,
this will be filled in for you. If not, type in your range. We’re going to format the cells with a custom
formula. We have to tell it how to do it. So, the formula’s going to be, make sure you
start it with an=sign. We’re going to say Count if, we’ll give it
a range, I meant to do this in CAPS, if you give it A:A, that makes it look at the entire
column. Just do that and you won’t have to adjust
it. It column A and then we’re going to start
it in A1. If you see A1 more than once, highlight it. This works because this evaluates the column
every time it moves down to a new cell. A1 is going to change into A2 when this formula
is evaluated in A2. That’s why you just have A1 in there. It’ll change to A2, A3, etc. When it goes to A3, that’s when it will say
it’s=to A5. A3 happened more than once so it gets highlighted. Just click Done and there you go. You’ve highlighted every occurrence of duplicates
in column A.

81 thoughts on “Google Sheets – Highlight Duplicate Data in a Column or Row”

  1. How do we highlight duplicates in such a way that one original value does not get highlighted?
    Also how can you filter by color in sheets without an ADD ON?
    Thanks.

  2. Steps:
    #1: select range of cells (for example A1:A7)
    #2: menu: Format → Conditional Formatting
    #3: Format cells if: [Custom formula is]
    #4: =COUNTIF(A:A, A1)>1

  3. I am having an issue. It says that the = signal in front of the formula is wrong, but without this it doesn't works

  4. My sheet is a Form response sheet. I need it ti highlight after 4 choices made, but it must be attached to the person answering. How can I do this?

  5. Help me please. how do I change the formula if I have 3 sheets. and the new formula should work for all sheets. for example, I entered data on the first sheet and the same on the third sheet. Sry for my english.

  6. For those curious, if you want to do a larger range (multiple rows and columns) formatting is:
    =countif($A:$BX,A1)>1

    This would search all columns/rows from A to BX and highlight dupes, starts with a search at A1.

  7. That's great but how do I then sort by color or mark ONLY the 1st duplicate cell, and leave the original unmarked?

  8. =COUNTIF(A:A; A1)>1 for Columns "A"
    =COUNTIF(B:B; B1)>1 for Columns "B"
    =COUNTIF(C:C; C1)>1 for Columns "C"
    =COUNTIF(D:D; D1)>1 for Columns "D"
    =COUNTIF(E:E; E1)>1 for Columns "E"

  9. Please give solution, I want to Highlight Data in Column "B" based on Column "A" (I mean all the words in A1 should be highlighted in B2

    https://docs.google.com/spreadsheets/d/1KXj7dsIZXhWfL26rSgU8yFY1QGQThY3MPJO23dq9ulY/edit?usp=sharing

  10. Thanks so much, I'm trying this with a slightly more complex sheet hope you can help. I only want duplicates highlighted when found in a certain range of rows, but my rows are highlighting even when it finds duplicates outside that range

  11. Google doesn't allow me to put the = sign (it says "Formula is not valid" or something like that) but if I put the formula without it, it doesn't work

  12. Thank you for sharing. It's really help me out.. 🙂

  13. so how would you write it if you want to look for duplicate words in lists? That is, I have a list of names in each cell. I want to know if the same name or names appears in another cell.

  14. can someone teach me how to check duplicates from 1 sheet to another sheet

    https://docs.google.com/spreadsheets/d/1zk0unlUVH1oirBzs02ZAU-7CHJDk29OIbKC3Oko-vcU/edit#gid=0

  15. Sir, i've been trying to apply this formula the exact same way you did, and it returns as 'invalid parse'. How can i proceed? :/

  16. If cell A1 is a header or part of a frozen row, you need to change "A1" to the first cell below the frozen row, like "A2" or whatever it may be.

  17. You have to add $ to the function , i.e. A$2:A, C$4:C, if it's meant to start from a specific row, otherwise the function won't work properly!!!
    I.e.: =countif(C$4:C$1298,C4)>1

    It's because for each next evaluated row, it will evaluate a row less from the top, i.e. in row 3, the evaluation will start in row 3 instead of row 2, if the function is A2:A and not A$2:A.

  18. If the formula is not highlighting cells that you know are duplicates then its possible that you have trailing or leading spaces on one, if so the formula will not pick these up as duplicates.

  19. Thank you. I read many extremely complicated answers to this question and this is exactly what I needed to do. I wish I had seen yours first. Thanks also to the comments explaining multiple columns, which I may need later.

  20. That voice!!! You should turn to relaxing videos!! I even forgot why was I looking for this kind of information hahaha thanks! 🙏🏽

  21. Thanks sooo much! Very useful.
    Just one question. Is there any way we can add the number to duplicates… So, there are not duplicates anymore?
    So, for example… we have 5 Birds there…
    So, it would do this:
    Bird (leave the first as is)
    1Bird
    2Bird
    3Bird
    4Bird

    Thanks a lot :)))

  22. it just doesnt work anymore…

    =COUNTIF(A:A, A1)>1

    it has a comma, and google says the code is wrong. I put a semicolon instead of a comma ; and it allows me, but, it only highlight the duplicates within the SAME COLUMN,
    If i put the same input in another column, it won't highlight the duplicate
    Ok, so I did another condition for the rows and now it works BUT it doesn't highlight the last, only the penultimate and I'M SCREAMING CAUSE I CANT SOLVE IT.

    https://imgur.com/a/ol5aDYN

  23. =COUNTIF(A:A, A1)>1
    —– notwork anymore

    "," REPLACE with this code " ; "

    =COUNTIF(A:A;A1)>1 —- it works

  24. Mine is only highlighting the first instance of the duplicate, not both. Any ideas on how to make it highlight both? My formula is =countif(D7:D53,D7)>1

  25. Hi, I am not sure if you keep track of comments, but I would really like you do because I have a question, is this formula able to cover multiple sheets, for example, I would like to check A:A:A1 in sheet number 1, to same in sheet number 2, and mark those in number 2 that are duplicate?
    I am not sure if it makes sense, please let me know when you can,

    Max

  26. Thanks for the video, it's been very usefull! What should we do if the range of cells is in a different sheet? I've tried to copy and paste the formula from a cell to the conditional formatting formula field, but it doesn't work. Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *