Icons in conditional formatting help to understand the data at a glance. There are several icon sets in Excel. But in some cases, the icon sets may not be liked of feasible for the data. In this situation, we can set our own customized icon sets. you will learn two easy ways from this tutorial to add custom icon sets in Excel conditional formatting.
How to Add Custom Icon Sets in Excel Conditional Formatting: 2 Ways
To explore the methods, we’ll use the following dataset that represents some students’ scores on a class test including the range of remarks.
1. Customize Default Icon Sets of Excel Conditional Formatting
There are 4 different kinds of icon sets in Excel conditional formatting. But we can use any icon from any set by customizing them. But we can’t download or import more icons in it, there’s no way. So if you like to add particular icons from different sets then this is the only way.
Steps:
- Select the data range where you want to apply the icons.
- Next, click as follows: Home > Conditional Formatting > Icon Sets > More Rules.
And soon you will get a dialog box.
- Later, insert the values of the range in the Value boxes and select the Number format from the Type box.
- Then select the preferred icon for every range from the Icon Click on the drop-down button and excel will show all the icons of conditional formatting. I selected Green Check Symbol for the value greater than or equal to 80.
- Select the other two icons for the other two ranges of data.
- Finally, just press OK.
Now have a look, excel is showing the selected icons beside the left side of the cells.
- If you want to show only the icons in the cells then it’s possible of course. From the previous Edit Formatting Rule dialog box mark the Show Icon Only option.
Then see, it’s only showing the icons. So now at a glance, you can easily understand who is doing well on the test and who are doing bad rather than looking through their scores.
Read More: Excel Conditional Formatting Icon Sets Based on Another Cell
2. Insert Symbol to Add Custom Icon Sets in Conditional Formatting
Excel conditional formatting has a limited collection of icons and there’s no way to download or import icons so then there’s another tricky way by which we can set any custom icons. We’ll use the Symbol feature and formula using the IF function of Excel in this regard.
Steps:
- Firstly, we’ll insert icons beside the remark column to show which icon is for which range, click as follows: Insert > Symbols > Symbol.
- After appearing in the Symbol dialog box, select Windings font from the Font section.
- You will get a lot of icons here. Select your desired one from here. I want to set the Smile face icon for Good, Neutral face for Average and Sad face for Bad So I chose Smile face for the first cell. Its character code is 74. We’ll need it while applying the formula, so remember it.
- After that, press Insert.
- Do the same operation for the other two cells and then you will get the icons like the image below. The character code of Neutral face is 75 and for Sad face is 76.
- Next, add another column beside the Score column, here we’ll insert the formula for the icon.
- Insert the following formula in Cell D5–
=IF(C5>80,CHAR(74),IF(C5<60,CHAR(76),CHAR(75)))- Then hit the ENTER button for the output.
- Soon you will see that it returned L. the reason is, that the cell is in Calibri font and according to the Calibri font, L represents the smiling face. As we used the Wingdings font in the Symbol operation so we’ll have to use the same font for the cell.
- Just choose the Wingdings font from the font selection dropdown box and then you will get the icon.
- After that, use the Fill Handle tool to copy the formula for the other cells.
Here are our all icons according to the score.
Now we can give the icons a better look by using Conditional Formatting. We’ll set three different colors for the icons.
- Select the cells and click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.
Soon after, a dialog box will open up.
- To highlight the smile face icon, insert the following formula in the formula box-
=CHAR(74)- Then choose the color from the color selection box. I picked Green Fill with Dark Green Text for the smile face icon.
- Finally, just press OK.
Now see that all the smile face icons are highlighted with our selected color.
- Follow the same procedures to set the color for the other two icons. Use character code 75 for Neutral face and 76 for Sad face.
Read More: Excel Conditional Formatting Icon Sets Based on Text
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to add custom icon sets in Excel conditional formatting. Feel free to ask any question in the comment section and please give me feedback.
