Avatar of thirtywinter
thirtywinter
Flag for United States of America asked on

Tips on conditional formatting.

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.
Microsoft Excel

Avatar of undefined
Last Comment
thirtywinter

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dlmille

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


Dave
JP

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
JP

Sorry formatting was only set on Cell C2
Your help has saved me hundreds of hours of internet surfing.
fblack61
Patrick Matthews

peetjh,

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

Patrick
JP

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.
Patrick Matthews

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
thirtywinter

ASKER
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?
Patrick Matthews

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
thirtywinter

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Patrick Matthews

Crap, that's a new Excel 2010 feature.

Starting up Excel 2007 to see what I can do as a workaround.
Patrick Matthews

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

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

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
thirtywinter

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