Excel Tutorial: Using Conditional Formats for Traffic Lighting

Simplicity is key when presenting statistics to management or customers. Data analysts are often tempted to put as many details as possible on the standard “one sheet” template; however, too many numbers on a page can cause confusion about where to initially focus. When reducing the number of statistics on a page is not an option and summary stats are not appropriate, traffic-lighting is a quick and effective method to highlight trends or draw attention to information that need to be addressed.

Pre-Requisites

• Microsoft Excel 2003 or later

Downloads

• Spreadsheet: Marital Status Data (Couples.xls)

The Scenario

You are given a spreadsheet of statistics detailing the number of couples in Canada’s provinces and territories. The sum totals are broken down the following ways:

• Married couples vs. common-law couples
• Couples with children vs. couples without children

Suppose you want to study what social/economic conditions cause an increase in common law couples with children. Because resources are limited, regions where at least 15% of couples have these attributes should be studied foremost. Remaining resources should be dedicated to regions where 5-15% of couples are common law with children. Regions under 5% should be ignored.

Adding Traffic Lights

For this example, we will use the following colors:
Green = 15% or greater
Yellow = 5% to 15%
Red = Less than 5%

Open the tutorial spreadsheet. The first table provides raw figures for the number of couples in each region. The first column represents the entire number of couples, while the remaining totals break the total down by sub-categories: married with children, married without children, common-law with children, and common-law without children.

The second table displays the subcategory totals expressed as a percentage of all couples in the region.

Click and drag the mouse to select the cells representing the first percentage value for common law couples with children (E23-E35).

Select Format –> Conditional Formatting from the menu.

In the Conditional Formatting window, ensure the leftmost dropdown box displays “Cell Value Is”. Click on the second drop down box and select less than.

Click on the text box on the right side of the upper row. Type 0.05 which represents 5% in decimal form.

Click on the Format button

In the Format Cells window, Click on the Patterns tab.

Click on the red color swatch so that the block under the title “Sample” is colored red. Click on the OK button. You have now defined that the selected cells to turn red if its value is less than 5%

Using the Add button to create blank conditions, repeat the previous steps to create two more conditions:

• If cell value is between 0.05 and 0.15 then set color to yellow
• If cell value is greater than 0.15 then set color to green

Click on the OK button when you are finished

Look at the highlighted cells. Each cell should be colored red, yellow or green depending on its value. Looking at the original scenario, the study should be conducted primarily in Quebec, Saskatchewan, Nunavut and the Northwest Territories. Secondary study should be conducted in every other region except Manitoba and British Columbia.

Next Steps

Traffic lighting a powerful method to summarize data that would otherwise be difficult to interpret. Excel allows for easy traffic lighting via conditional formats. This example was relatively simple in that we entered numeric values directly. More complex traffic lighting can be accomplished by assigning conditional values to existing cells, which can in turn be dynamically calculated. Conditions can also be calculated based on formulas rather than numeric values or single cells.