Conditional Formatting with Icon Sets in Excel (Easy Guide)

Conditional Formatting with Icon Sets in Excel offers a simple way to turn raw data into visual indicators. Whether you’re showing performance levels, progress tracking, or status checks, icon sets help you communicate information clearly without using extra words or charts.

In this guide, we will explain how to use, customize, and apply icon sets in Excel, including how to base them on values from other cells. Let’s walk through each step.

What Are Icon Sets in Excel?

Icon Sets are one of the conditional formatting tools that allow you to place symbols in cells based on their values. These symbols can include:

• Arrows
• Flags
• Shapes
• Check marks
• Ratings (stars, bars, etc.)

They automatically group your data into categories (typically 3 to 5 levels), helping you spot trends or conditions quickly. These icons adjust based on value comparisons within a range.

How to Apply Icon Sets in Excel

Let’s look at how to use Excel’s built-in icon set formatting in a worksheet.

Step 1: Select the Range

Click and drag to highlight the cells you want to format with icon sets. This could be a column of scores, percentages, or totals.

Step 2: Open the Conditional Formatting Menu

• Go to the Home tab in the Excel ribbon.
• Click Conditional Formatting in the ‘Styles’ group.

Step 3: Choose an Icon Set

• Hover over Icon Sets.
• Choose a category like:
  • Directional (arrows)
  • Shapes (traffic lights, circles)
  • Indicators (flags, signs)
  • Ratings (stars, bars)

Once selected, Excel will apply the icon set based on the default rules.

Customizing Icon Set Rules

Excel uses default thresholds, but you can change them to better fit your data or use case.

How Excel Sets Defaults

By default, Excel splits the range between the lowest and highest value into equal bands, using Percent thresholds:

• 3 icons: top icon at 67% of the range or more, middle at 33% or more
• 4 icons: 75%, 50% and 25%
• 5 icons: 80%, 60%, 40% and 20%

These are percentages of the value range, not equal numbers of cells. One very high or low value can push most cells into the same icon, which is why setting your own Number thresholds is usually better.

But this default behavior doesn’t always fit what you need.

Step-by-Step Customization

1. After selecting your icon set, open:
   Conditional Formatting > Manage Rules > Edit Rule
2. In the dialog box, adjust:
   • Type (Number, Percent, Formula, or Percentile)
   • Values/Thresholds
   • Icons (you can change or remove them)

Example: Custom Thresholds

You can apply the rules using “More Rules” and choosing “Number” as the type.

Show Icon Only

To display only the icon (and hide the cell value):

• Check “Show Icon Only” in the rule settings.

Using Icon Sets Based on Another Cell’s Value

Excel doesn’t directly allow icon sets to reference another cell. But you can emulate this behavior with a helper column.

Example: Flag Icon Based on Payment Status

Suppose column D has payment dates.

In column A, enter a formula like:

=IF($D2<>"", 3, 1)

This returns:

• 3 if paid (cell has a date)
• 1 if not paid (cell is empty)

Now, apply an icon set to column A.

Check “Show Icon Only” so only the flag shows in column A.

This is useful for dashboards or overviews that reflect status indicators.

Creating Custom Icon Sets Using Formulas and Symbols

If the built-in icons don’t fit, a formula can return a symbol directly. UNICHAR (Excel 2013 and later) works with any normal font, so there is no Wingdings guesswork:

=IF(C5>80, UNICHAR(10004), IF(C5<60, UNICHAR(10008), "!"))

Symbol Codes

Color the symbols with ordinary conditional formatting rules, for example green font when C5 > 80. If you prefer the Wingdings font, the check mark is CHAR(252) and the cross CHAR(251); letters like J, K and L show faces in Wingdings, not checks.

Common Use Cases for Icon Sets

Icon sets aren’t just for numbers. They are highly useful in tracking different types of data visually.

1. Performance Indicators

For example, categorize employee performance as:

2. Project Progress Tracking

This helps highlight progress visually across tasks or team members.

3. Risk Assessment

Use arrows or signs to show the direction of risk:

• Up arrow for improving
• Side arrow for stable
• Down arrow for declining

4. Status Checks

Show yes/no or complete/pending conditions using:

• Green check for done
• Red cross for not done

How to Combine Icon Sets with Other Conditional Formatting Rules

You can layer icon sets with:

• Color scales for gradients
• Data bars for progress bars
• Text rules (e.g., format if text contains “urgent”)

This makes your Excel file more dynamic and readable, especially in dashboards and reports.

Summary Table: Quick Steps and Features

Here’s a snapshot of the key steps and how they work:

Final Thoughts

Icon sets in Excel are a fast and effective way to communicate the status, trends, or performance of your data. By using custom thresholds, helper columns, and symbol-based formulas, you can go beyond the basics and build visuals that bring clarity to your spreadsheets.

For best results, combine icon sets with clear labels, consistent formatting, and a solid understanding of your data goals.

FAQs