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.

Thanks so much for the concise video and for explaining why A1 was necessary. What does ">1" do in the formula?

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.

THANK YOU

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

Your voice..godly!

Thank you. This helped me tremendously. Any way to make each instance of duplicate data a different color?

Thanks my man

Thank you

This just saved me an hour!! Thank YOU

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

excellent, easy to follow, thanks!

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?

why the hell is this not a standard feature in sheets?

Thank you, it's very useful.

Mine is highlighting the cell below where the duplicate values are? could someone help?

If I wish to highlight duplicates found in more than one columns, how do I do so?

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.

Thank you very much!!

Thanks was very helpful

I have column A and need to compare it to B,C and D. Any help please.

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.

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

awesome thx much, appreciate it.

=COUNTIF(A:A, A1)>1 or =COUNTIF(A:A; A1)>1 gives me invalid formula

how do i copy only the highlighted cells. Why is this so hard lol damn

Thanks for the great tutorial!

ooow that voice is so warm

Thanks!!

=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"

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

this saved me! thank you

Thank you! This was helpful!

Thanks, dude. It was so important for my work here. Thanks again!

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

Hello- this video was very useful- Thank you.

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

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

Thank you so much!

Invalid Formula …that's odd

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.

easy peasy thank you

If you get invalid formula use thisone instead

=AND(NOT(ISBLANK(A1)); COUNTIF($A$1:$A; "=" & A1) > 1)

tHANKS

Thank you! It was very helpful!

Amazing! Nice, clean and simple solution. Thanks a lot for sharing 🙂

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

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? :/

Great tip, thank you very much! First guide ive been able to find on this

Thank you!!!! This is fantastic!

You should've name your video to "ASMR Google Sheet Tutorial"

Great tutorial by the way. Thanks 🙂

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.

Thank you for this! 🙂

You have a very calm and beautiful voice… Thanks for the video

Thank you! Very helpful

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.

Thanks a lot for creating this.

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.

Very helpful, thank you.

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.

Thanks, this made it so easy for me!

Thank you!! This worked perfectly!

=COUNTIF in cap not working any more. You need to type lowercase: =countif

thank you!!!

Thank you! I wasted so much time trying to figure this out before watching your video!

Didnt work at all!

Thanks, that is so much more complicated that in excel.

You are a true hero sir.

Pretty cool man! 🙂

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

Thanks! That's what i needed! 🙂

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 :)))

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

=COUNTIF(A:A, A1)>1

—– notwork anymore

"," REPLACE with this code " ; "

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

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

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

THANK YOU!

=COUNTIF(A:A, A1)>1 This didn't work for me. Instead this works =COUNTIF(A:A; A1)>1

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?

Thank you sir! Simple and was exactly what I needed!

Really Work

Can u explain the logic.?