Conditional Formatting in Excel: How to Change the Background Color of Cells Based on Values in the Cell

Conditional formatting in MS Excel allows you to apply different formatting options on one cell or more cells with certain specified conditions. It is very helpful to identify the differences in a wide range of values at a glance. This feature in Excel gives you the ability to color rows, hide duplicate, hide errors and highlight items.

This article explains that how you can change the background color of cells according to their values.

Example: You have a spreadsheet of average marks obtained by students in a exam and you want to figure out overall performance quickly, then you can color each cell of average marks according to cell's value. It is the easiest way to see at a glance relevant marks.

I am just taking few names of students and their marks in particular subjects randomly.

In above example Overall Average is the average marks of students in their exam and you want to color background of these cells on the basis of their marks. So that you can easily figure out who is failed, who has got good grade and who has got excellent grade.

I will color each cell's background of Overall Average marks depending on a scale. Let me decide the scale...

• If a student has scored average marks between 0 and 40 then he/she is failed and color will be Red.
• If a student has scored average marks between 40 and 60 then color will be Blue and it is average grade.
• If a student has scored average marks between 60 and 80 then color will be Yellow and it is good grade.
• If a student has scored average marks between 80 and 100 then color will be Green and it is excellent grade.

You can choose colors according to your benchmark. I have chosen it randomly to make the differences between each range of overall average marks.

To do this, follow these steps:

1. Select all the cell that you want to apply this formatting; I have selected B8 to G8.

Select > B8 to G8

2. Now go at the top of screen and select Home tab. Now click Conditional Formatting on the ribbon and select Manage Rules.

Home > Conditional Formatting > Manage Rules

3. Now you will see the pop window of Conditional Formatting Rules Manager.

4. Now click on New Rule to set color of cells red for marks between 0 and 40.

Conditional Formatting Rules Manager > New Rule

5. Now you will see New Formatting Rule window. In the pop-up window of Select Rule Type section, choose the second one “Format only cells that contain”.

New Formatting Rule > Select Rule Type > Format only cells that contain

6. In Edit the Rule Description section select Cell Value in first drop down box, between in second drop down box, type 0 in third drop down box and 40 in the last drop down box.

Format only cells with: > Cell Value > between > 0 > 40

7. Now click on Format and you will see a pop-up window of Format Cells.

8. Now click on Fill tab and choose the red color from the drop-down menu of Color section and click on OK.

Format Cells > Fill > Color > Red > OK

When you will click on OK in previous step then you get return back to New Formatting Rule window and preview box of Format will shows color red. Now click on OK.

10. Now you will get back return to Conditional Formatting Rules Manager window.

11. You are done with first rule, if Cell value is between 0 and 40 then color will be Red.

12. Now click on New Rule and make second rule of cell value between 40 and 60 (color will be blue). To do these just follow all methods that you had done before. When you are done with second rule then Conditional Formatting Rules Manager window will look like this.

13. Now do the same thing to set your rest of the rules. When you are done with all your rules then Conditional Formatting Rules Manager window will look like this.

14. Click on OK and your Overall Averages cell will look like this.

 

In above sheet you can easily figure out who has failed (red color) and who has scored good grade (green color) at a glance. These steps are applicable on MS Excel 2007 and 2010.