We need to enter lots of data in a excel sheet. Later we want to color code that data.

I will be the one preparing this excel sheet and sharing with team. What I want is the moment I put a number and if it is not falling in range, cell will turn red else green i.e. I don't want to go through cells again and color code them.

I tried:

I found if formula =if(200<C3<500,[value_if_true],[value_if_false])

Here I have two problems,

  1. C3 should be direct input means I don't want to apply this formula on someother column.
  2. I don't know the formula to color a cell if condition is not true/false.

You can do this with Conditional Formatting:

enter image description here

enter image description here

enter image description here

Formulas: =NOT(AND(200<C3,C3<500)) and =AND(200<C3,C3<500) - assuming you wanted to colour a number OVER 200 and LESS THAN 500. Bear in mind, 200 and 500 are NOT inclusive with this formula.

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.