Highlight operations performance with conditional formatting

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

Okay, so you do a great job tracking your operations data. In your spreadsheets and databases you have enough info to sink a ship. But… how easy is that data to read? Can you tell at a glance what departments are performing well and what areas need improvement? When managers or department heads want to review your data, does it make sense to them?

You can use conditional formatting (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.) in Microsoft Office Excel 2003 to quickly draw attention to important figures. You specify what conditions you want to highlight, and Excel takes it from there.

For example, if your policy is to ship in minimum quantities of 50 whenever possible, you can use conditional formatting to automatically show the orders that were at least 50 in green and those that were under 50 in red. The color coding makes it easy to scan the information and see areas that need attention.

Table with conditional formatting

In this article


Top of Page Top of Page

Add conditional formatting

  1. Select the cells for which you want to add conditional formatting. In the example, we selected the data in the units_shipped column.
  2. On the Format menu, click Conditional Formatting.
  3. In the Conditional Formatting dialog box, do one of the following:

 Note   If you enter a formula, start it with an equal sign (=).

  • To use a formula as the formatting criteria (to evaluate data or a condition other than the values in selected cells), click Formula Is and then enter the formula that evaluates to a logical value of TRUE or FALSE.
  1. Click Format.
  2. Select the formatting you want to apply when the cell value meets the condition or the formula returns the value TRUE.
  3. To add another condition, click Add, and then repeat steps 3 through 5.

In the example, there are two conditions defined for special formatting:

  • Condition 1: When the units_shipped cell value is greater than or equal to 50, the font is formatted in green, to show orders that met or exceeded the minimum order policy.
  • Condition 2: When the units_shipped cell value is less than 50, the font is formatted in red, to show orders that were below the minimum order policy.

You can specify up to three conditions. If none of the specified conditions are true, the cells keep their existing formats.

 Note   If you are using multiple conditions, Excel applies only the formats of the first true condition, even if more than one condition is true.

Top of Page Top of Page

Copy conditional formatting

  1. Select the cells that have the conditional formatting you want to copy.
  2. On the Formatting toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click Format Painter Button image.
  3. Select the cells you want to format, and then click Format Painter again.

Top of Page Top of Page

Change conditional formatting

  1. Select the cells that have the conditional formatting you want to change.
  2. On the Format menu, click Conditional Formatting.
  3. Click the format button for the condition you want to change, and then change the format.

Remove conditional formatting

  1. Select the cells for which you want to remove conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. Click Delete, and then select the check box for the conditions you want to delete.

Top of Page Top of Page

Information at a glance

Conditional formatting helps you to quickly identify policy compliance and enables you to highlight areas for improvement. With just a few clicks, you can clearly show managers and department heads how well your team is tracking against policies, goals, and metrics.

Top of Page Top of Page

 
 
Applies to:
Excel 2003