?
Solved

Excel 2010 Conditional Formatting

Posted on 2014-04-05
9
Medium Priority
?
545 Views
Last Modified: 2014-04-07
I am trying to use the Excel 2010 Conditional Formatting feature to color a cell Red, Yellow, or Green.  If the cell’s value is less than 61, I want the cell color Green.  If the cell’s value is between 61 and 80, I want the cell color Yellow.  If the cell’s value is greater than 80, I want the cell color Red.  The data is displayed as a percent.  I have not been able to find what combination of choices to make this work.

A1                                B1
Current Capacity      74%
0
Comment
Question by:cowboywm
[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
  • 5
  • 3
9 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39980282
Select that column, then simply add 3 conditional formatting rules:
1. Conditional formatting->Highlight cell rules->Less than->61% and put "Light green fill"
2. Conditional formatting->Highlight cell rules->Between->61% and 80% and put "Yellow fill"
3. Conditional formatting->Highlight cell rules->More than->80% and put "Red fill"

You have to remember that conditional formatting rules are additive, meaning you can add as many as you want.

HTH,
Dan
0
 

Author Comment

by:cowboywm
ID: 39980334
Dan, I'm sorry.  I don't understand what formulas to input where on the New Formatting Rule Form.   Can you be more specific regarding what to select and what to put in each field by using the field's names, i.e. Select a Rule Type, Minimum, Midpoint, Maximum, Type, Value, color, etc.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39980345
See the images;
1. select the cells
2. click on Conditional Formatting
3. click on Highlight cells rules
4. click on Less than...
5. type 61%
6. select "green fill..."

Repeat for the other rules.
excel conditional formattingexcel format less rule
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39980352
BTW, you can see the currently applied rules by going to Conditional Formatting->Manage Rules.
0
 

Author Comment

by:cowboywm
ID: 39980450
Dan, first I was looking in the wrong form.  Thanks for clarifying.

Second, the cell referenced B1 must check for three things not just one.  I do not have three separate cells to check.  How is this configured using three value possibilities in one cell?

Bill
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 total points
ID: 39980453
As I was saying, "conditional formatting rules are additive, meaning you can add as many as you want."
After you add the first rule (<61, green) add the rest.

You can add as many rules as you want to a range. A range can consist of a single cell.

You can add multiple rules
0
 

Author Closing Comment

by:cowboywm
ID: 39980544
Yes, this does work.  Make sure you use decimal points for percentages.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39980550
Glad I could help!

Please read the grading guide here: http://support.experts-exchange.com/customer/portal/articles/481419

If you award any grade other than the default A, you're required to post an explanation of what was deficient on the solution you accepted.

Thank you.

PS: 61% = 0.61
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 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