Highlight sales performance with conditional formatting

Try Office 2010 In Excel 2010, conditional formatting has been improved.
Watch a video or try Office 2010!

As a sales professional, you may find yourself sifting through large amounts of sales data in search of a few key numbers. Not only does that take time, but you risk overlooking significant information. In Microsoft Office Excel 2003, you can easily make the important data stand out by applying conditional formatting to your worksheet.

Conditional formatting enables you to quickly and easily color-code your information based on parameters that you set. Highlight top sales numbers in green, or call attention to weak performance in red. Conditional formatting saves you time by emphasizing exactly what you want to see.

Compare sales figures

Imagine yourself examining a worksheet of fourth quarter sales numbers by region. Wouldn't it be helpful to have your highest and lowest performers automatically singled out from the rest? In the following worksheet, regions exceeding sales goals by more than 10% are highlighted in green. Regions falling behind show up in red.

Quarterly sales report by region

Add conditional formatting

  1. Select the cells to which you want to add conditional formatting. In our example, we selected cells E4 through E11.
  2. On the Format menu, click Conditional Formatting.
  3. In the Condition area, do one of the following:
    • To change the formatting based on a value, select Cell Value Is, select a condition from the drop-down menu, and then enter a number or a formula. If you enter a formula, start it with an equal sign (=).
    • To change the formatting based on a formula that evaluates to TRUE or FALSE, select Formula Is, and then enter a formula.

In our example, we chose Cell Value Is, selected the condition less than, and entered 0.

  1. Click Format.
  2. Select the formatting you want to apply when the cell value meets the condition that you specified, or when the formula value returns TRUE.

In our example, we changed the text color to red.

  1. Click OK.
  2. To add another condition, click Add, and repeat steps 3 through 6.

In our example, we added a second condition stating that if the Cell Value Is  greater than  10, the text will be formatted as bold and green.

Conditional Formatting dialog box

You can specify up to three conditions. If none of the specified conditions are true, the cell formatting doesn't change.

 Note   If you are specifying multiple conditions, Excel only applies the formatting associated with the first true condition, even if more than one condition is true.

  1. Click OK.

Information at a glance

In the preceding example, Condition 1 draws attention to regions that are not meeting last year's numbers, and Condition 2 highlights regions that show a sales increase of over 10%. Now it only takes a quick glance to identify which regions are excelling, and which ones have room for improvement.

Top of Page Top of Page

 
 
Applies to:
Excel 2003