Conditional Formatting

Microsoft Excel 2010

Conditional formatting can apply formatting effects to cells depending on their contents, thereby improving the readability of your data.  e.g.  For example, you could add condition formatting to highlight cells over or under a certain amount. You can also add icon sets, color scales, and data bars to better illustrate the change in values.

Conditional formatting can be applied before or after the data is added to the worksheet.

Notice the following sample effects based on the raw data shown in the set at the top left.

 

Adding Conditional Formatting

  1. Select the range of cells to apply conditional formatting to.
  2. Display the Home ribbon and click on the Conditional formatting button. The following options appear:
  3. Apply conditional formatting rules, as desired. Refer to the following headings for more information on each option.

Highlight Cell Rules

  1. Select Highlight Cell Rules from the Conditional Formatting list (as indicated in Adding Conditional Formatting, above).    The following options appear:
  2. Select the comparison rule from the list shown. The resulting window depends on which comparison you selected. The following illustration shows the window option for the Greater Than rule.
  3. Enter your criteria in the field at the left then use the drop list button to select the formatting desired. Use Custom Format if you wish to create your own.
  4. Choose OK to finish.

Top/Bottom Rules

  1. Select Top/Bottom Rules from the Conditional Formatting list (as indicated in Adding Conditional Formatting, above).    The following options appear:
  2. Select the comparison rule from the list shown.
  3. Select the formatting desired. Use Custom Format if you wish to create your own.
  4. Choose OK to finish.

Data Bars

  1. Select Data Bars from the Conditional Formatting list (as indicated in Adding Conditional Formatting, above).    The following options appear:
  2. Select a format from those shown.

Color Scales

  1. Select Color Scales from the Conditional Formatting list (as indicated in Adding Conditional Formatting, above).    The following options appear:
  2. Select a format from those shown.

Icon Sets (very cool)

  1. Select Icon Sets from the Conditional Formatting list (as indicated in Adding Conditional Formatting, above).    The following options appear:
  2. Select a format from those shown.

New Rule (Make up your own)

You can also apply conditional formatting based on your own rules. For example, you may want to format cells greater than $50,000 in red and cells less than $20,000 in green. In this example you would choose New Rule then create two rules using the selection Format all celled based on their values.

Removing Conditional Formatting

  1. Select the area that you wish to remove the conditional formatting from.
  2. Click on the Conditional Formatting button on the Home ribbon then choose Clear Rules.

Additional References