Skip to main content
Skip to main content
Microsoft 365

Icon Set Improvements in Excel 2010

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 image to group values, however we want to use a different icon image instead of the red flag image 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 image 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!

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365
• 5 min read

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards
• 5 min read

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting
• 6 min read

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…