Software

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

Give your data a little something extra: a visual representation in Excel of superlative values. Mary Ann Richardson shows how to use conditional formatting to mark values within particular ranges.

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:

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

Now, an orange X denotes all values above 100. An exclamation point denotes values equal to 100, and values below 100 remain unformatted.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox