Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Conditional Formatting, multiple blank or zero

Posted on 2011-02-21
Medium Priority
368 Views
How would a conditional formatting statement be written to cover multiple conditions in a cell, such as a blank cell or a "0" (zero) in a cell. Column of cells actually.

Thanks for any assistance

0
Question by:dgd1212

LVL 5

Assisted Solution

roger_karam earned 800 total points
ID: 34948249
You can use an iff statement in the conditional formatting then copy it down. Example:

In cell A1, use the following formula for conditional formatting = if(OR(A1=0;A1="');1;0) , than drag the format down to the other cells

hope that helps
-RK
0

LVL 10

Expert Comment

ID: 34949487
you may need to adjust roger_karam's formula to change the ; to , depending on your regional settings...
0

LVL 50

Accepted Solution

barry houdini earned 1200 total points
ID: 34950288
In conditional formatting you don't really need an IF function, just a statement that returns TRUE or FALSE, so if you wanted to format column A if it contains "x" or "y" then this would suffice

=OR(A1="x",A1="y")

For your specific example, if the blanks are truly blank then Excel actually treats those as zeroes so in that case you only need a single condition

=A1=0

That will format any zero that actually contains a zero or is truly blank

If you want to apply to a whole column you just select the column first and then use the formula applicable to row 1, so the above formulas would work to format the whole of column A

regards, barry
0

LVL 5

Expert Comment

ID: 34957306
Barry's answer is the way to go. Used to the IF statement because i remember having trouble with older versions of excel without it.
0

Author Closing Comment

ID: 34975519
Thanks guys. Also didn't realize that a "blank cell" = 0
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month10 days, 14 hours left to enroll