Management Reporting with Charts

How to Create Excel Traffic Lights with Charts and Text

Wouldn't it be useful if your reports could highlight exceptional performance automatically...using charts? In fact, Excel provides such outstanding traffic-lighting power.

"Traffic lights" are a common feature offered by software designed for management reporting and analysis.

Traffic lights provide at least two benefits. First, they alert readers to exceptional results that readers might otherwise miss. Second, they provide visual frosting that can make reports more interesting.

The three examples shown here illustrate a simple form of traffic lighting.

All three images show the same cell in an Excel worksheet. When sales rise Excel displays the green image. When sales fall Excel displays the red image. Otherwise, Excel displays the yellow image.

These images switch automatically. No macros are needed. And simple formulas determine which image is used.

This article explains how you can set up traffic lighting in your own Excel reports.

Introducing the Camera Object

Excel's Camera object is the key to traffic-light reporting. This tool has been a standard part of Excel for many years, but very few Excel users have ever used it.

The Camera object returns a picture of a given range, and a formula can specify that range. This simple description provides a hint about how I created the traffic lights above.

First, I set up three different cells with the colored background objects shown above. Then I wrote formulas that told the Camera object which of those three cells to return a picture of, based on the value in a designated cell.

Let's take these steps one at a time.

Experiment With the Camera object

The first step is to place the Camera object icon on one of your formula bars in Excel.

To do so, first choose Tools, Customize in Excel. In the Commands tab choose the Tools category. In the list of commands at the right side of the dialog box you'll find the Camera icon slightly below the mid-point of the list. Click and drag the icon to a convenient toolbar in your Excel workspace.

Let's experiment briefly with the Camera object.

In a new worksheet, enter text in cell B2. If you want, add some color and other formatting as shown. Then select cell B2 and click on the Camera icon. When you do so, the border around cell B2 turns into a moving marquee, and your mouse pointer turns into a cross.

Now click anywhere on your spreadsheet. When you do so, Excel returns the Camera object, as shown in the illustration above. You can move this object anywhere on your worksheet.

With the Camera object selected look at the formula in your formula bar. This is the range whose picture the Camera returns. This range can be one or more contiguous cells, and you can modify it manually, as you will see.

You can cut or copy and paste the Camera object to any other worksheet. When you do so, however, you'll notice one minor problem. When you paste a normal cell reference to some other worksheet, Excel automatically changes the reference to the form of an external reference. To illustrate, suppose you had this formula in Sheet1:

=$B$2

If you were to cut and paste this cell to Sheet2, Excel would paste this formula:

=Sheet1!$B$2

That is, Excel automatically adjusts the reference so that it continues to point at the cell you originally specified.

Unfortunately, when you cut and paste the Camera object object between worksheets or workbooks, the cell reference doesn't adjust as you might expect. But this isn't a problem. After you paste the Camera object to its new location, merely select the object's formula in the formula bar and then select the range you want your Camera object to reference. Doing so would change the object's formula from something like...

=$B$2

...to something like...

=[Book2]Sheet1!$B$2

Take a Short Detour Into Excel Ranges

So far, we've used the Camera object only with simple references, like =$B$2. However, for traffic lighting we need to use a formula that returns different references under different conditions. That is, we need the Camera object to have a reference that's something like:

=IF($A$1=1,$B$2,$B$3)

Here, if cell A1 contains the value of 1, we want the Camera object to return a picture of cell B2; otherwise, we want it to return a picture of cell B3.

Unfortunately, if you try to enter a formula like this for your Camera object, you'll get an Excel error message. But that's no problem, because we have an easy work-around: range names.

Most Excel range names in most Excel spreadsheets are very simple. Names are defined using simple references, like...

=Sheet1!$C$8

...or...

=Sheet1!$D$9:$D$12

However, Excel names can be defined using formulas. For example, you could define the name Test as:

=IF($A$1=1,$B$2,$B$3)

Try it. Choose Insert, Name, Define. Enter Test as the name. Then, in the Refers-to box, enter the formula above. (Excel will add the name of the active sheet to each cell reference; but it still will accept the formula.)

To test whether the named formula works as expected, enter the value 1 in cell A1. Then go to the range defined by Test. To do so, press the F5 function key, which launches the Go To dialog. Enter "Test" (without the quotes) in the Reference box. Then choose OK. Excel should select cell B2. Then, enter any other value in cell A1. This time, when you go to Test, Excel will select cell B3.

(By the way, when you press the F5 function key Excel won't display names like Test that have been defined using formulas. That's no problem. Merely enter the name in the Reference box.)

We took the side trip through range names because the Camera object can use dynamic range names, names that use formulas to return references. To illustrate, create a Camera object that references any range, say cell C5. Then, with the Camera object selected, change its formula in the formula bar from =$C$5 to =Test.

If you have defined Test as shown above, the Camera object should return a picture of cell B2 when cell A1 contains the value 1. Otherwise, the Camera should return a picture of cell B3.

Now, with the Camera object and dynamic range names in our toolkit, let's create some traffic lights.

Set Up the Traffic-Light Formulas

This figure illustrates our simple traffic light display.

Cell B2 has been named Score. It typically contains a formula that looks at the data we're displaying and then scores the results. A score of 1 returns green around the chart; 2 returns yellow; and 3 returns red.

The Score formula can be anything you want. Suppose, for example, that a cell named Growth contains the percentage that the most-recent quarter has grown from the previous quarter.

The following formula would return 1 if sales have grown by more than 2%, 3 if sales have fallen by more than 2%, and 2 otherwise.

=IF(Growth > 0.02, 1, IF(Growth <- 0.02, 3, 2))

For now, however, just enter a value of 1, 2, or 3 as your Score.

Define the range names Green, Yellow, and Red as =$B$14, =$B$16, and =$B$18 respectively, as shown in the figure. Define the range name Alert to contain this formula:

=CHOOSE( Score, Green, Yellow, Red)

Complete the Traffic Light

To complete the traffic light, first enter the images shown into cells B14, B16, and B18.  Use an Auto Shape from the Basic Shapes section, and a Text Box for the label. Assign any shade of color you want to the three objects.

In the Text Box, be sure to set a white font and to set no line and no shading.

Then set up the Camera object. To do so, choose any cell; click on the Camera icon; then click on any cell. With the Camera object selected, replace its formula in the formula bar with:

=Alert

This named formula returns a reference to cell B14, B16, or B18, depending on the Score value in cell B2.

By default, the Camera object has both Line and Fill settings. You need to remove them both. To do so, right-click the Camera object. Choose Format Picture. In the Colors and Lines tab, set the Fill Color to No Fill and the Line Color to No Line; then choose OK.

To complete the figure, you can create a chart as shown. To control the white space in the figure I made the chart and plot areas transparent. Then I positioned a white drawing object between the camera object and the chart, as shown in the figure.

To place the three objects in the proper order so that the correct object is on top, right-click on any of the objects, and then specify the Order setting from the menu shown in the list. Your choices are Bring to Front, Send to Back, Bring Forward, and Send Backward.

Using the Camera object in an Actual Report

The worksheet shown above is not an actual report, of course. It merely is a worksheet you can use to experiment with traffic lighting.

In a real report, you typically would have one sheet that contains the traffic light images and another sheet that contains your actual report. Each area of your report that needs a traffic light would use its own Camera object with its own variation of the Score cell and Alert formula.

With these tools to build on, there's virtually no limit to the types of traffic-light reports that you can create with Excel.