Conditional Format in Excel Tutorial

Conditional Formatting

Microsoft Excel 2010 offers Conditional Formatting options, which automatically format the values if they go beyond certain limit.

The option for “Conditional Formatting” is listed under the Home Tab in the Style Group of options.

Various conditional formatting options

  • Highlight Cells Rules : This option highlights any cell or cells automatically containing  text, values of dates or if the values in the cell are more than or less than an specified value or if it lies within the defined range.
  • Top/Bottom Rules: This option opens up a menu with options for applying formatting to highlight the cells containing cells containing above and below average values in the cell selection and top and bottom values, percentages.
  • Data Bars: This option allows you to use the predefined palette with different color data bars, which can be clicked to apply the format that indicates the magnitude of values with these horizontal bars. Each cell will show a bar on the background according to the value contained in the cell. 
  • Color Scales: This option lets you choose from the palette containing different three- and two-colored scales which helps you to identify values which lie in the similar range by highlighting them with similar color scale. The following screenshot shows the effect of applying Color Scales conditional formatting.
  • Icon Sets: This option lets you choose from the different icons which reflect the values in the similar range with these colorful icons. The following screenshot shows the effect of applying one of the Icon Sets conditional formatting.
  • New Rule: This option allows you to define your own rules according to which you can apply formatting to the selected cells.
  • Clear Rules: This formatting option allows you to removing all types of formatting applied to a selected Cells, Entire Worksheet, or Data Table by clicking on the Selected Cells option or Entire Sheet option or This Table option respectively.
  • Manage Rules: This formatting option allows you to manage rules with the help of a dialog box, which provides option for new rule, edit rule and delete rule.