Solved

Excel 2010 Conditional Formatting

Posted on 2014-04-05
9
519 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
  • 5
  • 3
9 Comments
 
LVL 34

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 34

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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39980352
BTW, you can see the currently applied rules by going to Conditional Formatting->Manage Rules.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 34

Accepted Solution

by:
Dan Craciun earned 500 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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to setup 18 80
Software for Checking updates on Different Forums. 5 55
stamp tool on acrobat 1 26
Denali Software 2 15
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This video shows how use content aware, what it’s used for, and when to use it over other tools.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.

757 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

18 Experts available now in Live!

Get 1:1 Help Now