You can use icon sets in Microsoft Excel to visually represent your data with arrows, check marks, flags, and other objects. It’s a great way to add visual interest and enhance your presentation of important spreadsheet data.
As with many concepts in Excel, an example will help you learn how this feature works!
Example: Using an Icon Set to Enhance Your Worksheet
Look at the following spreadsheet. It contains a list of employees and their salaries. We can utilize an icon set to visually indicate which employees have the highest and lowest salaries.
To use an icon set to make this information more visual, do the following.
- Select cells B2:B11.
- Click Conditional Formatting in the Home > Styles group.
A list of options appear. - Point to Icon Sets then make a selection from the list.
I chose 3 Traffic Lights (Unrimmed). Here is what my worksheet looks like, with the icon set applied.
Salaries with the green circles are in the top third of the range, yellow represents midrange salaries, and the red circles indicate the lowest salaries in the range.
These icons have a simple style that makes it easy for any spreadsheet user to understand how the salaries compare to one another.
Customize Icon Sets to Enhance Your Worksheet
You can customize any icon set to enhance the way information is displayed in your Excel worksheet. There are so many options in this app!
If you need to change how icons (or colors) are allocated, do this.
- Select cells B2:B11.
- Click Conditional Formatting in the Home > Styles group.
A list of options appear. - Click Manage Rules…
The Conditional Formatting Rules Manager dialog box appears. - Select Icon Set then click Edit Rule…
The Edit Formatting Rule dialog box appears.
Notice that a green circle is displayed when a salary is 67% or above for the highlighted range of values. Salaries in the 33% – 66% range are considered mid-range. Salaries less than 33% are in the lowest range.
You can easily enter different percentages, if you want to change how the icons are applied.
I include icon sets in my work all the time. It’s a feature that instantly makes any worksheet way more user friendly!
Keep It Simple: Only Show Icons
If you’re concerned about keeping peoples’ exact salaries confidential, you could select the Show Icon Only checkbox in the Edit Formatting Rule dialog box (pictured in the previous section) and click OK.
Here is how the sample worksheet would look with the Show Icon Only option selected.
How Many Icon Packs Does Excel Come With?
Excel comes with 20 icon sets containing various shapes and styles that you can adapt for your projects.
Think about it… you can create a new dashboard to display information without code or programming knowledge… is there anything this app can’t do?
Gotta love Excel!
Other Conditional Formatting Options
While this article is primarily about icon sets, I thought you might like to see some of the other conditional formatting options that are available for your worksheets.
Here is the same salary list formatted with different options. The list on the left has been formatted with Data Bars. The list on the right is using Color Scales.
What’s Next?
Icon sets can make it much easier for users to understand a worksheet – and there are many options you can customize! Just configure the settings in a way that will work best with your information.
If you’ve enjoyed learning about icon sets in Microsoft Excel, you might want to check out our complete guide to basic formatting in Excel.
See you next time!
