Icon sets in Excel are effective tools for visualizing values in a range of cells easily. Each icon represents a range of values.
Steps to add Icon sets
Use the following steps to add an icon set, shape, or indicator. In the example, we apply traffic light style shapes: green, yellow, and red traffic lights that indicate high, middle, or low values.
#1. Select a range that contains data
In the example, you want to apply icon sets to the column ‘I’ to highlight low, middle, and high-priced products.
#2. Use Conditional Formatting
Use the drop-down list to select Icons sets, then locate the Shapes Group.
#3. Choose Icon Sets and click a subtype
You have various icon options: Directional, Shapes, Indicators, and Ratings. Choose your favorite icon of these to fit the needs of your data.
Explanation:
By default, Excel calculates the green-yellow-red dividers for 67% and 33% of the three shapes. In this example, the minimum value is $4.70, and 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.
#4. Change the cell values
Let’s modify the values in the highlighted range! Excel will update the icon set in real-time.
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!
Click the Edit rule:
Now, you will see the ‘Edit Formatting Rule’ dialog box. Here, you can change the icon styles and properties.
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).
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’ option.
Related articles: