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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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

0
djw8
Asked:
djw8
  • 10
  • 4
  • 4
1 Solution
 
ChuckDeezelCommented:
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.
0
 
djw8Author Commented:
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
0
 
ChuckDeezelCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
djw8Author Commented:
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...
0
 
ChuckDeezelCommented:
Why not set it column by column then? Set it for column B. Then Set C (Using B in the formula) and so on.
0
 
djw8Author Commented:
No go on that, got mostly down arrows across the range regardless of the values.
0
 
Rory ArchibaldCommented:
You can't use icon sets with formulas - they only work on the value in the cell.
0
 
ChuckDeezelCommented:
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.
0
 
djw8Author Commented:
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.
0
 
djw8Author Commented:
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
0
 
Rory ArchibaldCommented:
Sorry - I read some of the comments in passing and merely made the observation that icon sets won't work for this (unless you use an additional row of cells that calculates the difference between the cells, then apply the formatting to that.) but then I wasn't really following the Q after that.
What you could do is use CF to apply a custom number format that includes an arrow symbol (up down or sideways). See attached demo.
cf-symbol-demo.xlsx
0
 
djw8Author Commented:
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.
0
 
Rory ArchibaldCommented:
You have to use the "use a formula to determine..." option, as I did in the sample I posted.
0
 
djw8Author Commented:
My bad, sorry, forgot to check the "Conditional Formatting" Tab. Thanks!!!
0
 
Rory ArchibaldCommented:
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!
0
 
djw8Author Commented:
I accepted the wrong comment as the solution, I'd like to remove this comment and select the correct solution, thank you
0
 
djw8Author Commented:
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.
0
 
djw8Author Commented:
This was succinct and easy to follow, thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now