We help IT Professionals succeed at work.

Tips on conditional formatting.

thirtywinter
thirtywinter used Ask the Experts™
on
I've been toying around with conditional formatting for a little while now and I'm simply stumped for some reason. I want certain cells to show a graphic depending on what the cell value is at any given time.

This is what I want:
I want  to have a checkmark when a cell is between X and Y.
I want the same cell to have an arrow pointing up when the value in cell is over Y and an arrow pointing down when it is under X.

It looks like it should be easy to set up,but I've been hitting my head against the wall for about an hour now with no results, so any help would be appreciated. Or is what I want not possible?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
Conditional formatting provides several decent indicators based on "truth" tests.  E.g., a cells' value is between X and Y - it can change the font of the cell, change the fill, border, and even numeric format.

What you're asking for is for the cell to display something that's not part of conditional formatting.

I suggest, however, a helper column to give you what you want.  I've created a demo, where the first column is a random set of numbers between 1 and 20.  X is 5, Y is 10, so if the number is between X and Y, we get the check mark (Wingdings Font, Char(252) - see Sheet2 demonstration on how to get these funny characters), Up Char(233), Down char(234).

See example:

Dave
conditionalFormat-Helper-ChkUpDo.xls
Top Expert 2010
Commented:
I was able to do it in Excel 2007/2010 with icon sets.  For example:


Q-27244971.PNG
Most Valuable Expert 2012
Top Expert 2012

Commented:
Good one, Patrick - I had total forgotten about ICON SETS.  They're available in Excel 2007 as well!


Dave
JPIT Director

Commented:
The only problem with it is when the number is 7 it will show the up arrow and when it is 4 it will show the check. I believe if it is either X or Y exactly it should be checked as well. I have been unable for it to be no check when it is X or Y.
Cond-Format-Icons.xlsx
JPIT Director

Commented:
Sorry formatting was only set on Cell C2
Top Expert 2010

Commented:
peetjh,

That is a simple matter switching the operators in the drop downs to the left of the Value boxes.

Patrick
JPIT Director

Commented:
You would have to explain how because the only options are >=  and <= depending on which option you choose first. So in either case you can get it to be an up arrow or a check but unless I am just brain dead today which is possible at this time of day I don't see the any way to get no icons when value is = to X or Y.
Top Expert 2010

Commented:
OK fine, then make the value 7.000000001 for the up arrow, and all is good.  Does not change the substance of my answer one iota.

Author

Commented:
Patrick,

I'm using excel 2007 but I do not see any way to use custom icon sets like you have. Under format style there is no option to choose custom at all. Am I being thick or something?
Top Expert 2010

Commented:
thirtywinter,

Instead of trying to select 'Custom' (which won't be there), start off with your icon set as green arrow up, right pointing yellow arrow, red arrow down.  Then, edit the rule, and in the selector for the yellow arrow select instead the green check mark.

Patrick

Author

Commented:
Patrick,

I'm with you all the way to the yellow arrow. After that there is no way to select or change any of the arrows. Is it possible that's an Excel 2010 function?
Top Expert 2010

Commented:
Crap, that's a new Excel 2010 feature.

Starting up Excel 2007 to see what I can do as a workaround.
Top Expert 2010

Commented:
In Excel 2007, I think the best you are going to be able to do is Dave's suggestion in http:#a36331785

Author

Commented:
Hmmm... not unexpected.

However, I feel  that your solution was so blisteringly elegant that I just can't ignore it. I think I'll share the points among the two of you.

On a personal note, I just can't believe I couldn't get the conditional formatting to work the way I wanted for me without help. I'm not sure where my head was.. :D
Top Expert 2010

Commented:
thirtywinter,

Glad to help.  Mine does work in Excel 2010, but not in Excel 2007.  Bummer, but then again that gives you another reason to upgrade :)

Seriously though, upgrading to Excel 2010 is a must if only so that you can then bask in the glory that is PowerPivot.

Patrick

Author

Commented:
It's on my to-do list actually.. :)