Table of Contents
Icon Sets in Excel
Icon Sets in Excel are the sets of the different types of Icons, Shapes, Indicators, Directions which are used for visualizing the selected values by giving it different meanings to it. Icon Sets can be accessed by the Home menu ribbon’s conditional formatting drop-down list. There we will have different categories and all the icons there on the list have some purpose and use. For ups and downs in data, we can use Directions, for any indications use Shapes and Indicators and to rate anything prefer Ratings.
How to Use Icon Sets in Excel?
Using Icon Sets in Excel is easy and we can find the icon sets in the Home menu under Conditional Formatting styles group which is shown by using some examples.
Applying Indicator Icon Sets Using Conditional Formatting – Example #1
In Microsoft Excel there are icon sets with different shapes and Icon sets in excel are a unique kind of conditional formatting Here in this example we will see how to use indicator icon sets in Excel using conditional formatting.
Consider the below example which shows the purchase data of Nov-16 month.
Now in this example, we are going to apply indicator icon sets in excel using conditional formatting where our conditional formatting formula is as follows.
- Green Colour Icon: If the value is greater than equal to 811.
- Yellow Colour Icon: If the value is less than 811 and greater than equal to 250 percent.
- Red Colour Icon: If the value is less than 250 percent.
Now in this example, we will see how to display these icon sets in excel by following the below process.
- First, create a new column for displaying icon sets as shown below. Then next apply = (equal) sign in I2 and select G2 cell to get the value in the I2 cell for applying conditional formatting.
- Drag down the formula to all cells so that we will then get the values which are shown in the below screenshot.
- Next, select the full column name Icon Set and click on Conditional Formatting and select Icon Sets so that we will get the option to choose the icon shapes as shown below.
- Once we click on the icon sets in excel we will get various icons like Direction, Shapes, Indicators, Rating.
- Here in this example, we will choose the flag icon set indicators to display the output.
- In icon sets at the bottom, we can find “More rules” where we can apply conditional formatting over here.
- On top, we can see various format rules. Select the first format rule Format all cells based on their values
- In the above dialog box, we can see the format style, Icon style and Icon to be displayed. Now apply the conditional formatting as below so that we will get the icon sets to be displayed.
- Green Colour Icon: if the value is greater than equal to 811.
- Yellow Colour Icon: if the value is less than 811 and greater than equal to 250 percent.
- Red Colour Icon: if the value is less than 250 percent.
- Change the icon style to Flag and Apply the values and choose the Type as Number because by default excel will take the values as a percent. We have chosen type as number because the format rule is based on values and not on the percentage which is shown in the below screenshot.
- Click OK and we will get the below output as follows.
- In the above screenshot, we can see that icon set Flag indicators are displayed based on the condition we have applied.
- As we notice that icon sets are displayed along with the values. In order to display only the icon we need to select the option called Show Icon Only check box.
- Go to conditional formatting and choose icon sets so that we will get the below dialog box.
- In the above screenshot as we can see that checkbox is enabled for “Show Icon Only” so that it will remove all the values and display the output with only selected icons set which is shown as the output result below.
In the below result we can see the flag icon in column I, where we have applied condition to display as Green Flag: If the value is greater than or equal to 811 and Yellow Flag: if the value is less than 811 and greater than or equal to 250 and Red Flag: if the value is less than 250.
Applying Direction Icon Sets Using Conditional Formatting – Example #2
In this example for Icon sets in excel, we are going to apply Direction Icon Set in Excel by using the same condition formatting.
Consider the below example which shows sales data value for the month of May-18. Here in this example, we are going to check which product has sold out with the highest value using the icon set conditional formatting by following the below steps.
Now in this example, we are going to apply Direction icon sets (High & Low) using conditional formatting where our conditional formatting formula is as follows.
- Green Colour Icon: if the value is greater than equal to 45000.
- Yellow Colour Icon: if the value is less than 45000 and greater than equal to 43000.
- Red Colour Icon: if the value is less than 0.
Now in this example, we will see how to display these icon sets by following the below steps.
- First, create a new column for displaying icon sets as shown below.
- Then apply = (equal) sign in I2 column and select G2 cell to get the value in the I2 cell for applying conditional formatting.
- Drag down the formula to all cells so that we will then get the values which are shown in the below screenshot.
- Next, select the full column of Icon Sets and Click on Conditional Formatting and select icon sets so that we will get the option to choose the icon shapes as shown below.
- Once we click on the icon sets we will get various icons like Direction, Shapes, Indicators, Rating.
- Here in this example, we will choose the Direction icon set indicators to display the output.
- In icon sets at the bottom, we can find More rules where we can apply conditional formatting over here.
- Click on More Rules so that we will get the conditional formatting dialog box as follows.
- On top, we can see various format rules. Then select the first format rule Format all cells based on their values.
- In the above dialog box, we can see the format style, Icon style and icon to be displayed. Now apply the conditional formatting as below so that we will get the icon sets to be displayed.
- Green Colour Icon: if the value is greater than equal to 45000.
- Yellow Colour Icon: if the value is less than 45000 and greater than equal to 43000.
- Red Colour Icon: if the value is less than 0.
- Change the icon style to Direction icon and Apply the values and choose the type as a Number because by default excel will take the values as percent. We have chosen type as number because the format rule is based on values and not on the percentage which is shown in the below screenshot.
- Click OK and we will get the below output as follows.
In the above screenshot, we can see that icon set Direction indicators are displayed based on the condition we have applied.
- As we notice that icon sets are displayed along with the values. In order to display only the icon, we need to select the option called Show Icon Only checkbox.
- Go to conditional formatting and choose icon sets so that we will get the below dialog box.
- In the above screenshot as we can see that checkbox is enabled for Show Icon Only so that it will remove all the values and display the output with only selected icons set which is shown as the output result as follows.
In the below result we can see the flag icon in column I where we have applied condition to display as Green: If the value is greater than or equal to 45000 and Yellow: If the value is less than 45000 and greater than or equal to 43000 and Red: if the value is less than 0.
Things to Remember
- Icon Sets in Excel will work only if we apply correct conditional formatting.
- While using Excel Icon Sets, apply exact formatting rules or else excel will throw an error dialog box.
- Icon Sets are mostly used to indicate the values in a graphical manner.
Recommended Articles
This has been a guide to Icon Sets in Excel. Here we discussed how to use Icon Sets in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
