djw8

asked on

# How do I set up conditional formatting in Excel 2007 to use Icon Sets

I'd like to use the arrows in the Icon Sets to indicate a higher or lower value in the previous cell in the row.

So, if A1 = 6, B1 = 8, C1 =6, I want B1 to show an up arrow and C2 would show a down arrow, etc.

I've tried the conditional formatting feature but I can't get past using the first cell as the reference cell for all cells.

Thanks

So, if A1 = 6, B1 = 8, C1 =6, I want B1 to show an up arrow and C2 would show a down arrow, etc.

I've tried the conditional formatting feature but I can't get past using the first cell as the reference cell for all cells.

Thanks

Why not use the "Use a formula to determine which cells to format" option. Just click Conditional Formatting > New Rule > Use a formula to determine which cells to format. Create a rule for each condition and set it to your arrow.

ASKER

Thanks for the reply, it worked except for this glitch: Arrow direction is indicated in paras.

Neu = arrow points to side, I'd expect that in A1 because it's the first in the series but I don't expect it in cells C1 or D1.

A1 B1 C1 D1 E1 F1

144(neu) 161 (up) 148 (neu) 143(neu) 167(up) 129(dwn)

Here's the formula I'm using to designate which cells to format: =$B$1>$A$1. I suspect I need to adjust the formula but not sure what adjustment to make.

Thanks

Neu = arrow points to side, I'd expect that in A1 because it's the first in the series but I don't expect it in cells C1 or D1.

A1 B1 C1 D1 E1 F1

144(neu) 161 (up) 148 (neu) 143(neu) 167(up) 129(dwn)

Here's the formula I'm using to designate which cells to format: =$B$1>$A$1. I suspect I need to adjust the formula but not sure what adjustment to make.

Thanks

So according to the formula, you are comparing it to A1. How did you set the conditional formatting? Did you set it for the entire range or did you split it up at all?

If you selected the entire table as the range, try doing it column by column? Just a shot in the dark really though.

If you selected the entire table as the range, try doing it column by column? Just a shot in the dark really though.

ASKER

I selected the entire range, in this case it was a row, then used the Conditional Formatting>>New Rule, entered the rule, clicked OK then went back to the Conditional Formatting with the range still highlighted and selected the Icon Sets and the arrows appeared.

I began with A1 because its the first cell with a value in the row, but I dont want to compare all the subsequent values with A1, I want to compare each value to the value that preceded it. It looks like I got part of it right...

I began with A1 because its the first cell with a value in the row, but I dont want to compare all the subsequent values with A1, I want to compare each value to the value that preceded it. It looks like I got part of it right...

Why not set it column by column then? Set it for column B. Then Set C (Using B in the formula) and so on.

ASKER

No go on that, got mostly down arrows across the range regardless of the values.

You can't use icon sets with formulas - they only work on the value in the cell.

Ahh. And I didnt test it so...Then why not try to use the "Format all cells based on their values" using the "Icon Sets" format style.

Unless, of course, thats what you were already doing.

Unless, of course, thats what you were already doing.

ASKER

Using that option doesnt include the arrows Icons as a formatting option. I thought the formula I was writing effected the arrows but it doesn't. It's some sort of default rule for the arrows that applies by selecting a range then going to Conditional Formatting>>Icon Sets.

I'd still like to find a way to compare a cell's value to the one preceding it then if possible assign an icon to the cell to indicate higher, lower or equal values.

I'd still like to find a way to compare a cell's value to the one preceding it then if possible assign an icon to the cell to indicate higher, lower or equal values.

ASKER

First, I apologize for leaving this unattended. My ignorance of the protocols and courtesies both documented and implied is born from my limited interactions here. I haven't been ignoring this question, I am on the email alert system which advises me when a response is posted. I check my email mulitple times every day and if I don't see a response then I figure nobody has been able to propose a solution...yet. One member has suggested a couple of solutions which I've tried and responded to, another member (thanks to both) has told me that I can't do it the way the other member is proposing but didnt provide a possible solution.

I was waiting for something more definitive but nobody here has told me that what I want to do isn't possible. Since hope springs eternal I thought someone would read the question and post a response eventually ; I guess I didnt think the question was that difficult. I'm willing to close this but I'd like to do it with an understanding of why this wasn't answered. Perhaps there's something I can do in the future to incite a bit more interest.

Thanks

I was waiting for something more definitive but nobody here has told me that what I want to do isn't possible. Since hope springs eternal I thought someone would read the question and post a response eventually ; I guess I didnt think the question was that difficult. I'm willing to close this but I'd like to do it with an understanding of why this wasn't answered. Perhaps there's something I can do in the future to incite a bit more interest.

Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks rorya when I look through the CF options, I don't see a way to build the formula to do what I want it to do. And, I'm on my way now to teach myself how to build a custom number format.

You have to use the "use a formula to determine..." option, as I did in the sample I posted.

ASKER

My bad, sorry, forgot to check the "Conditional Formatting" Tab. Thanks!!!

FYI, all you have to do is accept my solution if it works for you - it won't help others coming to this question later if you have accepted your own comment as the answer!

ASKER

I accepted the wrong comment as the solution, I'd like to remove this comment and select the correct solution, thank you

ASKER

rorya, I apologize, I clicked on the wrong comment as the solution. I've requested they reopen the question so I can correct the mistake.

ASKER

This was succinct and easy to follow, thanks