Solved

Excel 2010 Conditional Formatting

Posted on 2014-04-05
9
534 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A list of useful business intelligence software.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

734 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