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.


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
  • 1305


转载请注明出处: java操作office文件现在主流的jar包有jxl和POI,由于目前jxl.jar没有人更新,并不支持Excel2007及其以上版本,Apache POI是Apache软件基金会...
  • xiaojimanman
  • xiaojimanman
  • 2014年04月02日 15:27
  • 2001

yolo-v1 train和test自己的分类和数据

一.参考文档 github下面有详细的yolo版本1的详细训练过程。 这里只介绍我们自己的训练过程(只识别“人”这一个类)...
  • u012235003
  • u012235003
  • 2017年01月16日 19:57
  • 698


import; import; import; import...
  • kaixuanfeng2012
  • kaixuanfeng2012
  • 2014年01月02日 13:38
  • 23803


相关jar包,自行去poi官网下载,其中包含了对于date类型的处理,其中该列必须在excel中设置为日期类型。 解析date 工具类: [java] view plain c...
  • zmx729618
  • zmx729618
  • 2016年05月05日 16:02
  • 1257

NPOI导入和导出EXCEL 支持2003/2007

================ * Introduction * ================ This project is the .NET port of POI project a...
  • u013538542
  • u013538542
  • 2017年03月04日 19:23
  • 1164


  • chuan_jing
  • chuan_jing
  • 2016年07月26日 15:06
  • 1696


  • u011271894
  • u011271894
  • 2017年03月29日 21:49
  • 1698

Java操作excel-兼容office 2007版本之后

前言 office2007版本有一次标准升级,最为明显的就是excel文件的文件名后缀(文件扩展名)由xls变成了xlsx,这是需要注意的地方,因为按照xls格式来处理xlsx格式的文件是无法操作的,...
  • bestcxx
  • bestcxx
  • 2017年04月19日 14:34
  • 861

NPOI 导入不了2007以上版本Excel

前段时间用在用NPOI导入Excel 的时候出现 异常  读取的方法是网上用的比较多的 using (FileStream file = new FileStream(filePath, Fi...
  • tanbo327
  • tanbo327
  • 2014年03月28日 11:03
  • 1048
您举报文章:Conditional Formatting: Adding Customized Icon Sets to Excel 2007