How to Mark Duplicates in Google Sheets 3+ Simple Ways (2023)

Watch Video: Highlight Duplicates in Google Sheets

When working with data in Google Sheets, sooner or later you will encounter the problem of duplicate data. This can completely ruin your spreadsheets. Wouldn't it be nice if there was a way to find all duplicates at once? This guide will teach you how to highlight duplicates in Google Sheets easily through a series of simple methods. Read to master them.

How to Highlight Duplicates in Google Sheets: Cells in a Column

The most common situation where you will need Google Sheets to highlight duplicates is when you have a set of data in a column and you want to quickly highlight duplicates.

For example, suppose you have a dataset like the one below, where you want to highlight all the repeated names in column A.

Below are the steps to highlight duplicates in a column:

1. Select dataset of names (excluding headers)
2. Click on the Format option in the menu
3. From the options that appear, click Conditional Formatting. This will open the conditional formatting rules panel on the right side
4. Click on 'Add another rule' option
5. Make sure the range (where we need to highlight the duplicates) is correct. If not, you can change it in the 'Apply to range' section.
6. Click on the 'Format Cells If' drop-down menu and then click on the 'Custom Formula Is' option
7. In the field below, enter the following formula:

   =COUNT IF($A$2:$A$10,A2)>1

   

8. In the 'Format Style' options, specify the format in which you want the duplicated cells to be highlighted. By default it will use the color green, but you can specify other colors and styles such as bold or italic.
9. Click Done

The above steps would highlight all cells with duplicate names in the specified color.

Find duplicates in Google Sheets by highlighting them in multiple columns

In the example above, we had all the names in a single column.

But what if the names are in multiple columns to perform conditional formatting for duplicates in Google Sheets (as shown below).

You can even use conditional formatting to highlight duplicate names (which would be a name that appears more than once in all three columns combined).

Below are the steps to highlight duplicates in multiple columns:

1. Select dataset of names (excluding headers)
2. Click on the Format option in the menu
3. From the options that appear, click Conditional Formatting.
4. Click on 'Add another rule' option
5. Make sure the range (where we need to highlight the duplicates) is correct. If not, you can change it in the 'Apply to range' section.
6. Click on the 'Format Cells If' drop-down menu and then click on the 'Custom Formula Is' option
7. In the field below, enter the following formula:

   =CONT.SE($A$2:$C$10,A2)>1

   

8. In the 'Format Style' options, specify the format in which you want the duplicated cells to be highlighted. By default it will use the color green, but you can specify other colors and styles such as bold or italic.
9. Click Done

The steps above would highlight the cell if the name appears more than once in the three selected columns combined.

How does this work?

This also worked as the last one forConditional formatting of Google Sheets duplicates.

NoCONTAR.SI formula, we cover all the cells in the three columns. Therefore, each cell in the range is checked using the specified formula and returns TRUE or FALSE. The COUNTIF formula is something like the duplicate formula for Google Sheets.

If there is a repeated name in any of the columns, it will be highlighted in the specified format.

Again, notice that I used the range $A$2:$C$10 (where there is a dollar sign before the column alphabet and row number). This is really crucial as it ensures that the range stays the same while conditional formatting checks the count of the name in a cell.

How to Highlight Duplicate Values ​​in Google Sheets (Find Duplicate Rows)

This one is a little tricky.

Suppose you have a dataset like the one below and you want Google Sheets to find all duplicates using highlighting.

In this case, a record will be duplicated if it has exactly the same value in every cell in the row (like rows 2 and 7 in the example above).

The reason this is a little tricky is that you don't need to check individual cells right now. You need to scan the entire row and highlight only rows where all cells are repeated.

But don't worry, it's not too hard to do.

Below are the steps to highlight duplicate rows using conditional formatting:

1. Select dataset (excluding headers)
2. Click on the Format option in the menu
3. From the options that appear, click Conditional Formatting.
4. Click on 'Add another rule' option
5. Click on the 'Format Cells If' drop-down menu and then click on the 'Custom Formula Is' option
6. In the field below, enter the following formula:

   =CONT.SE(FÓRMULA DA MATRIZ($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

   

7. In the 'Format Style' options, specify the format in which you want the duplicated cells to be highlighted. By default it will use the color green, but you can specify other colors and styles such as bold or italic.
8. Click Done

The above steps would highlight all repeating records in the dataset (as shown below).

How does this work?

This works the same as our first example (where we simply highlighted cells in a column that had duplicates).

But since there is an entire line that we need to compare with all other lines, we combine the contents of all the lines and create a single string for each line.

The next part of the formula creates a string array where all cell contents are combined into one line (done by concatenation using the ampersand sign).

ARRAY FORMULA ($A$2:$A$10 and $B$2:$B$10 and $C$2:$C$10)

This array is used in the Countif formula and the condition used is again a concatenated string that has all the values ​​in one line. This is done using the following criteria:

$A2&$B2&$C2

This has now been converted to a simple column type construct where theCONTAR.SIThe function checks how many times this combined string is repeated in the array of strings we created.

This will eventually highlight all duplicate records.

Show duplicates in Google Sheets with added criteria

Google Sheets can also use most aggregate criteria you can think of to highlight duplicate data. For example, you can configure the system to only highlight duplicates for specific values.

The syntax would have to use the "*" (and) operator for COUNTIF to use both criteria. The syntax would be something like this:

=(CONT.IF(Interval,Criteria)>1) * (New Condition) )

We can use data from our past employees to show how this would work. Suppose we discover that there were actually two Henrys in marketing, so we update the ID number of one of them.

We still want to highlight duplicate employees, so we can add a second condition to make it true like this:

1. Navigate conditional formatting rules as in previous examples
2. Enter the first part of the formula with the range and format we should already be familiar with =(countif(Range,Criteria)>1) so our example is:

=(Counter ($A$2:$C$10,$A2)>1)

This will show you if there are duplicates in row A, but it won't delete the second Henry's now unmatched ID, so you'll need those additional steps

1. Use the command "*" (y) after the first formula
2. Add the second condition to the syntax (COUNTIF(Range,Criterion)>1), but make sure you are addressing another line, line C in this case. The whole formula should look like this:

=(Count if($A$2:$C$10,$A2)>1)*(Count if($A$2:$C$10,$C2)>1)

Of course, with this exact example, you could have used the second half of the formula to get the same result, but this is just a sample of how adding a second condition can be used. Depending on your needs, you can also:

• Use a different criteria after the criteria, i.e. >0 or <5, etc.
• Add a third criterion
• Many other conditions "*"

Editing or deleting conditional format rules

When finding all duplicates by highlighting, you might want to keep some of the duplicates but no longer want them to be highlighted. The solution is simple, just edit or delete the conditional formatting rule. To do it:

1. Highlight the cells to which you applied the conditional formatting rule
2. navigate toFormat > Conditional Formatting
3. Click the trash can symbol next to the existing rule

Possible issues with simple fixes when highlighting duplicate cells in Google Sheets

Sometimes it may happen that you follow all the steps above and use the same formulas, but Google Sheets still doesn't highlight the duplicates. If they're not highlighted, how should they be?remove duplicates?

Here are some possible reasons you can check:

Extra spaces in cells

is there anyadditional spaces(leading or trailing space characters) in the text in one cell and not the other?

Since we are looking for an exact match for two or more cells to be considered as duplicates, if there are extra space characters in the cells, it would cause a mismatch.

So even if you see that there is a duplicate, it might not be highlighted.

To get rid of this, you can use the TRIM function (and the CLEAN function) to get rid of all the extra space characters.

wrong reference

There are three different types of references in Google Sheets.

1. absolute references(example – $A$1)
2. Relative references (example – A1)
3. Mixed references (example: $A1 or A$1)

If a formula requires one reference type and you end up using the others, you likely have a problem.

So, check references to ensure that Google Sheets highlights duplicates as expected.

Some tips when highlighting duplicates in Google Sheets

• Make sure you don't have other conditional formatting rules for your desired cells that might give false results
• Make sure your searches are not missing spaces
• Don't select headings when using ARRAYFORMULA to highlight duplicates in Google Sheets

How to find duplicates in Google Sheets using UNIQUE

This formula is much simpler to use when working with smaller data sets and you only need to see how many differentunique pieces of datathere in your spreadsheet. although it does not provideGoogle Sheets Conditional Formattingduplicate

The syntax is simply:

=UNIQUE(Interval)

So, in our example above, all we would need to do to find all unique values ​​in is:

1. Select an empty cell (D2 in our case) and press enter
2. Type =UNIQUE and click and drag over the desired cellssearch unique data
   
3. Press enter and a list of eachunique valuewill appear in the column
   

FAQ about highlighting cells with conditional formatting

Can Google Sheets highlight duplicates?

Yes, you can highlight duplicates. You must use a combination of the COUNTIF formula and conditional formatting. You can set conditional formatting to highlight only one cell if the COUNTIF formula returns a TRUE value.

What is the formula for highlighting duplicates in Google Sheets?

Here's how to identify duplicates in Google Sheets.You just need to use the COUNTIF formula inside the conditional formatting menu. The COUNTIF formula uses the following syntax.

=COUNTIF(range, criteria)

Therefore, you must enter the range you want to highlight and>1like himcriterion.As in, if more than one instance exists, highlight the cell. Follow the guide above for a deeper look.

How do I group duplicates in Google Sheets?

To group duplicates together, you can use the SORT function or use the sort shortcut on the toolbar. Here it ishow to find duplicates in Google Sheets with Sort menu:

1. Select the column you want to group duplicates
2. navigate toData > Classification worksheet

If you used our method above to conditionally format duplicates. You can alsosort by colorto avoid sorting non-duplicate cells.

How do I see duplicates in Google Sheets? / Can Google Sheets find duplicates?

You can see duplicates using conditional format in the format menu and a COUNTIF formula. Then choose the color in which you want to highlight the duplicates.

How do I highlight the same cell with duplicates in Google Sheets?

Unfortunately, you cannot find duplicates in a single cell.

How do I compare two columns in Google Sheets to find duplicates?

You can use the CONT.IF formula as a custom formula in the conditional formatting menu to find duplicates andcompare two columns. Just include the cell references in the formula and use>1like himcriterion.

How do you highlight duplicate values ​​except the first instance in Google Sheets?

Follow the method above to use highlighted cells, except use >2 as the criteria. This means that highlighting will only happen if there are two or more than two instances of a value, i.e. ignoring the first match.

How do I compare different Google Sheets for duplicates?

You can do this with complicated formulas or scripts, but the easiest way would be to add the required worksheet into a single total worksheet and thencompare the two worksheets. To do this:

1. Right-click on the worksheet name at the bottom of the page
2. hover overcopy for
3. give a clickexisting worksheetand select the desired worksheet
4. You can use the worksheet reference in the COUNTIF formula, for example if you want to use a range of cells from Sheet1 you can write=Hoja1!

double your learning

Here are ways to highlight duplicates in Google Sheets using conditional formatting.

Hope you found this tutorial helpful!

Other Google Sheets tutorials you might like:

• Apply conditional formatting based on another cell value in Google Sheets
• How to count cells with specific text in Google Sheets
• How to Search Google Sheets
• How to use the SINGLE function
• How to Highlight Highest or Lowest Value in Google Sheets