Excel: Conditional formatting if cell is not equal to one of multiple values

Question

Using Excel, I would like to conditionally highlight a cell, if the value of the cell itself is not one of several values.

The cell should be contain of the following percentages – 0%, 6%, 12% - or else it should be conditionally formatted to a red background.

Based on this answer, I would've thought that the below formula would work – but for some reason it doesn't.

=NOT(OR(D3=0, D3=0.06, D3=0.12))

D3 is the cell I want to enter a value into, and it's the same cell I wish to be conditionally formatted.

I create a conditional formatting rule, via New Rule > Style: Classic > Use a formula to determine which cells to format. I then paste the above formula into the formula/range selection field.

I get the error There's a problem with this formula:

Solution

Select required Data Range and write this Formula as New Rule and apply an appropriate color.

=NOT(OR(G142=0%, G142=6%, G142=12%))

Note, adjust cell reference in the Formula as needed.

Comments

[cybernetic.nomad]

What version of Excel are you using?

[P A N]

@cybernetic.nomad Excel 16.18 for Mac (from Office 365).

[cybernetic.nomad]

The formula does exactly what you describe in Excel 2013 on a PC. (complete shot in the dark: are you sure , and the delimiters you should be using? what happens if you use ; instead?)

[P A N]

@cybernetic.nomad Thanks! It works if I change the delimiter to ;. It might be Mac thing, or a localization issue. This works: =NOT(OR(D3=0; D3=0.06; D3=0.12)).

[P A N]

Using ; as delimiter worked for me: =NOT(OR(D3=0; D3=0.06; D3=0.12)), but it didn't work with , as delimiter in my non-US version of Excel for Mac.

[Rajesh Sinha]

@Winterflags,, it's because of regional setting difference,, which is quite obvious ☺ If you feel now you can accept it as answer.