Highlighting 2 identical values in a Column.

[steveh8204]

Hi,

A simple one really but what is the best way to highlight similar values in a column of numbers?

Regards

 

[etaf]

similar values or identical values

conditional formatting - use a formula with a countif() > 1

 

[steveh8204]

similar values or identical values

conditional formatting - use a formula with a countif() > 1

This doesn't appear to work. I've just entered "countif()>1" in the "Use a formula to determine which cells to format", am I missing something?

 

[etaf]

you need the range and criteria

do you have a sample sheet?

 

[steveh8204]

I have selected the whole column first as the range. I will try and upload an image of it.

 

[steveh8204]

Below is an image of what I've done. The values circled should be in Bold as per conditional formatting.

https://drive.google.com/open?id=1bBdGaz00n1UUmGskgQZeTnLlWVY_n543

 

[etaf]

for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting


Highlight applicable range >>

B:B



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(B:B,B1)>1



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

 

[steveh8204]

for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting


Highlight applicable range >>

B:B



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(B:B,B1)>1



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

Hi, sorry as you can tell I'm hopeless at CF. Thanks for your help.

I've had some joy but one has come up even though it's a unique value which is strange.

https://drive.google.com/open?id=129EeTs_BHmkddN5ElHsMDpB-ceGk7X9L

 

[etaf]

the formula looks wrong
see the cell ref - should be B1

link the spreadsheet rather than an image - BUT it is a public forum

 

[steveh8204]

the formula looks wrong
see the cell ref - should be B1

link the spreadsheet rather than an image - BUT it is a public forum

Thats sorted it, I selected that column and it used that ref strangely.

I would share file but it's got a lot of sensitive company info so I've got to be careful with it.

Thanks for your help.