OfficeTalk: Conditional Formatting: Adding Customized Icon Sets to Excel 2007

Article
05/23/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Icon sets give you an opportunity to create visual effects in your data to see how the value of a cell compares with other cells. Learn how to implement and manipulate icon sets through the user interface and programmatically. (5 printed pages)

Frank Rice, Microsoft Corporation

April 2007

Applies to: 2007 Microsoft Office Suites, Microsoft Office Excel 2007

Contents

Overview
A Large Selection of Icon Choices
What You Can and Can Not Do with Icon Sets
Using Icon Sets Programmatically
To Summarize
More information about Conditional Formatting in Excel 2007

Overview

This column continues my discussion on conditional formatting in Microsoft Office Excel 2007 by introducing another visualization you can apply to your data to give it a little more punch - icon sets. As the name implies, this feature allows you to put icons in cells based on the values of the cell. Icon sets are similar in some ways to data bars and color scales; so much of the information that I covered in previous Office Talk columns applies to icon sets as well. For more information on these features, see More information about Conditional Formatting in Excel 2007. The primary differences are that:

You can specify a number of categories for the data (between three and five categories).
You can choose a set of icons that you want to appear in the cells for each category.
You can have Excel determine the categorization of each cell relative to all cells in the selected range so that it can draw the appropriate icon in the each of the cells.

This is very effective when you are trying to annotate and present data in a way that is quickly readable and comprehensible.

A Large Selection of Icon Choices

Excel 2007 offers several choices of icon sets. You can choose the icons that are most appropriate for the data you are using. Figure 1 shows the gallery of icons as well as the user interface (UI) to get to the icons. The Condition Formatting option is available on the Home tab.

Figure 1. Excel 2007 offers many sets of conditional formatting icons

Let's look at some examples. Figure 2 is an example of red-yellow-green icons in a range of data:

Figure 2. Red-Yellow-Green geometric icons applied to a range of data

Figure 3 is an example of a range of data with colored arrows in different directions, relative to the middle values in the range. Arrows tend to indicate trends:

Figure 3. Colored arrows indicate direction relative to mid-range values

The next example shows five different categories grouped using a circle that ranges from empty through filled (similar to the phases of the moon or a pie chart):

Figure 4. Icons display a pie chart-like indicator of percentage of fill

Like data bars and color scales, you can set the value for each of the different categories in an icon set using numbers, percent, percentiles, and formulas. By default, Excel uses percentiles. For example, in the three-icon case, Excel sets 33% and 67% as the break between the three sets of icons.

What You Can and Can Not Do with Icon Sets

Here are some answers to questions you may have about icon sets:

Icon sets come in three sizes, so as you increase or decrease the font size, the icon become larger or smaller, as appropriate.
You cannot add your own icons in this release of Excel. However, as you have seen, there are a large number of icons to choose from so this should not be an issue.
It is possible to hide the value of the cell and just draw the icon when you apply a conditional formatting rule for icon sets. This enables you to build all sorts of interesting "dashboards" in Excel 2007.
For people familiar with Microsoft SQL Server 2005 Analysis Services Key Performance Indicators (KPI), data imported into Excel is synched up with the graphics, so if you use Analysis Services KPIs in Excel 2007, when you apply conditional formatting rules, the correct icons are put into the cells automatically.
You can overwrite default behavior of the icon by using formulas. For example, for a growing portfolio, a negative net income might not be a true "red" as long as it is more than that outlined in a plan.

Using Icon Sets Programmatically

You can also specify icon sets and icon set criteria programmatically. Excel 2007 provides an IconSets collection containing the set of IconSet objects, an IconSet object that represents a single set of icons, and an IconSetCondition object that represents an icon set conditional formatting rule. There is also an IconCriteria collection, which represents the set of criteria for an icon set conditional formatting rule. Using the methods and properties associated with these objects provides a considerable amount of flexibility in setting conditions and criteria to make your data more meaningful to the user.

The following code example creates a range of numbers and then applies an icon set conditional formatting rule to that range. A 5-arrow icon set is chosen as the type of icon set. Finally, the threshold type is modified from percentile to a hard-coded number.

Note

An easy way to implement this code sample is to add a button to the Ribbon in Excel 2007. You can see the steps to do this in my previous Office Talk columns on conditional formatting.

Sub CreateIconSetCF()
Dim cfIconSet As IconSetCondition

' Fill cells from C1 to C12 with sample data.
With ActiveSheet
   .Range("C1") = 55
   .Range("C2") = 92
   .Range("C3") = 88
   .Range("C4") = 77
   .Range("C5") = 66
   .Range("C6") = 93
   .Range("C7") = 76
   .Range("C8") = 80
   .Range("C9") = 79
   .Range("C10") = 83
   .Range("C11") = 66
   .Range("C12") = 74
End With

Range("C1:C12").Select

' Create an icon set conditional format for the created sample data range.
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition

' Change the icon set to a 5-arrow icon set.
cfIconSet.IconSet = ActiveWorkbook.IconSets(xl5Arrows)

' The IconCriterion collection contains all the icon criteria. By indexing into
' the collection, you can modify each criteria. The following sections set
' the criteria for each of the arrows in the set.
With cfIconSet.IconCriteria(1)
   .Type = xlConditionValueNumber
   .Value = 0
   ' XlFormatConditionOperator enumeration that specifies "greater than 
   ' or equal to."
   .Operator = 7
End With
With cfIconSet.IconCriteria(2)
   .Type = xlConditionValueNumber
   .Value = 60
   .Operator = 7
End With
With cfIconSet.IconCriteria(3)
   .Type = xlConditionValueNumber
   .Value = 70
   .Operator = 7
End With
With cfIconSet.IconCriteria(4)
   .Type = xlConditionValueNumber
   .Value = 80
   .Operator = 7
End With
With cfIconSet.IconCriteria(5)
   .Type = xlConditionValueNumber
   .Value = 90
   .Operator = 7
End With

End Sub

Running this procedure results in the display shown in Figure 5.

Figure 5. The result of applying an icon set conditional rules to a range of data

To Summarize

Icon sets make it very easy to see at a glance how data values relate to each other as well as trends in your data. You have also seen how easy it is to add icon sets to your data by using the Excel 2007 conditional formatting UI as well as by adding them programmatically. Changing the types of icon sets and their thresholds is also very easy.

In future columns, I discuss other aspects of the new conditional formatting enhancements in Excel 2007, including the flexibility that is added to conditional formatting rules.

Share via