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
Home > Conditional Formatting > Manage Rules
Conditional Formatting Rules Manager > New Rule
New Formatting Rule > Select Rule Type > Format only cells that contain
Format only cells with: > Cell Value > between > 0 > 40
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.