Saturday, November 03, 2012

EXCEL COLOURING OF CELLS BASED ON VALUE OF ANOTHER COLUMN OF CELLS

Say if we have the names of students in Column A of the excel spreadsheet and the description of their grades in Column B as follows: “Excellent”, “Pass” or “Fail”. You want the names of the students in Column A to appear “Yellow” if their grades are “Excellent”, “Green” as  “Pass” and “Red” as “Fail”. This is what you do:


Open up Microsoft Excel 2007
Select the cell containing the first name entry, in this case it is A1
With cell A1 selected, go to “Home”->”Conditioning Formatting”->”New Rule”->”Use a Formula to determine which cells for format”
Inside the bar under “Format values where this formula is true”, type =(B1=”Excellent”) and click the “Format” button below and choose the “Yellow” colour, click Ok and Ok.
STILL with cell A1 selected, go to “Home”->”Conditioning Formatting”->”New Rule”->”Use a Formula to determine which cells for format”
Inside the bar under “Format values where this formula is true”, type =(B1=”Pass”) and click the “Format” button below and choose the “Green” colour, click Ok and Ok.
STILL with cell A1 selected, go to “Home”->”Conditioning Formatting”->”New Rule”->”Use a Formula to determine which cells for format”
Inside the bar under “Format values where this formula is true”, type =(B1=”Fail”) and click the “Format” button below and choose the “Red” colour, click Ok and Ok.
To apply this formula to other names in Column A, select cell A1, select “Format Painter” and paint the brush over the other cells in Column A.
Note: this method should work well unless the cell reference becomes locked like B1 becomes $B1$1

Total Pageviews