Conditional Formatting Basics
/ 0
Conditional formatting allows users to create rules for selected cells that controls the way Excel formats those cells based on the contents of the cells and the criteria established by the user. If the rules (conditions) that are specified are met, then the formatting is applied. For example, a user could use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values.
Conditional formatting can also control the cell font, fill colour and border setting based not only on the selected cell's contents, but also based on the contents of another cell.
Of course, one could manually change the formats of cells that meet a criteria. But this would be a cumbersome and repetitive process, especially if the data set is large or the values frequently change. Conditional formatting automatically changes formatting when a cell meets certain criteria.
NOTE: In Excel 2003, and earlier versions, you were limited to 3 rules for each cell. To apply more than 3 conditional formas in a cell in Excel 2003 and earlier versions, you would need to use programming. Excel 2010 eliminates that restriction and users can apply as many conditions as they desire.
There are 2 types of conditions:
- Cell value based conditions: These conditions are tested on the cell value itself. So if you select a bunch of cells, and mention the condition as between 15 and 25, all the cells with values between 15 and 25 are formatted as you specify.
- Formula based conditions: Sometimes you need more flexibility than a few simple conditions. That is when formulas come handy. Conditional Formatting Formulas are slightly complicated and can be difficult to learn or use if you are new to excel. But they are very useful and intuitive and if you use them once you get a hang of it.
There are some examples here for you to practice with.