Solved

Excel 2010 Conditional Formatting

Posted on 2014-04-05
9
531 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
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 34

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Let’s list some of the technologies that enable smooth teleworking. 
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

830 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