How to: Using Conditional Formatting in Excel 2007

Conditional Formatting in Excel allows you to select one or more cells and create rules that determine when and how those cells are formatted. You can set Excel to automatically modify the font, fill color, and border settings of a particular cell based on its contents or based on the contents of another cell. If the rules (conditions) that you specified are met, then the formatting is applied.

The Conditional Formatting menu is divided into several categories:

  • Highlight Cells Rules – allows you to set specific formatting based on criteria selected (i.e., highlight all cells greater than a given number)
  • Top/Bottom Rules – applies formatting to cells if they are in the top or bottom range of your data (i.e., highlight all cells in the top 10%)
  • Data Bars – applies shading to a cell, where the length of the bar depends on the value of a cell
  • Color Scales – applies a color scheme to the selected data, where the color depends on the value of a cell
  • Icon Sets – similar to color scales, icon sets allow you to add a specific icon to a cell, where the icon displays depends on the value of the cell
  • New Rule – allows you to create a custom rule
  • Clear Rules – removes all rules for a selected cell or series of cells
  • Manage Rules – allows you to view/modify rules for the spreadsheet

Using Highlight Cells Rules

Say you have a spreadsheet with the following data and you want Excel to highlight any cells that have a value greater than 60:

To accomplish this, simply follow these steps:

  1. Highlight the cells you wish to include
  2. Click on Conditional Formatting and select Highlight Cells Rules
  3. Click on Greater Than… from the provided sub-menu
  4. In the dialog box that appears, enter 60 into the provided text box on the left and choose a formatting option from the drop down menu on the right
  5. Click on OK

 The conditions you selected should now be applied to the cells you highlighted.

Using Top/Bottom Rules

Using Top/Bottom Rules is a great way to identify the largest or smallest items in your data.  The menu in Excel specifically notes Top 10 Items or Bottom 10%; however, Excel allows you to define this criteria yourself (e.g., Top 10%, Top 2%, or Top 40%).

Say you had a spreadsheet with the following data and you wanted Excel to highlight all cells in the top 40%:

To accomplish this, simply follow these steps:

  1. Highlight the cells you wish to include
  2. Click on Conditional Formatting and select Top/Bottom Rules
  3. Click on Top 10 %…
  4. In the dialog box that appears, enter 40 in the provided text box to the left and choose a formatting option from the drop down menu on the right.
  5. Click on OK

The conditions you selected should now be applied to the cells you highlighted.

Using Data Bars, Color Scales, and Icon Sets

Applying Data Bars, Color Scales and Icon Sets are very easy and can add an appealing graphical component to your data. To apply any of these features, follow these steps:

  1. Highlight the cells you wish to include
  2. Click on Conditional Formatting
  3. Select Data Bars, Color Scales, or Icon Sets, depending on which formatting option you wish to add
  4. Click on one of the pre-defined formatting options

The chosen formatting option will be immediately applied to the cells you selected.

 

You can even apply multiple options to the same range of data. For example, let’s say you wanted to add a Data Bar and an Icon Set to the same series:

South

Tagged with: ,
Posted in General I.T. Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *

*