You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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!
Comments: (9) Collapse
"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."
I'm glad to see that Microsoft recognizes the issues surrounding the use of icon sets. With too many icons competing for the user's attention, the brain suffers from visual overload, making it difficult to pinpoint the real trouble spots in the data. Since In-cell charts...er, sparklines don't always provide sufficient context for the data, I've found the new icon set behavior useful for drawing attention to sparklines that may need further investigation (using icons in cells adjacent to the sparklines).
The problem that remains is, out of the box, the UI does not encourage the best use of icon sets. All of the options on the flyout menu are designed to litter the entire data range with icons. The problem is further underscored by the fact that in the New Formatting Rule dialog box, if you customize the icon set and then change the rule type, the custom set (which may include "No Cell Icon" for some rules) reverts to a multiple icon set.
Finally, I've had problems with icons not displaying after setting rules. In one case, I used formula rules where the formulas were based on defined names. The icons didn't show up as expected. After reselecting the range and choosing Manage Rules, the icons suddenly appeared.
I like the improvements made for icon sets. This will definitely improve the use of tables. But I'm still missing options:
- Smileys as icons,
- customize your own icon sets (either using image files or WinDings font)
- Use icons in tables in PowerPoint (and maybe Word). No need to be able to apply them in the other programs, just keep them if an Excel table is copied into a slide.
Hi Amit
Nice post
I not test it but if we send the sheet in the body of the mail does it keep the icons now in 2010 ??
In 2007 it delete the icons
Been working with Excel for 15 years. I really hate the way 2007 looks and feels. I just un-installed it and replaced it with Excel 2003. 2003 looks and feels more professional. If I wanted a lot of color and icons I would purchased a MAC.
Rob
Nice, but I still need an upward point arrow in Red and a downward pointing arrow in green.
Please!
Regards
Gary
Colin Banfield: "The problem that remains is, out of the box, the UI does not encourage the best use of icon sets" ... agreed, we want to improve on this and make more of the icon set options visible.
"he problem is further underscored by the fact that in the New Formatting Rule dialog box, if you customize the icon set and then change the rule type, the custom set (which may include "No Cell Icon" for some rules) reverts to a multiple icon set."
This is fixed in more recent builds.
"Finally, I've had problems with icons not displaying after setting rules."
Please send Joseph an email with the details and we can see whats going on.
Ute Simon (MVP): we really want to allow for custom icons, wasn't something we could get this time, but is near the top of the list.
Ron de Bruin: works like 2007 still...
Gary: Yeah, should be there. I'll talk to Amit about it but not likely for this version.
This is a real improvement over 2003. I use the Icon set (sparsely) and it does improve readability of the results. Two observations however:
1. Copying a table with conditionally formatted icons does not conserve them when pasting into Word (2007)
2. My major objection: Once a table (worksheet) has been formatted with icons, you have no clue what the cutoff values are. If I hand such a worksheet over to a colleague, I have to tell him "green icon means less than 15%, red icon means ...". Unless ofcourse he/she is clever enough to go for "conditional formatting, manage rules, ...", which I do not like him/her to do. (In fact, the same is true for all formatting rules)
What I would like is that If such Icon Formatting is applied, a Legend would would be created (very much like a chart legend) explaining the symbols. Or at least there is an option to create a Legend.
Totally agree with BEL8490's comments. These features would make it more useful. Often, Excel is used to 'prepare' data/charts for the presentation and the data is presented in Word/Powerpoint.
Also love to have double headed arrow () as part of Icon Set for depicting 'No Change'
Thanks for all your comments. These are great suggestions.
BELA8490 - Those are great suggestions. We will consider this for future versions.
Shashur- A follow-up questsion. Currently, Icon sets do not special case for zero values (but divides value in range into equal parts) unless you customize it. Can you elaborate on your "No Change" scenario?
Comments: (loading) Collapse