Icon Sets

Icon sets in Excel are effective tools for us to visualize values in a range of cells easily. Each icon represents a range of values.

This tutorial is a part of our definitive guide on conditional formatting.

If you want to add an icon set, shape, or indicator, execute the following steps.

Steps to add Icons sets

We will use traffic light style shapes: green, yellow, and red traffic lights that indicate high, middle, or low values.

Step 1: Select a range, which contains data

In the example, you want to apply icon sets to the column ‘I’ to highlight low, middle, and high-priced products.

Select a range, which contains the data

Step 2: Go to the Home tab and select Conditional Formatting

how to use icon sets

Use the drop-down list to select Icons sets, then locate the Shapes Group.

Use the drop-down list to select your shape style

Step 3: Choose Icon Sets and click a subtype

You have various options for icons: Directional, Shapes, Indicators, and Ratings. Choose your favorite icon of these to fit the needs of your data.

Result:

excel icons sets to highlight data range

Explanation:

By default, for three shapes, Excel calculates the green-yellow-red dividers for 67% and 33%.

In this example, the minimum value is $4.70. The maximum value is $19.80

Above 67% (green shape) = min + 0.67 * (max-min) = 4.70 + 0.67 * (19.80 – 4.70) = 14.817

Below 33% (red shape) = min + 0.33 * (max-min) = 4.70 + 0.33 * (19.80 – 4.70) = 9.68

  • A green shape will show values equal to or greater than 14.81.
  • A yellow shape will show values greater than 9.68 and equal to or less than 14.81.
  • A red shape will show values less than 9.48.

Step 4: Change the cell values

Let’s modify the values in the highlighted range! Excel will update the icon set in real-time.

change cell values

Icon Sets Example

Select the range I2:I15

Locate the Home Tab on the Ribbon. Then, under the Styles group, click Conditional Formatting.

Choose Manage Rules!

manage a rule

Click the Edit rule:

click edit rule

Now you will see the ‘Edit Formatting Rule’ dialog box. Here you can change the icon styles and properties.

default shape style for icon sets

In the example below, you will change the formatting style. On the bottom-left area of the dialog box, use the drop-down menu to pick a new icon style, three traffic lights (rimmed).

how to edit formatting rule using traffic light icon sets

Under the Type section, change the actual value to Number.

Apply the following rules: If the value is greater or equal to 17, you’ll see a green shape, between 13 and 17, apply red.

If the value is less than 13, you will use the ‘No Icon’ options.

Result:

purchase price ordered icons

Download the practice file.