How to highlight cell if value duplicate in same column for google spreadsheet?

Asked 20/2, 2014 at 6:3 Answered 4/9, 2019 at 19:19

google-sheets conditional-formatting

296

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column

can anyone please assist me for this query?

Covin answered 20/2, 2014 at 6:3 Comment(0)

527

Try this:

Select the whole column
Click Format
Click Conditional formatting
Click Add another rule (or edit the existing/default one)
Set Format cells if to: Custom formula is
Set value to: =countif(A:A,A1)>1 (or change A to your chosen column)
Set the formatting style.
Ensure the range applies to your column (e.g., A1:A100).
Click Done

Anything written in the A1:A100 cells will be checked, and if there is a duplicate (occurs more than once) then it'll be coloured.

For locales using comma (,) as a decimal separator, the argument separator is most likely a semi-colon (;). That is, try: =countif(A:A;A1)>1, instead.

For multiple columns, use countifs.

Bimah answered 28/4, 2014 at 16:12 Comment(17)

As of the new Google Sheets you can use the multi-column version COUNTIFS. Use like this if duplicates are a combination of more columns =COUNTIFS(A:A; A1; B:B; B1)>1 – Jeneejenei 27/12, 2014 at 14:41

You can also use =countif( B:B, B37 ) <= 1 as a custom formula in the Data -> Validation... menu if you want Data Validation features (such as a tool-tip saying what kind of values are allowed and rejecting input) – Anglo 9/6, 2015 at 16:52

the ending ; results in an "invalid formula" error for me. Just removing it did the trick. Also be careful: the cell you specify as 2nd argument of the countif should be the first cell of the range you select. – Arnaud 16/9, 2015 at 9:44

It works. But why? Assuming A1 is a criterion for the equation, doesn't this formula compare all cells in range to A1? – Akers 17/9, 2015 at 10:28

@mafonya, the custom formula is a generic formula that must be read as applying to the first cell. All references will be adapted for each and every cell in the range selected. So for instance, when checking cell B2 the formula will become internally =countif(B:B,B2)>1. This allows one to do some pretty advanced formatting when using absolute vs relative cell references. – Ward 18/11, 2015 at 1:10

As a side note, if you wanted to apply it to the whole column, you can use the formula =countif(A:A,A:A)>1 This means that it won't just apply to cell A1, but will to A4 and A8 if they are the same (duplicate). Now, both A4 and A8 will be formatted. – Lillian 3/3, 2016 at 0:50

The formula to format from the second instance of duplicated only: =COUNTIF($A$3:A3, A3) > 1 – Earphone 8/9, 2016 at 4:53

Is there a way to use the same idea but making it show even words that appear duplicated in a column? I mean at this point it only shows columns that are exactly the same. – Mctyre 4/12, 2016 at 18:12

@maartenmachiels I found that the order of all the rules messed it up. I had to pull this one to the top of the chain, so that the duplicate check applies first (and takes precedence over other rules). – Elvinaelvira 27/10, 2017 at 1:51

Make sure the formula has priority. After a while i found out all was correct, but it had to be above other formulas. – Swifter 6/12, 2017 at 11:50

I am having an issue. It says that the = signal in front of the formula is wrong, but without this it doesn't works – Embus 17/3, 2018 at 22:0

Its important to note that this formula works because it includes the whole column, however, if you need it applied to a range or part of a column you must set the reference with $ like so: =countif(A$3:A$20,A3)>1 – Billiebilling 12/12, 2018 at 19:14

I tried it, but it doesn't works for me. =countif(J:J,J10)>1 , did I wrote it wrong? – Translucent 14/8, 2019 at 4:6

@Translucent I believe you need J1 instead of J10 – Rapier 18/11, 2019 at 19:30

What's the difference between =countif(A:A;A1)>1 and =countif(A:A,A:A)>1 ? Please explain the formula. – Colver 9/12, 2019 at 0:37

@Lillian should post his comment... as an answer, FTW – Doley 9/12, 2019 at 16:50

Btw, how to read (A:A,A1) in a human English language? afaik, it's (range, criteria) .. so the range is A:A and criteria A1? not really intuitively clear. – Topdrawer 15/12, 2019 at 6:45

While zolley's answer is perfectly right for the question, here's a more general solution for any range, plus explanation:

    =COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1

Please note that in this example I will be using the range A1:C50. The first parameter ($A$1:$C$50) should be replaced with the range on which you would like to highlight duplicates!

to highlight duplicates:

Select the whole range on which the duplicate marking is wanted.
On the menu: Format > Conditional formatting...
Under Apply to range, select the range to which the rule should be applied.
In Format cells if, select Custom formula is on the dropdown.
In the textbox insert the given formula, adjusting the range to match step (3).

Why does it work?

COUNTIF(range, criterion), will compare every cell in range to the criterion, which is processed similarly to formulas. If no special operators are provided, it will compare every cell in the range with the given cell, and return the number of cells found to be matching the rule (in this case, the comparison). We are using a fixed range (with $ signs) so that we always view the full range.

The second block, INDIRECT(ADDRESS(ROW(), COLUMN(), 4)), will return current cell's content. If this was placed inside the cell, docs will have cried about circular dependency, but in this case, the formula is evaluated as if it was in the cell, without changing it.

ROW() and COLUMN() will return the row number and column number of the given cell respectively. If no parameter is provided, the current cell will be returned (this is 1-based, for example, B3 will return 3 for ROW(), and 2 for COLUMN()).

Then we use: ADDRESS(row, column, [absolute_relative_mode]) to translate the numeric row and column to a cell reference (like B3. Remember, while we are inside the cell's context, we don't know it's address OR content, and we need the content in order to compare with). The third parameter takes care for the formatting, and 4 returns the formatting INDIRECT() likes.

INDIRECT(), will take a cell reference and return its content. In this case, the current cell's content. Then back to the start, COUNTIF() will test every cell in the range against ours, and return the count.

The last step is making our formula return a boolean, by making it a logical expression: COUNTIF(...) > 1. The > 1 is used because we know there's at least one cell identical to ours. That's our cell, which is in the range, and thus will be compared to itself. So to indicate a duplicate, we need to find 2 or more cells matching ours.

Sources:

Docs Editors Help: COUNTIF()
Docs Editors Help: INDIRECT()
Docs Editors Help: ADDRESS()
Docs Editors Help: ROW()
Docs Editors Help: COLUMN()

Rapier answered 17/3, 2016 at 15:50 Comment(9)

Sadly this doesn't work for me. It fails to highlight anything at all despite there being plenty of duplicates. Not sure why, it sounds like a nice solution. – Use 6/8, 2016 at 17:31

This works for me, but also highlights blank cells. Is there a way to exclude blanks? – Decreasing 5/10, 2017 at 16:35

@Amanda - Yeah, a bit of creativity and it's possible. I'm AFK for the week, but catch me in a week.and a half and I'll be happy to help. The idea is to create an AND statement (using *) and another check, so along the lines of ((COUNTIF(...))*(NOT(ISBLANK(INDIRECT(...current cell...))))). That's the best I can do on mobile. :) – Rapier 5/10, 2017 at 16:54

Exactly this is the solution required. This just works like a HASHMAP to identify duplicates. – Dizzy 27/3, 2018 at 1:1

The "=countif(A:A,A1)>1" formula highlighted some fields that weren't duplicates; didn't have time to debug, but this formula doesn't have the same problem. – Baguio 29/1, 2019 at 16:17

@JeffAxelrod - that's because the first answer assumes the first cell (top left) in your range is A1, otherwise it'll have an offset that counts items outside your range. :) – Rapier 30/1, 2019 at 17:55

Works charming, thanks @Selfish! Saved me time. Note: only thing to change must be $A$1:$C$50 - accordingly to the columns under question. I like this more generic approach more than zolley's. – Canales 5/8, 2019 at 14:40

Thanks @boldnik! If I'm not mistaken, it IS $A$1:$C$50.. Am I missing something? – Rapier 6/8, 2019 at 15:11

@Rapier nono, I mean it's a generic solution where I had to change only those values for my own case. Thanks! – Canales 7/8, 2019 at 8:54

Answer of @zolley is right. Just adding a Gif and steps for the reference.

Goto menu Format > Conditional formatting..
Find Format cells if..
Add =countif(A:A,A1)>1 in field Custom formula is
- Note: Change the letter A with your own column.

Heterogeneity answered 22/3, 2018 at 13:19 Comment(2)

How can I filter them by color after highlighting the duplicates? – Outlast 7/11, 2018 at 5:4

@Faisal only with script or addon – Serous 2/9, 2019 at 16:19

From the "Text Contains" dropdown menu select "Custom formula is:", and write: "=countif(A:A, A1) > 1" (without the quotes)

I did exactly as zolley proposed, but there should be done small correction: use "Custom formula is" instead of "Text Contains". And then conditional rendering will work.

Screenshot from menu

Housekeeper answered 11/8, 2015 at 13:54 Comment(2)

I didn't downvote you (I just found this question today), but here is a comment on your answer, as you requested. I don't think the semicolon is necessary in the formula. Also, I still see a drop-down in the conditional formatting panel in google sheets. At least, I click on the selection option, and a long set of options appears, which I would generally call a drop-down menu. The option that initially appears in the drop-down is frequently Text Contains, so that is what a user would generally click on to access the dropdown menu. – Intercollegiate 14/4, 2016 at 14:32

Thanks Paul, for your comment. Maybe I misunderstood: I understood that "Text Contains" (or "Format cells if...") has a child drop-down menu "Custom formula is:". Moreover "Text Contains" should have a corresponding substring value, not "=countif(A:A,A1)>1;". In fact it's another approach. – Housekeeper 14/4, 2016 at 14:46

Highlight duplicates (in column C):

=COUNTIF(C:C, C1) > 1

Explanation: The C1 here doesn't refer to the first row in C. Because this formula is evaluated by a conditional format rule, instead, when the formula is checked to see if it applies, the C1 effectively refers to whichever row is currently being evaluated to see if the highlight should be applied. (So it's more like INDIRECT(C &ROW()), if that means anything to you!). Essentially, when evaluating a conditional format formula, anything which refers to row 1 is evaluated against the row that the formula is being run against. (And yes, if you use C2 then you asking the rule to check the status of the row immediately below the one currently being evaluated.)

So this says, count up occurences of whatever is in C1 (the current cell being evaluated) that are in the whole of column C and if there is more than 1 of them (i.e. the value has duplicates) then: apply the highlight (because the formula, overall, evaluates to TRUE).

Highlight the first duplicate only:

=AND(COUNTIF(C:C, C1) > 1, COUNTIF(C$1:C1, C1) = 1)

Explanation: This only highlights if both of the COUNTIFs are TRUE (they appear inside an AND()).

The first term to be evaluated (the COUNTIF(C:C, C1) > 1) is the exact same as in the first example; it's TRUE only if whatever is in C1 has a duplicate. (Remember that C1 effectively refers to the current row being checked to see if it should be highlighted).

The second term (COUNTIF(C$1:C1, C1) = 1) looks similar but it has three crucial differences:

It doesn't search the whole of column C (like the first one does: C:C) but instead it starts the search from the first row: C$1 (the $ forces it to look literally at row 1, not at whichever row is being evaluated).

And then it stops the search at the current row being evaluated C1.

Finally it says = 1.

So, it will only be TRUE if there are no duplicates above the row currently being evaluated (meaning it must be the first of the duplicates).

Combined with that first term (which will only be TRUE if this row has duplicates) this means only the first occurrence will be highlighted.

Highlight the second and onwards duplicates:

=AND(COUNTIF(C:C, C1) > 1, NOT(COUNTIF(C$1:C1, C1) = 1), COUNTIF(C1:C, C1) >= 1)

Explanation: The first expression is the same as always (TRUE if the currently evaluated row is a duplicate at all).

The second term is exactly the same as the last one except it's negated: It has a NOT() around it. So it ignores the first occurence.

Finally the third term picks up duplicates 2, 3 etc. COUNTIF(C1:C, C1) >= 1 starts the search range at the currently evaluated row (the C1 in the C1:C). Then it only evaluates to TRUE (apply highlight) if there is one or more duplicates below this one (and including this one): >= 1 (it must be >= not just > otherwise the last duplicate is ignored).

Episodic answered 4/9, 2019 at 19:19 Comment(0)

I tried all the options and none worked.

Only google app scripts helped me.

source : https://ctrlq.org/code/19649-find-duplicate-rows-in-google-sheets

At the top of your document

1.- go to tools > script editor

2.- set the name of your script

3.- paste this code :

function findDuplicates() {
  // List the columns you want to check by number (A = 1)
  var CHECK_COLUMNS = [1];

  // Get the active sheet and info about it
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  // Create the temporary working sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");

  // Copy the desired rows to the FindDupes sheet
  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }

  // Find duplicates in the FindDupes sheet and color them in the main sheet
  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
      }
    }
  }

  // Remove the FindDupes temporary sheet
  ss.deleteSheet(newSheet);

  // Alert the user with the results
  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found and colored red.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

4.- save and run

In less than 3 seconds, my duplicate row was colored. Just copy-past the script.

If you don't know about google apps scripts , this links could be help you:

https://zapier.com/learn/google-sheets/google-apps-script-tutorial/

https://developers.google.com/apps-script/overview

I hope this helps.

Gardant answered 15/8, 2017 at 17:7 Comment(1)

Can this code be modified to only highlight the duplicate fields in the column with red instead of the full row? – Pulvinus 11/1, 2019 at 20:28

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Highlight duplicates (in column C):

Highlight the first duplicate only:

Highlight the second and onwards duplicates:

Recommended topics

Hot tags