Help Documentation

Conditional Formatting

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.

Applying Conditional Formatting

Create Rules

  • To apply Conditional Formatting, you can select a single cell or a range of cells.
  • After selecting the values, click Format > Conditional Formatting on the menu tab.

  • The Create Rule dialog box is displayed.

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

  • Numerical
  • Text
  • Date
  • Cell Values.

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.
Formula =MOD(ROW(),2)

Cells that are blank and contain error values can also be highlighted using built in rules.

  • The Preview button gives a preview of how the cell value will look when the formatting is applied.
  • To specify another Rule, click the check-box "Rule 2" to enable the second rule builder.
  • Once the required rules are applied, click OK to save the setting.
  • If in the selected range, the conditions are met by the values of the cells, the formatting will be applied immediately.
  • On further updates in the sheet, if the existing cell value is changed, the formatting of that cell will be changed or left as it is, depending upon the new value satisfying the existing condition.

Manage Rules

  • To review the rules applied in a sheet, navigate to Conditional Formatting, and click Manage Rules.
  • The dialog box displays all the rule sets and the ranges where they are applied in the current sheet.
  • You can expand each rule set to view / review each rule.
  • Hovering over the settings icon against each rule set displays options to Edit/Copy/Delete the rule.
  • Using the drop down, all the rules in the workbook can also be viewed.

Framing Conditional Formatting 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.

Numerical Values

When the cells in the range contain numerical data, the following operators can be used to create rules.

      • Less Than
      • Greater Than
      • Less than or equal to
      • Greater than or equal to
      • Equal to
      • Not equal to
      • Between
      • Not Between

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.

Text Values

If the rule is being framed for cells containing text values, the following operators are used.

      • Contains
      • Does not contain
      • Begins with
      • Ends with.

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.

Date Values

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.

      • Today
      • Tomorrow
      • Yesterday
      • In the last 7 days
      • In the next 7 days
      • Last Week
      • This Week
      • Next Week
      • Last Month
      • This Month
      • Next Month
      • Last Year
      • This Year
      • Next Year
      • Before
      • On or Before
      • After
      • On or After
      • On
      • Not On
      • Between
      • Not Between

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.

Special Values

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.

Formula

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.
Ex: =OR(WEEKDAY($A6)=1;WEEKDAY($A6)=7)
This rule can be used to highlight weekends if Column A contains Dates.

Note

  • Conditional Formatting can be used for comparing values across columns too. Highlighting highest/lowest values, alternate rows, duplicate values, upcoming dates, expiry dates, errors and blanks are some of the most common usages for Conditional Formatting.
  • While applying Conditional Formatting rules for Numerical values, the value of blank cells will be treated as 0.
  • Using a formula rule, formatting can be applied to an entire row.

Top