Office Expert

Conditional Formatting

One of the most useful function in excel is conditional formatting . Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s value/certain conditions .It allows you change the appearance of the cell depending upon certain conditions .Conditional formatting sticks to a cell and changes dynamically depends on the cells value .

Example :-

Given below are the marks of my friends in 7th sem engineering  [any resemblance to anyone is purely coincidental ] .

We need to give different colours to the cell according to the  average of marks scored by different students.

If the average is less than 40 then Red Color to the cell

If the average mark is more than 40 and less than 50 then YELLOW color to the cell

If the mark is More than 50 And less than 60 then Green Color

More than 60 then Blue color.

We want an answer like this 

Step 1

Find The Average

Step 2

Select all the Average Cells –goto –HOME MENU-you can locate STYLES PANEL And CONDITIONAL FORMATTING item

The conditional formatting menu gives you various options .The easiest one is the COLOUR SCALES option.

Click On More Rules

Select “FORMAT ONLY CELLS CONTAIN…” .

This allows you to setup our values .

The part we are interested is the bottom part .Under the “EDIT THE RULE DESCRIPTION “.It says Cell value “BETWEEN/LESS THAN /GREATER THAN..”.

Here first option we want is IF the average mark is less than 40 then cell color is red .

select the cell value “LESS THAN “ Write “40”

Click Format ….Click Fill Color …Select Red Color Press OK .

You will find that one of the cell turned red …

This  Belongs To CHRIS ..He was really a friend of mine ….:(

Click again on conditional formatting –Goto Manage rules –

The below dialog box appears as below...

click New Rule ..

You will goto the below window which we have worked earlier..

Now we need a Yellow color cell when mark is between 40 & 50 .

Under the “EDIT THE RULE DESCRIPTION”

select the cell value “BETWEEN” write 40 & 50

Click Format ….Click Fill Color …Select Yellow  Color Press OK .

Continue  the same procedure according to the condition and finally we will get the formatted excel sheet as below…..