Conditional Formatting: everything but "X", "Y", or "Z"

[reneev]

We are trying to capture errors in rates. Rates can either be 0, 36.12 or 40. How do I conditional format a cell if it's anything other than one of those numbers? And can it be expanded for additional numbers if I have more than three rates

 

[tyija1995]

I suppose making a custom conditional formatting (CF) rule would be best here -> Home -> CF -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true
=NOT(OR($A$1=0,$A$1=36.12,$A$1=40)) -> Format: Choose whatever you would like cells to show as if it is not one of these numbers.
Apply it to the cells that make sense for your situation (and also change $A$1 to whatever your range is to make sense)

 

[reneev]

That worked!

Now, for the second part of my question, which refers to a different column and different rates. Seems there are WAY too many rates to try to capture in this type of formula so now the idea is to highlight anything that is NOT between 3.88 and 213.69 except we don't want zero's to highlight. I was able to get part of this to work using the the "Value not between" conditional formatting until I realized that the zero's were highlighted.

 

[Joe4]

Try something like:

[COLOR=#333333]=AND($A1<>0,OR($A1<3.88,A1>213.69))[/COLOR]

 

[reneev]

YES!!! THANK YOU SO MUCH!

P.S. It would be great to find a class or tutorial that teaches the meaning behind formula code. All I've ever seen is the instructor telling you to insert this and that code but not WHY. I've figured out some of it over the years but if I could find something that goes into greater detail about when/why to use "and" or "or", what the comma means, what the parenthesis mean, etc... I could figure these things out on my own.

 

[Joe4]

On the "Formulas" menu, there is an "Insert Function" icon. If you use this to enter your formulas, it tells you what each "argument" (the values separated by the commas) represent.
Just pick your function, and it will help you fill it out. And most have a "Help on this function" link, which takes you to documentation with examples.

Also, doing Google searches on specific functions also yields a lot of helpful information. For example, do a Google search on "excel and function", and see what pops up!

And of course, feel free to ask us anything that you cannot figure out on your own, espeically if you would like us to explain some formula that we are using.

 

[tyija1995]

On the "Formulas" menu, there is an "Insert Function" icon. If you use this to enter your formulas, it tells you what each "argument" (the values separated by the commas) represent.
Just pick your function, and it will help you fill it out. And most have a "Help on this function" link, which takes you to documentation with examples.

Also, doing Google searches on specific functions also yields a lot of helpful information. For example, do a Google search on "excel and function", and see what pops up!

And of course, feel free to ask us anything that you cannot figure out on your own, espeically if you would like us to explain some formula that we are using.

In addition to Joe's comment if you press Ctrl+A you will see the function arguments! A nice little shortcut
Ctrl+Shift+A will open up the parameters for the function too!

To execute the Ctrl+A method, say you were to use a SUMIF function then:
=SUMIF(***Now use Ctrl+A***
So after the opening parenthesis you apply shortcut and it will prompt the arguments! :D

EDIT: You can also hit Ctrl+A before you use an opening parenthesis, so =SUMIF **Ctrl+A** will also work.

 

[DSCfromCFA]

G'day Reneev,

An additional protection is to use Data Validation Lists at the data entry end in both cases, which makes it trivially easy to either change the rate or add/delete rates later, without needing to go in to alter CF formulae. Note that Data Validation won't identify any currently incorrect values.

shane

 

[ClaireS]

Yes it will: if you add Data Validation to existing data, you can Circle Invalid Data to highlight records that do not meet the condition.

 

[ClaireS]

I agree with Shane. I would make a Data Validation List - then you could put all valid values into the list and limit entries to those. Then you don't need to use very complex - or rather general - Conditional Formatting rules.

Using a DV list would mean that you could edit the list in the future to add/change/remove entries.​

As I say, you can (temporarily) circle Invalid entries if you are dealing with data that has already been entered - maybe then dash through and format these manually with a colour so you can filter/sort later. Data Validation circles disappear after a while and do not persist if you save and close, then re-open the file.