Conditional Formatting: Adding Customized Icon Sets to Excel 2007

转载 2011年01月21日 14:57:00

Conditional Formatting: Adding Customized Icon Sets to Excel 2007

Office 2007

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



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
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
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
Colored arrows indicate direction

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
Pie chart icons indicate 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.

Bb407306.note(en-us,office.11).gif 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


' 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
Applying icon set conditional rules to 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.


Excel条件格式化(conditional formatting)应用

条件格式化顾名思义就是根据条件对单元格进行格式化(填充,字体等)。 比如我们有一个学生成绩表,根据需要我们要把成绩按“不及格,60且90”进行颜色标注。...
  • claroja
  • claroja
  • 2016年12月09日 15:09
  • 989

【VSTO】创建 Excel 2007 AddIn (1. CommandBar 以及如何自定义Icon)


How to install Oracle Database Server software silently with customized listener configuration

Applies to: Oracle Universal Installer - Version: and later   [Release: 10.2 and later ] ...

Excel: Formatting

  • 2009年07月02日 08:12
  • 58KB
  • 下载

《Image-to-Image Translation with Conditional Adversarial Networks》论文笔记



  • 2010年01月25日 10:02
  • 1.62MB
  • 下载

Adding Classifications to Web Services.doc

  • 2009年03月10日 17:19
  • 21KB
  • 下载

Introduction to Probability (二) Conditional probability

Conditional probability Lecture objective Review 首先回顾下样本空间:是一次实验可能出现的所有结果,它内部的元素有互斥性:一次实验...
您举报文章:Conditional Formatting: Adding Customized Icon Sets to Excel 2007