Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    is it possible to highlight cells with no data (null)? Conditional Formatting doesn't provide this option, which is something of a fail, in my humble opinion

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to highlight cells that contain neither a value nor a formula:
    - Select the range.
    - Note the address of the active cell within the selection; let's say it is B3.
    - Select Format | Conditional formatting...
    - Select Formula Is from the first dropdown.
    - Enter the formula =ISBLANK(B3) in the box next to it, using the cell address noted above.
    - Click Format...
    - Specify the pattern color that you want.
    - OK your way out.


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks - that works, but it's a little strange... if i highlight a large range and enter =ISBLANK(A1:O26) <<regardless of the actual range>> Excel happily applies formatting across the entire dataset. weirdness.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should use

    =ISBLANK(A1)

    where A1 is the active cell within the selection (usually the upper left corner). Excel will automatically adjust the cell reference for the other cells within the selection. Do *not* use

    =ISBLANK(A1:O26)

    where A1:O26 is the selected range!

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You should use

    =ISBLANK(A1)

    where A1 is the active cell within the selection (usually the upper left corner). Excel will automatically adjust the cell reference for the other cells within the selection. Do *not* use

    =ISBLANK(A1:O26)

    where A1:O26 is the selected range!
    Another way with similar approach :

    [attachment=86822:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by prasad View Post
    Another way with similar approach :
    The result is not quite the same. The suggestion I posted will highlight cells that are really blank, i.e. they contain neither a constant value nor a formula. Yours will also highlight cells that contain a formula resulting in an empty string.
    Which one to use depends on what the original poster intended - one method is not better or worse than the other, just different.

Posting Permissions

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