Business Intelligence: Stoplights

One of the new and exciting capabilities in Excel 12 is the ability to display indicators that reflect your organization's performance. For example, you could show a stoplight with red, yellow, and green lights that represent performance that falls below expectations, approaches expectations, or meets expectations. Such displays are the backbone of executive dashboards, which enable managers to ascertain an organization's relative performance at a glance.

Excel 2003 and previous versions of Excel don't have the built-in capability to display stoplights or similar icons, but you can approximate stoplights by using conditional formats.

You can create two types of conditional formats: formats that rely on the value in the cell to which the value is applied, or a format that relies on the result of a formula. Most books only cover the former variety of conditional format, but changing the color of a cell's contents can make the values harder to read. The second type of conditional format lets you create formulas that format a cell based on the contents of any cells in the workbook.

In this procedure, you'll format a cell to simulate a stoplight indicator.

1. Run Excel and type 2004 in cell B2 and 2005 in cell C2. Select cells B2:C2 and format them as bold and centered.

2. Type 245000 in cell B3, 275000 in cell C3, 300000 in cell B4, and 320000 in cell C4. These values represent sales for the years 2004 and 2005.

3. Drag the right border of column D until it is 17 pixels wide (you'll see the width appear as a ToolTip).

4. Select cell D3 and then choose Format | Conditional Formatting. The Conditional Formatting dialog box appears.

5. Click the down arrow in the first list box and choose Formula Is; then type =C3<(B3+(B3*0.05)) in the text box on the top row. This formula checks to see if the 2005 sales improved 5% or less over 2004.

6. Click the Format button to display the Format dialog box. Click the Patterns tab, click the Red square, and then click OK.

7. Click the Add button to create a space for a new conditional formatting rule.

8. Click the down arrow in the first list box and choose Formula Is; then type =C3<(B3+(B3*0.1)) in the text box on the top row. This formula checks to see if the 2005 sales improved by more than 5% but  less than 10% over 2004.

9. Click the Format button to display the Format dialog box. Click the Patterns tab, click the Yellow square, and then click OK.

10. Click the Add button to create a space for a new conditional formatting rule.

11. Click the down arrow in the first list box and choose Formula Is; then type =C3>=(B3+(B3*0.1)) in the text box on the top row. This formula checks to see if the 2005 sales improved by 10% or more over 2004.

12. Click the Format button to display the Format dialog box. Click the Patterns tab, click the Green square, and then click OK.

13. Click the OK button to apply your conditional formatting rules.

Cell D3 should now be colored green and cell D4 should be colored yellow. Try changing the value in cell C4 to 300000. When you do, cell D4 will change to red.