How to Use Excel Conditional Formatting with Icon Sets

The Numbers-Only Dashboard

Your sales manager asks you to build a weekly performance report in Excel. The spreadsheet lists each salesperson’s name and their percentage of target achieved (e.g., John: 112%, Sarah: 87%, David: 45%). When the manager glances at this report, they have to mentally process each number to determine if it is good, bad, or average.

A far more effective approach is to add a visual indicator directly inside each cell. Imagine a bright green upward arrow next to John’s 112%, a yellow horizontal dash next to Sarah’s 87%, and a red downward arrow next to David’s 45%. The manager can now scan the entire report in two seconds and instantly identify who is thriving and who needs help.

Excel can add these icons automatically using a feature called Conditional Formatting with Icon Sets. You define the rules (e.g., above 100% = green arrow, 70-99% = yellow dash, below 70% = red arrow), and Excel inserts the correct icon into every cell in the column without any manual effort.

Applying an Icon Set

1. Highlight the column of data you want to decorate with icons (e.g., cells C2 through C20, containing the target percentages).
2. Go to the Home tab on the Excel ribbon.
3. Click Conditional Formatting.
4. Hover over Icon Sets.
5. A gallery of icon styles will appear. You will see options including directional arrows (green up, yellow right, red down), traffic lights (green, yellow, red circles), stars, and flag sets. Select the 3 Arrows (Colored) set.

Instantly, every cell in the highlighted range will display a small coloured arrow next to the number. Excel automatically divides the data into three equal segments and assigns the icons accordingly.

Customising the Thresholds

The default automatic thresholds are rarely what you need for a business report. Excel divides the data into equal thirds, but you likely have specific business rules (e.g., green = at or above 100% of target, not just the top 33%).

1. With the data still highlighted, click Conditional Formatting again.
2. Click Manage Rules…
3. Select the Icon Set rule you just created and click Edit Rule…

A detailed rule editor will open. You will see three rows, one for each icon (green arrow, yellow dash, red arrow), with dropdown menus and value fields.

4. Change the dropdown from “Percent” to Number.
5. Set the Green Arrow threshold: Value >= 1 (representing 100% or above, if your data is formatted as decimals) or Value >= 100 (if formatted as whole numbers).
6. Set the Yellow Dash threshold: Value >= 0.7 (representing 70%).
7. Everything below 0.7 will automatically get the Red Arrow.
8. Click OK twice.

Your icons now precisely match your business definitions of “good,” “acceptable,” and “needs attention.”

Showing Only the Icons (Hiding the Numbers)

For a very clean dashboard aesthetic, you might want to show only the coloured icons and hide the actual numbers entirely. The icons alone tell the story.

1. Open the rule editor again (Conditional Formatting > Manage Rules > Edit Rule).
2. Check the box at the bottom labeled “Show Icon Only.”
3. Click OK.

The numbers will vanish from the cells, leaving only the clean green, yellow, and red arrows. The underlying data is still there (formulas still reference it), but the visual display is purely iconic.

Conclusion

A wall of numbers forces the reader to think. A column of coloured icons tells the story instantly. By applying Excel Conditional Formatting with Icon Sets, you transform your performance reports into intuitive, scannable visual dashboards that any manager can read at a glance.