Excel 2013 Conditional Formatting Tutorial: Representing Data with Graphics

Page content

As an example, this article adds familiar traffic-style red/yellow/green lights to signal the hold-off, preparatory or go-ahead stages of product development. Each stage is linked to the current date, so each time you open the spreadsheet or refresh the data, Excel reassesses the date and makes any necessary changes to the icon.

1. Highlight the cells where the icons should appear.

In this example, the dates are scrambled, so you can’t quickly identify each stage. You could sort the data by date for manual vetting, but icons will quickly flag products and automatically compare dates to the current date.

2. Click Conditional Formatting from the Home tab’s Styles group, point to Icon Sets and select the icons you prefer.

In this case, traffic lights are chosen to indicate time-based action. Notice that the icons automatically appear in the selected data cells as you point to various sets. However, how the icons are configured might not conform to your needs, so verify the settings.

3. Click Conditional Formatting again and choose Manage Rules.

4. Select the Icon Set rule and click Edit Rule. If there is only one rule, it is automatically selected. If you deselected the Icon Set range, click Current Selection and choose This Worksheet to see the Icon Set rule.

5. Click Reverse Icon Order if you want the order of icons reversed.

The order originally displays green at the top, but it begins with red after clicking the Reverse Icon Order button, as shown in the next step. This was necessary, because the operators are limited to just >= or > and the example uses red for the latest dates.

You could also click the Icon buttons and manually choose the icon you want to configure.

6. Click the Type drop-down menu to the right of the first icon and select Formula. This enables you to create a dynamic formula for the icon. The other options include Number to specify a fixed value, Percent to choose a percentage of the value range and Percentile to use percentiles.

7. Enter the formula in the Value field, select the operator (>= or >) for each icon and then click OK. The last icon is the catchall; that is, values that don’t fit the previous rules are assigned the last icon.

In this case, the formula =TODAY()+30 is used for the first icon. Combined with the >= sign from the first drop-down option, it tells Excel to assign the red light to any date that is at least 30 days after the current date (Nov. 9, 2015). Likewise, the yellow light is assigned to any date after the current date and up until the red light is assigned. Finally, the green light is assigned to all other dates, which effectively means the go-ahead green light is assigned to the current date and older.

8. Click OK to apply the updated rule and close the Conditional Formatting Rules Manager.

The end result is therefore:

You can more easily see the distinct, dated stages after sorting the data by date: