Results 1 to 8 of 8

Conditional formatting a "#N/A" value

This is a discussion on Conditional formatting a "#N/A" value within the Excel Questions forums, part of the Question Forums category; I would like to conditionally format my "#N/A" cells to show red so I can spot values that don't conform ...

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    32

    Default Conditional formatting a "#N/A" value

    I would like to conditionally format my "#N/A" cells to show red so I can spot values that don't conform to my table...but I can't get the conditional formatting to work.

    In the conditional formatting box, I've tried...
    #N/A
    "#N/A"
    '#N/A"

    I have it formatted as a text value currently.

    Any ideas?

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,927

    Default Re: Conditional formatting a "#N/A" value

    You would think the cell showing #N/A would be enough ...

    anyway, try

    1. Select "Formula Is" in the conditional formatting

    =ISNA(A1)

    where A1 is the cell you want to test for error.

    Hope that helps..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    New Member
    Join Date
    Jan 2009
    Posts
    32

    Default Re: Conditional formatting a "#N/A" value

    I tried that but it turns ALL cells red for some reason.

  4. #4
    New Member
    Join Date
    Dec 2009
    Location
    Montreal Canada
    Posts
    30

    Default Re: Conditional formatting a "#N/A" value

    Hi Dave, what about using AutoFilter and sifting them out like that?

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,791

    Default Re: Conditional formatting a "#N/A" value

    Quote Originally Posted by Dave1969 View Post
    I tried that but it turns ALL cells red for some reason.
    What formula are you using Exactly? Make sure you use =ISNA(A1) not =ISNA($A$1)

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Location
    Scotland
    Posts
    377

    Default Re: Conditional formatting a "#N/A" value

    When doing this kind of conditional formatting, always make sure that you format 1 cell ata time. Therefore do formula is
    =Iserror(A1)
    as suggested (no $) and inout your formatting.
    THEN use the format painter to copy this formatting to the other cells.

    Probably not the only way to do but after lots of experience, it's the best way to not get it doing mad stuff.

  7. #7
    New Member
    Join Date
    Jan 2009
    Posts
    32

    Default Re: Conditional formatting a "#N/A" value

    Quote Originally Posted by barry houdini View Post
    What formula are you using Exactly? Make sure you use =ISNA(A1) not =ISNA($A$1)
    Yes, I did relative reference it using

    =ISNA(O4) in my case

    Still seems to affect all cells. Admittedly I've not used conditional formatting in Excel 2007 much, so I could be doing something wrong

  8. #8
    New Member
    Join Date
    Jan 2009
    Posts
    32

    Default Re: Conditional formatting a "#N/A" value

    stevie, that fixed it...I needed to use Format Painter to copy the formatting rather than copying the formula down...thanks everyone !!!!

    I'm also going to use a countif function to count the exceptions.

    Dave

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
  •  


DMCA.com