Google Sheets - Highlight Duplicate Data in a Column or Row

Learn how to use conditional formatting in Google Sheets to highlight cells with duplicate data.

To do a row instead of a column, use 1:1 to reference all of row 1, just like you use A:A to reference all of column A.

If you live in a country that uses a comma as a decimal separator, use semi-colons instead of commas.

Learn more from Prolific Oaktree:

#googlesheets #spreadsheets #duplicates

[jedimawk]

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.

[TheFlyter]

I saw multiple videos with this formula and as someone who had no experience in google sheets was going crazy on why it wasnt working. You were the only one who mentioned to use ; instead of, if my country uses, for decimals. So thank you so much.

[jake.presents]

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

[joshuaheim]

Thank you for the video!


How would I use conditional formatting in this situation?


I have a list of keywords in my keyword tab and a list of web copy in one cell in my web copy tab. I want to highlight keywords in my keyword list that are found in my web copy cell.

[DavidMartinez-ydwq]

Hi. Quick question. I know how to highlight cells that are duplicated. But i want to learn how to highlight any cell that repeats to a separate cell. In my case i do a list with schedule with a bunch of names, so if i want to type a name and see highlighted all the cells where that name is how can i do it? it is like the "search function" but setting it up in a cell on the side of the schedule. Can you help?

[SamVekemans]

Sweet! That's exactly what I was looking for to help me with my spreadsheet. Thanks for sharing!

[giovannilignarolo]

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?

[andyballard]

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.

[MatteoOneself]

short video, straight to the point, clear explanation, high quality video... thank you

[OZ-_Bonnie]

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.

[bjac]

Thank you! I'm using this to input auto part numbers in an inventory sheet. SO fun...

[LeaRebecca]

Your voice is so soothing, I'm seriously contemplating playing your instruction videos for my toddler to fall asleep to

[haroldbubuli]

After over 5 years later and still works. Thank you!

[KrasHopper]

It returns 'invalid formula' in my sheet, so I used a semi-colon instead of a regular colon between the two parameters. COUNTIF(A:A;A1)>1

[Alex_]

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

[Sahrene]

I have two columns, one for date (L) and one for time (M). Im using this to book tours and I want to find a way to highlight the rows that has the same value from those two columns. How do I do that?

[aldolammel]

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

[stretchmonroe]

This just saved me an hour!! Thank YOU

[learntoearn]

Exactly what I search for. Thank you. Love this kind of short video.

[Good_Things_]

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.