Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    New Member reneev's Avatar
    Join Date
    Apr 2017
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

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

    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)
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    New Member reneev's Avatar
    Join Date
    Apr 2017
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Try something like:
    Code:
    =AND($A1<>0,OR($A1<3.88,A1>213.69))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member reneev's Avatar
    Join Date
    Apr 2017
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

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

    Quote Originally Posted by Joe4 View Post
    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.
    Last edited by tyija1995; May 29th, 2019 at 07:04 PM.
    √-1 2³ ∑ π
    …And it was delicious!

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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

  9. #9
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Helping you to Excel

  10. #10
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Helping you to Excel

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •