Highlighting a duplication

[casualbrowser]

Hi

I was wondering if someone can tell me this is doable... I am looking for a kind of formatting that highlights a duplication.

For example, if i was to type 'Lemons' in cell C3 next, it would highlight the cell (to indicate a duplication) and ALSO the cell that has the original entry in..... this would also need to work in multiple instances.... so in the future, there could have Table Z... and lemons might have 20 entries, all of which would be highlighted.

Any help greatly appreciated.

 

[etaf]

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available

you could use a countif()
but your example shows table C in column C
you then say

this would also need to work in multiple instances.... so in the future, there could have Table Z. and lemons might have 20 entries

so do you just want to highlight any duplicate

so not sure at what point to highlight

But if you select the cells likely to be filled - then it will highlight as soon as there is a duplicate
BUT will highlight all duplicates not just the cell being entered
Not sure how to do that , may need VBA

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A3:Z100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countif($A$3:$Z$100, A3)>1

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book16
	A	B	C	D	E	F	G
1
2
3	oranges	strawberries	lemons
4	lemons	sausages
5	custard	soda
6	water
7
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:K17	Expression	=COUNTIF($A$3:$Z$100,A3)>1	text	NO