Highlight Unmentioned Numbers

Occasional Visitor

Hi, I have been creating a document to keep track of my railway haulage. What I would like to do is highlight a Number in a cell that has not been mentioned before, however next time it is mentioned, It will not highlight. The only things I can find are highlight duplicate numbers and highlight numbers mentioned once, and I want neither of those!

all help appreciated,


2 Replies

@Oli_Kirrage There are 2 scenarios in the attached workbook:

1. You want if a number appears for the first time, it is highlighted but then if it reappears, both the cell of 1st occurrence and nth occurrence do not have any highlight: use the conditional formatting rule in Column A (use Countif(range,A1)=1 where the range should be an absolute range and cover as many cells as you could expect for your data range)

2. You want the highlight on the 1st occurrence remains but any nth occurrence does not any highlight: use the conditional formatting rule in column B (use column C as a helper column which could be hidden where the range for each cell in column C is a running range)

Note that cell A15 and B15 are test cases of a number already mentioned previously to see how the conditional formatting rules in each scenario differ.



@Oli_Kirrage Just as a variant to the suggestions already made by @hynguyen


Column E produces the same results as Column A, using the standard "Unique Values" rule (although you mentioned that this is not what you want)

Column F produces the same results as Column B, but without the need of Column C.