Share via

Why isn't my conditional formatting based on a text value not working

Anonymous
2025-05-22T15:01:57+00:00

In my file I am trying to format any cell in column E with a text value of "#N/A" in column L. All values in columns K thru O were formulas that I then went through and copied and pasted as values. In my example below cell E2 should be filled blue with white text based on the "#N/A" value in column L. I have formatted column L as text and that hasn't worked either.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Rich~M 20,350 Reputation points Volunteer Moderator
    2025-05-22T15:15:24+00:00

    Excel interprets the #N/A as an error even if it is entered as text. So, =L1="#N/A" will produce a #N/A result not a TRUE result. For Conditional Formatting to work it has to result in a TRUE result. Use this instead for your Conditional Formatting to deal with the #N/A.

    =IFNA(L1,TRUE)

    Here is a screenshot showing the formula adjusted for E2 looking at L2.

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest