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

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

can anyone please assist me for this query?

Solution 1:

Try this:

1. Select the whole column
2. Click Format
3. Click Conditional formatting
4. Click Add another rule (or edit the existing/default one)
5. Set Format cells if to: Custom formula is
6. Set value to: =countif(A:A,A1)>1 (or change A to your chosen column)
7. Set the formatting style.
8. Ensure the range applies to your column (e.g., A1:A100).
9. 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.

Solution 2:

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:

1. Select the whole range on which the duplicate marking is wanted.
2. On the menu: Format > Conditional formatting...
3. Under Apply to range, select the range to which the rule should be applied.
4. In Format cells if, select Custom formula is on the dropdown.
5. 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()

Solution 3:

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

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