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.

enter image description here

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

enter image description here

  • What version of Excel are you using? – cybernetic.nomad Oct 18 '18 at 20:12
  • @cybernetic.nomad Excel 16.18 for Mac (from Office 365). – Winterflags Oct 18 '18 at 20:13
  • 2
    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?) – cybernetic.nomad Oct 18 '18 at 20:17
  • 1
    @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)). – Winterflags Oct 18 '18 at 20:20

enter image description here

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.

  • 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. – Winterflags Oct 19 '18 at 11:26
  • @Winterflags,, it's because of regional setting difference,, which is quite obvious ☺ If you feel now you can accept it as answer. – Rajesh S Oct 20 '18 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.