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

Office Developer Documentation

Office 365 APIs

Access Office 365 data for websites or native applications running on iOS, Android, Windows, and other device platforms with the Office 365 REST APIs.

APIs

Develop for

APIs for

Try the Office 365 APIs

Office Add-ins (formerly Apps for Office)

Build an add-in that lives inside Outlook, Excel, Word, PowerPoint, or Project to extend what you can do from right in the documents, across devices and platforms — like pulling content from the web, calling mapping services, locating news, translating text, and more.

SharePoint Add-ins (formerly Apps for SharePoint)

Build SharePoint-hosted or provider-hosted add-ins to use remote web applications, surface remote data, offload business logic, access SharePoint list data, integrate with Office applications and services, make SharePoint a portal for other applications, and more.

 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

Office Developer Documentation

Office 365 APIs

Access Office 365 data for websites or native applications running on iOS, Android, Windows, and other device platforms with the Office 365 REST APIs.

APIs

Develop for

APIs for

Try the Office 365 APIs

Office Add-ins (formerly Apps for Office)

Build an add-in that lives inside Outlook, Excel, Word, PowerPoint, or Project to extend what you can do from right in the documents, across devices and platforms — like pulling content from the web, calling mapping services, locating news, translating text, and more.

SharePoint Add-ins (formerly Apps for SharePoint)

Build SharePoint-hosted or provider-hosted add-ins to use remote web applications, surface remote data, offload business logic, access SharePoint list data, integrate with Office applications and services, make SharePoint a portal for other applications, and more.