Scribd
Upload
115 views5 pages

Excel Conditional Formatting Guide

Conditional formatting allows you to change cell appearances based on conditions. The document discusses applying conditional formatting to color code student exam grades in different colors based on score ranges. It also provides examples of applying additional conditional formatting rules to cells, including data bars, icons, cell colors, borders, and date formats.

Uploaded by

Suman Mamgain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
115 views5 pages

Excel Conditional Formatting Guide

Conditional formatting allows you to change cell appearances based on conditions. The document discusses applying conditional formatting to color code student exam grades in different colors based on score ranges. It also provides examples of applying additional conditional formatting rules to cells, including data bars, icons, cell colors, borders, and date formats.

Uploaded by

Suman Mamgain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Conditional Formatting

You can use something called Conditional Formatting in your Excel spreadsheets. Conditional Formatting allows you to change the
appearance of a cell, depending on certain conditions. What we'll do is to colour the Overall Averages on our Student Exam
spreadsheet,
Highlightdepending on the
the cells with grade.
Overall Here's which
Grades, the spreadsheet we'llB11
should be cells be working
to I11 on.
The Overall Averages range from 44 to 85. We'll colour each grade, depending on a scale. A different colour will apply to the following
grades:

50 and below
51 to 60
61 to 70
71 to 80
81 and above

001 002 003 004 005 006 007


Business Analysis 45 78 56 55 78 37 88
HR 55 65 44 54 34 87 88
Finance 78 87 77 35 56 45 89
IT 89 98 76 38 65 45 98
Sales 56 65 54 67 76 65 86
Manufacturing 76 67 78 39 75 74 49

Overall Average 66.50 76.67 64.17 48.00 64.00 58.83 83.00


Conditional Formatting

1) Apply Data Bar Conditional Formatting to all cells based on their values
Min 40 & Max 100
Fill Color Any & Apply Border

2) Copy the Above Data & Apply Conditional Formatting Based on following conditon
Apply the Red Color to all departments that contain S character

001 002 003 004 005 006 007


Business Analysis 78 56 55 78 37 88
HR 55 65 54 34 88
Finance 78 87 77 35 56 45 89
IT 89 76 38 45 98
Sales 56 65 54 76 65
Manufacturing 76 67 78 39 75 74 49

Overall Average 70.80 72.40 68.20 44.20 63.80 53.20 82.40

3) Apply above example to the conditional formatting to blank cell


any background color
Conditional Formatting

1) Set the Four Rating Format based on their values to the Total Column Data Example 3
>=5000
2) Set the applied Icon in Average column to based on their values.
>=700 , >=500 and <700 And <500

Data Example 3

Region Builder Units Average Total


central Raheja 8 389 3112
east Goyal Group 10 385 3850
north Karan Group 3 771 2313
central Lodha Group 5 313 1565
south Lodha Group 10 574 5740
west Raheja 8 730 5840

3) Fill the Date (MM-DD-YY) format from recent Month


& Apply the color to Last week days
mple 3

Date Fill Here


9/27/2013
9/30/2013
10/10/2013
10/15/2013
10/24/2013
10/25/2013 Date occuring in the last 7
10/26/2013 days

You might also like