Solved

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

Posted on 2011-02-16
19
276 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
  • 10
  • 4
  • 4
19 Comments
 
LVL 4

Expert Comment

by:ChuckDeezel
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:djw8
Comment Utility
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
Comment Utility
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
Comment Utility
No go on that, got mostly down arrows across the range regardless of the values.
0
 
LVL 85

Expert Comment

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

Expert Comment

by:ChuckDeezel
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:djw8
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You have to use the "use a formula to determine..." option, as I did in the sample I posted.
0
 

Author Comment

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

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This was succinct and easy to follow, thanks
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now