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

Question

I am looking for formula for google spreadsheet  highlight cell if value duplicate in same column  can anyone please assist me for this query

User · Answer

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

User · Answer

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  gt 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  gt 1  instead   For multiple columns  use countifs

User · Answer

Highlight duplicates  in column C     COUNTIF C C  C1   gt  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  amp 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   gt  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   gt  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   gt  1  NOT COUNTIF C 1 C1  C1    1   COUNTIF C1 C  C1   gt   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   gt   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    gt   1  it must be  gt   not just  gt  otherwise the last duplicate is ignored

User · Answer

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  lt  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  lt  data length - 1  i          for  j   i 1  j  lt  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

User · Answer

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     gt  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       gt  1  The  gt  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

User · Answer

Answer of  zolley is right  Just adding a Gif and steps for the reference    Goto menu Format  gt  Conditional formatting   Find Format cells if   Add  countif A A A1  gt 1 in field Custom formula is   Note  Change the letter A with your own column

[google-sheets] How to highlight cell if value duplicate in same column for google spreadsheet?

Examples related to google-sheets

Examples related to gs-conditional-formatting