How to Use Conditional Formatting Icon Sets in Excel for Visual Dashboards

Conditional formatting in Excel can do more than change cell colours. The Icon Sets feature automatically places small visual indicators — arrows, traffic lights, flags, stars, or bar charts — inside cells based on their values. This makes it easy to scan large datasets and instantly identify trends, performance levels, or status categories without reading every number. Icon sets are particularly useful for dashboards, KPI reports, and any spreadsheet where you need to communicate data visually.

Applying a Built-In Icon Set

To apply an icon set to a range of cells:

1. Select the range of cells containing numeric data (e.g., B2:B50).
2. Go to Home > Conditional Formatting > Icon Sets.
3. Choose an icon set from the gallery. Options include:
   • Directional arrows — Green up, yellow sideways, red down.
   • Traffic lights — Green, yellow, red circles.
   • Shapes — Green circle, yellow triangle, red diamond.
   • Flags — Green, yellow, red flags.
   • Stars and ratings — Quarter, half, three-quarter, and full stars.
   • Bar indicators — Signal strength style bars.

Excel automatically distributes the icons across the data range based on percentile thresholds. By default, a three-icon set uses the 67th and 33rd percentiles as breakpoints.

Customising Icon Set Thresholds

The default percentile-based thresholds rarely match real-world requirements. For example, if you are tracking project completion percentages, you might want green for 80–100%, yellow for 50–79%, and red for 0–49%. To customise the thresholds:

1. Select the cells with the icon set applied.
2. Go to Home > Conditional Formatting > Manage Rules.
3. Select the icon set rule and click Edit Rule.
4. In the Edit Rule dialog, change the Type dropdown from “Percent” to Number.
5. Set the values:
   • Green icon: when value is ≥ 80
   • Yellow icon: when value is ≥ 50 and < 80
   • Red icon: when value is < 50
6. Click OK.

You can also change the type to Formula for dynamic thresholds that adjust automatically based on other cells in the spreadsheet.

Showing Only Icons Without the Numbers

In some dashboards, you may want to display only the icons and hide the underlying numbers to create a cleaner visual layout:

1. Open Manage Rules > Edit Rule for the icon set.
2. Tick the checkbox labelled Show Icon Only.
3. Click OK.

The cell values are still stored and can be referenced in formulas — they are simply hidden from view. This is useful for status columns where the icon itself communicates everything the reader needs to know.

Reversing the Icon Order

By default, Excel assigns the “positive” icon (green) to the highest values and the “negative” icon (red) to the lowest. If your data uses a scale where lower is better (for example, response time in seconds or error count), you need to reverse the order:

1. Open Manage Rules > Edit Rule.
2. Click the Reverse Icon Order button.
3. Click OK.

Now the green icon appears for the lowest values and the red icon for the highest, correctly representing data where lower numbers indicate better performance.

Mixing Icons from Different Sets

You are not limited to using icons from a single set. If you want green ticks for good values, yellow exclamation marks for warnings, and red crosses for critical values, you can mix and match:

1. Open Manage Rules > Edit Rule.
2. Click the dropdown arrow next to each icon.
3. Select an icon from any available set.
4. Click OK.

This flexibility allows you to create highly customised visual indicators that match your organisation’s reporting standards.

Using Icon Sets Based on Another Cell’s Value

Sometimes you want to display an icon in one cell based on a value in a different cell. For example, you might want a status icon in column C based on the score in column B:

1. In column C, enter a formula that references column B (e.g., =B2).
2. Apply the icon set to column C.
3. Enable Show Icon Only to hide the duplicated number.

This creates a dedicated status column that visually summarises data from another column, keeping your raw data and visual indicators separate.

Practical Use Cases for Icon Sets

• Sales performance dashboards. Green arrows for reps exceeding target, yellow for on-track, red for below target.
• Project status tracking. Traffic lights showing which deliverables are complete, in progress, or overdue.
• Student grade reports. Stars showing performance bands across different subjects.
• Inventory monitoring. Flags highlighting stock levels that are healthy, low, or critically low.
• Budget tracking. Directional arrows showing which departments are over, on, or under budget.
• Server uptime monitoring. Signal bars representing system availability percentages.

Removing Icon Sets

To remove icon set formatting without deleting the cell values:

1. Select the cells.
2. Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

The icons disappear and the underlying numbers remain unchanged. Any other conditional formatting rules applied to those cells are also removed, so apply this selectively if you have multiple formatting rules active.