Thanks to Amit Velingkar, a Program Manager on the Excel team, for putting together the next few posts on conditional formatting.
In Excel 2007, we introduced Icon Sets – a new type of conditional formatting. The basic idea is that Excel draws an icon in each cell representing the value of the cell relative to the other cells in the selected range. Icons sets are a great way to create groups of similar data as a part of your data analysis.
In the example below, we have the historical Sales and Profit amounts for a fictional bookstore. The last column has the percentage of increase in profits when compared to the previous year. We can see that icons sets are a great way to visualize the most profitable years in the history of this bookstore.
We received a lot of requests from users who wanted to extend their icon sets. Some of the popular requests were:
1. The ability to customize icon set arrangements.
The ability to choose a different icon from those provided in the set is great if we want to convey a particular meaning associated with the icon. For example, we might want to use a 3 flag icon set to group values, however we want to use a different icon instead of the red flag to indicate a potentially dangerous situation.
2. Apply only a single icon to cells that meet the criteria.
This arrangement can be used for highlighting (a technique for bringing attention to a certain area of your spreadsheet). It is a recommended best practice that highlighting is most effective only when no more than 10 percent of values are highlighted.
Since icon sets in Excel 2007 apply icons to the entire range of cells, icon sets were not very effective as a highlighting tool. In the above example, we may want to only highlight the bottom set to highlight the potentially dangerous situation.
In Excel 2010, we have new features in icon sets that address both these issues. These changes can be accessed from the â€œEdit Formatting Ruleâ€ dialog for icon sets. You may notice that this dialog looks a bit different from Excel 2007.
The improvements in Excel 2010 are as follows
1. Visually Select Icon sets
The new â€œIcon Styleâ€ dropdown lets you visually select the icon sets that you want. No longer do you have to guess the icon sets by name.
2. Customize Icon Arrangements
Each icon now has a dropdown associated with it. To change an icon set arrangement, simply click on the dropdown and choose a different icon.
3. Hide Icon
Each icon dropdown has an entry called â€œNo Cell Iconâ€. Choose this option to hide the icons for cells that meet these specified criteria.
Going back to our example, we can highlight the years that are most unprofitable by choosing â€œNo Cell Iconsâ€ for the top 2/3â€™rd values in the range and choosing icon to indicate the least profitable years. Visually this looks as follows:
These improvements will allow for greater control over icon set arrangements. Let us know what you think. If thereâ€™s more youâ€™d like to see out of the icon sets feature in the future, leave a comment.
In other news, I am taking a two week vacation starting Friday, so the posts will be slow coming during that time. I will try to fit a couple posts in while Iâ€™m out. Donâ€™t go anywhere, Iâ€™ll be back soon!