Excel allows you to format cells or ranges of cells based on what is in the cells. It gives you a large array of options on how you want to make a cell look, depending on what is in them. This can be very powerful to use in your spreadsheets and can enhance them incredibly.
In this blog post, I want to go over the basics of conditional formatting. Just how to set it up and the different types there are.
Let’s say we have the following spreadsheet:
We have a list of students and grades.
Conditional formatting will allow you to format these cells based on your set criteria.
Say you want to make all grades above 90 Green. And you want to make all grades below 75 Red.
To do this, first, hilight all the cells you want this applied to.
Next go to the Home tab and then over to Conditional Formatting. When you click on it, you’ll get the following dropdown. Drop down to Hilight Cell Rules and then over to Greater Than :
You will then be presented with a form which looks like below. Change the box on the left to read “90”. So this means it will format all cells great than 90.
Then on the right, it will ask you how you want it formatted. The default is to have Light Red. You can easily change this.
Click the drop down and you will get a list of choices on how to format it.
You should drop down to Green Fill because that is what we wanted for 90 and above.
If you do not see the formatting you like, you can drop down to Custom format. This will give you a broader range of styles to format. See next image:
As you can see above, Excel let’s you format the number style, the font, the border and the fill color of the cell. You can customize it however you want!
So next, now that we formatted everything above 90 to be green, we want to do the second part where we hilight everything below 75 as Red.
To do this, hilight all the cells you want to apply this to again:
You will notice the the numbers over 90 are already green.
So once those are hilighted, go to Conditional Formatting again, like earlier, but drop down to Less Than.
Now fill in the pop up wizard to format cells less than 75 as Red:
You will then get your table to look like this:
Congrats, exactly what we wanted!
Now if these grades change, this formatting is dynamic. So if a grade changes from 94 to 74, then the color will also change from Green to Red.
And when you do not want conditional formatting anymore, hilight the range of cells and go to Conditional Formatting.
Drop down to Clear Rules and then over to Clear Rules from Selected Cells.
This will then clear off all conditional formatting from it.
There are other ways to conditionally Format cells in Excel, like hilighting the top 10%, or hilighting duplicate values or creating small bar graphs on the numbers right in the cell. However I am not going to cover all of these today. I just wanted to show you the basics here on how to implement and remove Conditional formatting.
I hope this helps you! Please share this and let me know of any comments you have!