Office developer documentation, downloads, tools | Office 365, Apps for Office, Apps for SharePoint

 none
Exclude null values with conditional formatting on an Excel 2007 pivot table

    السؤال

  • Hi,

    I have Excel 2007 pivot table that is reading data from Access. I am trying to do conditional formatting on a numeric field  where between 0 and 5 it is green and greater than 5 it is red.  However, I want to exclude null values (I don't want them to turn green).  I do want a value of 0 to be green though.  I did between 0 and 5 for green but it turned the nulls green.  How can I exclude the nulls?

    Chuck


    Chuck W
    15/جمادى الأولى/1432 02:43 م

الإجابات

  • Format the select cells as green if its value is between 0 and 5; Then click Conditional Formatting > New Rule > Select "Format only cells that contain" > in the drop down menu "Format only cells with:" select "Blanks" > Click "Format" > do what you like
    Qingping Cheng
    • تم وضع علامة كإجابة بواسطة ChuWil 17/جمادى الأولى/1432 04:33 م
    17/جمادى الأولى/1432 04:56 ص

جميع الردود

  • Add a rule that tests for cells whose value is equal to ="" and set the format to be standard (I assume no color).

    Make this rule the first to be evaluated.


    Regards, Hans Vogelaar
    15/جمادى الأولى/1432 03:08 م
  • Try This in "Use a formula to determine which values to format"

    =IF(B5<>"",IF(B5<6,TRUE,FALSE),FALSE)

     

    i have used this in one of my reports and working


    Murali M
    16/جمادى الأولى/1432 12:24 م
  • Thanks for your help.  I am a novice when it comes to conditional formatting.  I selected "Manage Rules" and then selected "Edit Rule".  The Apply Rule to is set at =$AK$13:$AN$13 and Format only cells that contain is selected.  In the Edit the Rule Description I have Cell Value between =0 and =5.  The format is set to green.  How would I substitute your formula with what I currently have?

    Chuck


    Chuck W
    16/جمادى الأولى/1432 12:43 م
  • Format the select cells as green if its value is between 0 and 5; Then click Conditional Formatting > New Rule > Select "Format only cells that contain" > in the drop down menu "Format only cells with:" select "Blanks" > Click "Format" > do what you like
    Qingping Cheng
    • تم وضع علامة كإجابة بواسطة ChuWil 17/جمادى الأولى/1432 04:33 م
    17/جمادى الأولى/1432 04:56 ص
Office developer documentation, downloads, tools | Office 365, Apps for Office, Apps for SharePoint