Computer Tips

or
"I didn't know you could do that!"

  All Tips:

EXCEL TIP - Color a Cell Based on It's Value
CONDITIONAL FORMATTING

Scenario:

You're tracking time spent on projects and want to color cells based on the amount of time estimates for each project.

The color scheme is:

  • Pink - if the project takes 20 hours or less
  • Yellow - if the project takes over 20 but less than 30 hours
  • Turquoise - if the project takes 30 or more hours

Select the cells which will be "colored". .


You can set up to 3 conditions
Menu:
Format, Conditional Formatting...

 

Condition 1

between 0
and 20

Click Format... button

 

Patterns tab

Select a color

Ex.:
screaming pink

Click OK

 

Completed Condition 1

If you don't want to add any more conditions, click OK

If you want to add more conditions, click Add>>

 

 

Condition 2

Cell Value Is
between 20.01 and 30

Click Format...

Select a color
Ex.:
yellow

Click OK

Completed Condition 2

If you don't want to add any more conditions, click OK

If you want to add more conditions, click Add>>

 

 

Condition 3

Cell Value Is greater than 30

Click Format...

Select a color
Ex.:
screaming turquoise

Click OK

 

All 3 conditions are complete

 

Click OK

 

 

 

Cells are filled with appropriate color

If the values change, the formatting will change automatically

   
To Remove Conditional Formatting:

Select the range of conditionally formatted cells

then,

Menu: Format, Conditional Formatting...

 

 

Click Delete...

 

Check all the boxes

Click OK

 

All formats are cleared

Click OK

 

 

   March 31, 2006

ITS Dept 105 Nicarry Hall
361-1582