How to Build a Traffic Light Dashboard in Excel (with Sparklines)

The build, stage by stage

You’ll build a sales dashboard where each product gets a traffic light (red, yellow or green) based on how its sales compare with the average, plus sparklines showing each product’s trend. Pick a month from a drop-down and everything recalculates up to that month. The workbook has two sheets, Dashboard and Calculations. On the Dashboard, four products sit in B3:B6, with monthly sales in C3:N3 down to C6:N6 and the month names in C2:N2.

1. Add a month drop-down

Select B9 and go to Data > Data Validation > List, with the month names in C2:N2 as the source.

2. Highlight the selected month

Select C2:N2 and add a conditional formatting rule: Format only cells that contain, Cell Value equal to =$B$9. In Format > Fill > Fill Effects, choose a two-colour gradient (the video uses red as the second colour).

3. Total sales up to the chosen month

In O3, add up each product’s sales from January to the month in B9:

=SUM(OFFSET(C3,0,0,1,MATCH($B$9,$C$2:$N$2,0)))

MATCH finds the position of the chosen month (1 for January, 2 for February and so on). OFFSET then builds a range that starts at C3 and is that many columns wide, and SUM adds it up. Copy it down for the other products. Choose February, for example, and the total covers January and February only.

4. Set up the calculations sheet

On Calculations, list the products in column B and link each total in column C to the Dashboard (=Dashboard!O3, copied down). Then add:

• Average (C8): =AVERAGE(Dashboard!O3:O6)
• Low (C9): the average × 0.8 (80%)
• High (C10): the average × 1.2 (120%)

5. Write the light formulas

Each light shows an = sign when it should be on, and nothing otherwise:

• Red (D3), sales at or below the low value: =IF(C3<=$C$9,"=","")
• Yellow (E3), between low and high: =IF(C3<$C$10,IF(C3>$C$9,"=",""),"")
• Green (F3), at or above the high value: =IF(C3>=$C$10,"=","")

Copy them down, colour each column’s font red, yellow or green, then set D3:F6 to the Webdings font, which turns the = signs into round lights.

6. Design the dashboard

Insert an image of an empty traffic light and copy it so there’s one per product, aligned in a row. Add a rounded-rectangle title under each. Fill the main area grey with a dark grey frame, and turn off gridlines.

7. Put the lights into the traffic lights with the Camera tool

The Camera tool shows a live picture of a cell. Add it to the Quick Access Toolbar (More Commands > All Commands > Camera > Add). On the Calculations sheet, select D3, click Camera, and click on the Dashboard to place the picture. Remove its border and fill, and resize it to fit the first light. Copy it for the other lights, then update each picture’s formula in the formula bar so it points at the right cell for that product and colour.

8. Add sparklines

Under the lights, merge a few cells per product (for example C20:F20). Select C20 and go to Insert > Line Sparkline, with C3:N3 as the data range. Repeat for each product, and add Column Sparklines in a second set of cells for another view of the same data.

Now choose a month from the drop-down and watch the totals, lights and highlighted month all update together.