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
5
Medium Priority
?
368 Views
Last Modified: 2012-05-11
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
Comment
Question by:dgd1212
5 Comments
 
LVL 5

Assisted Solution

by:roger_karam
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

by:shahzadbux
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

by:
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

by:roger_karam
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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question