Highlight cells where the same number is repeated 3 or more times.

[dmrcs]

Hello all,

I am aware there could already be some literature on this one; however, I could not really find a solution on some of the threads I found...

So I have a spreadsheet and I would like cells within a column to highlight if the same value is repeated 3 or more times; I believe this to be achieved via Conditional Formatting and I have tried this, and this.

None of it works, the best I have managed is to get the first cell highlighted... I would like all cells in column A that have the same value repeated 3 times or more to be highlighted.

Here's the formula I am using:

=COUNTIF($A$3:$A$500, $A3) >= 3

What I am doing wrong? I am using Excel 365.

Many thanks in advance!

 

[Fluff]

Can you post some sample data that shows the problem, as that formula should work.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[dmrcs]

Hello @Fluff , thank you. I can't really do XL2BB on this particular PC as it's a work laptop and there are security implications.

Appreciate it not might the most helpful thing but I can share a screenshot of what I get using that formula:

There you can see there should be more cells highlighted as they are triplicated (or more); however, just the top one is changing format.

If this isn't good enough, I will install XL2BB on my personal laptop but I am under the impression the formula will then work there... I am totally lost as in why this isn't working here.

 

[Fluff]

Thanks for that, the reason it isn't working is the applies to range is a single cell.
Delete that rule, then select A2:A500 & create a new rule using the same formula.

 

[Peter_SSs]

.. or just select A2 and go back in to that CF dialog via CF - Manage rules
and change the Applies to: field to $A$2:$A$?? whatever range you want - Apply - OK

 

[dmrcs]

@Peter_SSs, @Fluff

I guess I should have gone to Specsavers, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

I guess I should have gone to Specsavers, thank you!

You're welcome.