Modifying cell formatting subject to pre-defined conditions being met, can make data interpretation easier. A conditional format changes the appearance of a cell based on a condition. If the condition is satisfied, the specified formatting is applied to the cell. Otherwise, the cell remains unchanged.
Here, the range chosen by you is displayed next to the "Applies to" label. You can also manually change/specify the range here.
A combination of up to 3 rules [ that make up a rule set ] can be specified to the selected range at a time. Any number of rule sets can be configured for different cell ranges in a sheet.
A rule can be framed on
Using pre-defined conditional operators, a rule can be framed and the formatting for the cell, i.e., Cell background color, Cell text color and cell text style - bold, italics, strike through or underline can be set. Framing rules for different data types is explained in the next section.
Ex: The following rule can be used to highlight cells containing the text "Gold" in a different color.
Text containing "Gold".
Conditional Formatting can also be applied based on the results of a formula expression. Ex: Using the following formula rule, alternate rows can be highlighted.
Cells that are blank and contain error values can also be highlighted using built in rules.
Rule sets for Conditional Formatting are constructed, to be applied on the cells falling within the selected range. The cells can contain text, numerical or date values. The rules are framed depending upon the type of data contained in the cells in the selected range.
When the cells in the range contain numerical data, the following operators can be used to create rules.
For Example: The Rule: Cell Value is Between 60 and 80 -> Background Color - Red
This will set the background color for all those cells whose value lies between 60 - 80 to Red.
For using the operators Between and Not Between, the lower and upper limits can be specified.
If the rule is being framed for cells containing text values, the following operators are used.
For Example: Text contains "Shooting" , Bold, Background Color - Blue.
This will change the font style of those cells with the text "Shooting" to Bold, and set the background color to Blue.
A wide range of operators are available for constructing rules when date values are present in the cells. Apart from the pre-defined operators, you can also use the other operators to specify exact dates / date ranges.
For Example: Date occurring between 25/6/12 and 26/8/12 Background - Green
This rule will highlight all those cells which has dates falling under the specified range.
There are also separate set of operators available to apply formatting rules to cells that are blank or that contains error values. These rules can be used for highlighting blank cells in the range or can highlight cells that contain values in a different color. The data type of the values does not matter. Similarly, these rules can be used to highlight cells containing error values arising out of faulty formulae and those cells which contain normal or expected values.
For Example: Cells containing blanks - Background - Orange
This rule will highlight all blank cells in Orange.
You can also construct rules using formula expressions. The formula expression is evaluated on each cell. If the value returned is true, the formatting is applied to that cell. Else no change is made and the evaluation moves on to the next cell.
This rule can be used to highlight weekends if Column A contains Dates.