Forum Discussion
Conditional Formatting exception formula
Hi,
I have a data set where I would like to highlight the cell if the cell value is "N". However in the same data set, I also have cell values of "N/A".
None of the existing preset rule seems to be able to tell the two values apart.
So am wondering if I missed out any suitable preset conditions or is there formula that i can use?
On the other hand, there are times where there is more text in the cell other than "N", eg. "N - xxxx"
So I thought of creating a formula that would identify cell that beginning with the letter "N" with the exception of "N/A". That would be the perfect scenario if anyone can help me creating a formula for this.
Much appreciated.
To highlight any non-blank cell except one with question mark you may use
=(A2<>"")*(A2<>"?")Dates in Excel are actually numbers. Except texts which looks dates, but formatting won't convert them into the dates.
To achieve conditional formatting that highlights cells with the value "N" but excludes "N/A" and other variations like "N - xxxx," you can use a custom formula.
Here is a formula you can use:
=AND(ISTEXT(A1), A1<>"N/A", LEFT(A1,1)="N")
This formula uses the AND function to combine multiple conditions:
- ISTEXT(A1) checks if the cell contains text.
- A1<>"N/A" ensures that the cell value is not exactly "N/A."
- LEFT(A1,1)="N" checks if the first character of the cell value is "N."
Here are the steps to apply this formula for conditional formatting:
- Select the range of cells where you want to apply conditional formatting.
- Go to the "Home" tab on the ribbon.
- Click on "Conditional Formatting" in the toolbar.
- Choose "New Rule."
- Select "Use a formula to determine which cells to format."
- Enter the formula: =AND(ISTEXT(A1), A1<>"N/A", LEFT(A1,1)="N")
- Set the formatting options you desire.
- Click "OK" to apply the rule.
This formula should highlight cells that start with "N" but exclude "N/A" and other variations. Adjust the cell reference (A1 in this example) based on your actual range.
Note: If your data is in a different column or range, you need to adjust the formula accordingly. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Hi NikolinoDE,
I have managed to vary your formula which you provided to the original poster for my own work book but have a couple of snagging issues. I am wanting to highlight any cell which is not empty except for cells with a question mark.
I have used the formula: =AND(ISTEXT(A2), A2<>"?") but I have some instances where some cells with dates in them haven't been highlighted but others have. I have checked these cells and they have the same format as other highlighted cellsTo highlight any non-blank cell except one with question mark you may use
=(A2<>"")*(A2<>"?")Dates in Excel are actually numbers. Except texts which looks dates, but formatting won't convert them into the dates.
