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.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
"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
The three examples shown here illustrate a simple form of traffic
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
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
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
|Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more.
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
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
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:
If you were to cut and paste this cell to Sheet2, Excel would paste
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
...to something like...
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:
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...
However, Excel names can be defined using formulas. For example, you
could define the name Test as:
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
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
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
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
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:
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.