Conditional Formatting Icon Sets

Hi and welcome to this blog post on Excel Conditional Formatting Icon Sets. In this blog post, I’ll describe what Icon Sets are, describe how you can edit the Conditional Formatting rules associated with Icon Sets and finish up with a couple of examples.

If you’re not familiar with Excel Conditional Formatting then it worth reading my two-part series introducing Conditional Formatting by click here.

What are Icon Sets

Ok, let’s get started. Icon Sets are, not surprisingly, a bunch of Icons that can be displayed in cell based on the cells value. Excel has several built in Icon Sets that can be accessed by clicking the Conditional Formatting button on the Home tab of the ribbon, then clicking the Icon Set button as shown in the image below (both Windows and Mac have a similar looking menu).

Accessing Icon Set - Mac

As can be seen from the image above the Icon Sets are split into Directional, Shapes, Indicators and Rates. Depending on what you’re trying to achieve you might choose to use a different set of icons. Often there is an overlap when you can choose more than one type of Icon Set. If this is the case, then I suggest trying a bit of experimentation to find out which Icon Set you prefer and which most clearly gets across the information you’re trying to convey.

Icon Set Rule Settings

Each Icon Set has a group of rule setting that determines which Icon from the Icon Set is displayed in a cell. To edit the rule setting of an existing Icon Set you need to have the range of range cells selected then click the Conditional Formatting button on the Home tab of the ribbon then the Manage Rules. Once this is done you will be presented with the Mange Rules pop-up menu which look like the images below for Windows and Mac;

Manage Rule - Icon Set Windows

Manage Rule – Icon Set Windows

Manage Rules - Mac Icon Set

Manage Rules – Icon Set Mac

Click the rule you want to Edit in the Manage Rules menu and you will be presented with the Edit Formatting Rule menu. Depending on the Icon Set being used you will be presented with slightly different options. The images below (for both Windows and Mac) Edit Formatting Rules menu for the three colored arrow Directional Icon Set.

Editing Formatting Rule - Icon Set Windows

Editing Formatting Rule – Icon Set Windows

Edit Formatting Rule - Icon Set Mac

Edit Formatting Rule – Icon Set Mac

Highlighted in the images above (for both Windows and Mac) is a drop down menu. From this drop down menu you get set the type of rule (Number, Percent, Formula and Percentile) you want to use to determine the Icon from the Icon Set that will be displayed.

Examples

Ok, lets take a look at a couple of examples. For three examples we will use the same data but format the cells using a different Icon Sets. The data being used for these example is a set of sale figures for a 12 month period.

Example One

In this first example we’ll format the sales figures using 3 colored dots, Red, Amber and Green from the Shapes Icon Set. Lets assume that we want we the sales figures in the top 34 percentile (greater than or equal to 67 percentile) to be shaded Green. The next 33 percentile (greater than or equal 33 but less than 67 percentile) to be shaded orange. Finally, the bottom 33 percentile (less than 33 percentile) to be shaded red.

If your uncertain what percentile means, click here for a fuller explanation or here for a shorter version.

One of the advantages of using percentile to determine the Icon that is displayed is that you don’t need to know the cell values in advance.

Start by selecting the range of cells that you want to apply the Conditional Formatting to, in this case B2:B13 as highlighted in the image below.

Selected Range of Cells

Then, click the Conditional Formatting button on the Home tab of the ribbon, then click the Icon Set button and finally the three colored dots from the Shapes group of Icons as shown in the image below (both Windows and Mac menus look very similiar).

Icon Set Colored Dots

Once you do this Excel is going to apply a default rule. This isn’t what we want so we’re going edit the default rule that Excel has applied by following the instructions earlier in this blog post. Click the Conditional Formatting button on the Home tab of the ribbon, then click the Manage Rules button. Once this is done you will be presented with the Mange Rules pop-up menu (which look like the images below for Windows and Mac). Highlight the rule to be edited and click the Edit Rule button.

Edit Icon Set Rule - Windows

Edit Icon Set Rule – Windows

Edit Icon Set Rule - Mac

When you’ve clicked the Edit Rule button you’ll be presented with the Edit Formatting Rule menu as discussed earlier in the post. In the setting menus we’ll set the green Icon to display for the top 1/3 of the sales figures, then the amber Icon for the middle third, then finally the red Icon for the bottom.

In this menu you’ll notice other drop-downs menus that allow you to change the Icon Style and the actual Icon that is displayed on an individual basis.

Lets get going, select both drop-downs menu under the Type label and select Percentile. Then in the text box under the value label for the green Icon enter 67. You’ll notice that the text ‘when value is’ and the greater than or equals symbol ‘>=’ next to the text box. This means any value that is in the 67 to 100 percentile will have a green Icon.

Next, in the text box for the amber Icon enter 33.  You will also notice the text ‘when value is <67’ and the greater than or equals symbol ‘>=’ next to the text box. This means any value that is in the 33 to 66 percentile will have an amber Icon. Excel knows to start the text next with <67 because this is value you entered in the text box above.

The red icon text box does need anything filling out. Because there is only three icons we’ve already set the rules for the first two Icons everything else will display a red icons.

When you’ve finished the the Edit Formatting Rule menu should look like the images below (the Windows and Mac menus differ a bit here).

Edit Formatting Rule - Windows

Edit Formatting Rule – Windows

Edit Formatting Rule - Mac

Edit Formatting Rule – Mac

Click OK on the Edit Formatting Rule menu and the ranges of cells should know look like the image below.

Example One Complete

Example Two

For the second example we’re going to use the 3 colored arrows from the directional Icon Set show in the image below.

Colored Arrows Icon Set

Using the steps before, select the range of cells to apply the Conditional Formatting to. Then, click the Conditional Formatting button on the Home tab of the ribbon, then click the Icon Set button and finally the three colored arrows from the Directional group of Icons.

As before, Click the Conditional Formatting button on the Home tab of the ribbon, then click the Manage Rules button. Once this is done you will be presented with the Mange Rules pop-up menu. Highlight the rule to be edited and click the Edit Rule button. Once this is done you will be presented with the Edit Formatting Rule menu again.

This time, rather than selecting ‘Percentile’ from the Type drop-down menu, select ‘Number’.

Edit Formatting Rule Number - Windows

Edit Formatting Rule Number – Windows

Edit Formatting Rule Number - Mac

Edit Formatting Rule Number – Mac

Example Two Complete

Example Two Complete

If you’d like a copy of the example spreadsheet used in this blog post sign-up for our newsletter below.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit