“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 below 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 introduced the Camera object at least 20 years ago. But several Excel generations ago, Microsoft changed its name to linked picture. Even so, I usually call it the Camera object.
(Similarly, in Excel 2016, Microsoft changed the name Power Query to Data, Get & Transform Data. But most people still call it Power Query, or PQ for short.)
Excel’s Camera object is often the key to traffic-light reporting. 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.
In a new worksheet, enter text in cell C2. Add some color and other formatting as shown below. Then, with cell C2 selected, press Ctrl + C to copy that cell.
Select cell C4, and in the Home, Clipboard group, choose Paste, Linked Picture.
When you do so, Excel returns the linked picture object, as shown here. You can move this object anywhere on your worksheet.
If you change the formatting or text in cell C2, you’ll see that the linked picture updates immediately.
Now, with the linked picture selected, look at the formula in your formula bar. This is the range whose picture the linked picture 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 linked picture 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 to the cell you originally specified.
Unfortunately, when you cut and paste the linked picture between worksheets or workbooks, the cell reference doesn’t adjust as you expect. But this isn’t a problem. After you paste the linked picture to its new location, merely select the object’s formula in the formula bar and then select the range you want your linked picture 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.
