April 9, 2008

Use Excel’s conditional formatting to mark values that meet your target goals

Date: April 8th, 2008

Author: Mary Ann Richardson

Excel 2007’s data visualization feature automatically gives every cell in a range a color, icon, or data bar according to its value. If you are analyzing a small number of values, this makes it easy to see which ones met your target goals. But what if you are examining a list with 100 rows of data? In this case, you will want to use Excel’s conditional formatting feature to build rules for marking only the cells outside your target range of values.

For example, say you want to mark only those cells that have a value greater than 100 with an X. Follow these steps:

Select the range of data.
On the Home tab, in the Styles group, click Conditional Formatting, then click Icon Sets.
Click 3 Symbols (Uncircled).
Click Conditional Formatting, then click Highlight Cell Rules | Less Than.
In the Less Than dialog, enter 100.
Click the arrow in the With drop-down box and select Custom Format, then click OK twice.
Click Conditional Formatting, then click Manage Rules.
Click the Stop If True check order generic viagra box for the rule Cell Value < 100.
Click the Icon Set rule, then click the Edit Rule button.
In the Edit Formatting Rule dialog, click the Reverse Icon Order check box at the bottom of the dialog window.
Click the >= drop-down arrow of the first Value list and select >.
Click the Type drop-down arrow of the first Value list and select Number.
Click in the first Value text box and enter 100.
Click the Type drop-down arrow of the second Value list and select Number.
Click OK twice.