Solved

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

Posted on 2011-02-16
19
300 Views
Last Modified: 2012-05-11
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
Comment
Question by:djw8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 4
19 Comments
 
LVL 4

Expert Comment

by:ChuckDeezel
ID: 34908070
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
 

Author Comment

by:djw8
ID: 34908739
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
 
LVL 4

Expert Comment

by:ChuckDeezel
ID: 34908793
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:djw8
ID: 34909069
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
 
LVL 4

Expert Comment

by:ChuckDeezel
ID: 34909290
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
 

Author Comment

by:djw8
ID: 34910540
No go on that, got mostly down arrows across the range regardless of the values.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34915323
You can't use icon sets with formulas - they only work on the value in the cell.
0
 
LVL 4

Expert Comment

by:ChuckDeezel
ID: 34917353
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
 

Author Comment

by:djw8
ID: 34917776
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
 

Author Comment

by:djw8
ID: 35191327
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 35194079
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
 

Author Comment

by:djw8
ID: 35199295
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199308
You have to use the "use a formula to determine..." option, as I did in the sample I posted.
0
 

Author Comment

by:djw8
ID: 35199566
My bad, sorry, forgot to check the "Conditional Formatting" Tab. Thanks!!!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35199621
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
 

Author Comment

by:djw8
ID: 35199627
I accepted the wrong comment as the solution, I'd like to remove this comment and select the correct solution, thank you
0
 

Author Comment

by:djw8
ID: 35199652
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
 

Author Closing Comment

by:djw8
ID: 35199662
This was succinct and easy to follow, thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

632 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