To explore the methods, we’ll use the following dataset, which represents some students’ scores on a class test, including the range of remarks.
Method 1 – Customizing Default Icon Sets of Excel Conditional Formatting
Steps:
- Select the data range where you want to apply the icons.
- Click Home > Conditional Formatting > Icon Sets > More Rules.
Will get a dialog box.
- Insert the values of the range in the Value boxes and select the Number format from the Type box.
- Select the preferred icon for every range from the Icon.
- Click on the drop-down button, and Excel will show all the icons for conditional formatting. I selected the Green Check Symbol for a value greater than or equal to 80.
- Select the other two icons for the other two ranges of data.
- Press OK.
Excel shows the selected icons beside the left side of the cells.
- To show only the icons in the cells,
- From the previous Edit Formatting Rule dialog box mark the Show Icon Only option.
It only shows the icons.
Read More: Excel Conditional Formatting Icon Sets Based on Another Cell
Method 2 – Inserting a Symbol to Add Custom Icon Sets in Conditional Formatting
Steps:
- Insert icons beside the remark column to show which icon is for which range
- Click: Insert > Symbols > Symbol.
- Select Windings font from the Font section after appearing in the Symbol dialog box.
- You will see many icons here. Select your desired one. I chose the Smile face for the first cell. Its character code is 74. We’ll need it while applying the formula.
- Press Insert.
- Do the same for the other two cells. You will get icons like the image below. The character code for a Neutral face is 75, and a Sad face is 76.
- Add another column beside the Score column. Insert the formula for the icon.
- Insert the following formula in Cell D5:
- Press ENTER.
- We used the Wingdings font in the Symbol operation so we’ll have to use the same font for the cell.
- Choose the Wingdings font from the font selection dropdown box.
- Use the Fill Handle tool to copy the formula for the other cells.
All our icons are according to the score.
To improve the icons’ appearance, use Conditional Formatting. We’ll set three different icons’ colors.
- Select the cells and click Home > Conditional Formatting > Highlight Cells Rules > Equal To.
A dialog box will open.
- To highlight the smile face icon, insert the following formula in the formula box:
- Select the color from the color selection box. I chose Green Fill with Dark Green Text for the smiley face icon.
- Press OK.
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 the Practice Workbook
You can download the free Excel workbook from here and practice.
Related Articles
- Excel Conditional Formatting Icon Sets More Than 3
- Excel Conditional Formatting Icon Sets Relative Reference
- Excel Conditional Formatting Icon Sets Based on Percentage
- How to Change Conditional Formatting Icon Set Color in Excel
<< Go Back to Icon Sets | Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!