Office Dev Center - Office Developer Documentation
Office Add-ins

Make your solution a native part of Office on every platform.

Choose a product below to learn more about add-ins.

Microsoft Graph

Connect to Office 365 data using the Microsoft Graph.

Get access to Users, Groups, Mail, Calendars, Contacts, Files, Tasks, People, Notes and more — all from a single endpoint.

See all Microsoft Graph documentation

Or, see the individual Office 365 REST API endpoints for OneDrive, Outlook and more

Office 365 Connectors

Use Office 365 Connectors to get useful information and content into your Office 365 Group.

See Office 365 Connectors documentation

 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 Dev Center - Office Developer Documentation
Office Add-ins

Make your solution a native part of Office on every platform.

Choose a product below to learn more about add-ins.

Microsoft Graph

Connect to Office 365 data using the Microsoft Graph.

Get access to Users, Groups, Mail, Calendars, Contacts, Files, Tasks, People, Notes and more — all from a single endpoint.

See all Microsoft Graph documentation

Or, see the individual Office 365 REST API endpoints for OneDrive, Outlook and more

Office 365 Connectors

Use Office 365 Connectors to get useful information and content into your Office 365 Group.

See Office 365 Connectors documentation