MS Excel - Icon Sets in Excel

In Microsoft Excel, Icon Sets are a Conditional Formatting feature used to visually represent data using symbols like arrows, traffic lights, ratings, shapes, or flags. They make data easy to interpret at a glance, especially in dashboards and reports.

1. What Are Icon Sets in Excel?

Icon Sets apply small symbols to cells based on their values. These icons act as visual indicators for trends, performance, or status without needing complex analysis.

Example:

  • Green upward arrow → High value

  • Yellow horizontal arrow → Average value

  • Red downward arrow → Low value

2. Where to Find Icon Sets

Path:
Home → Conditional Formatting → Icon Sets

You’ll find multiple icon categories:

  • Directional Arrows → Up, down, left, right

  • Shapes → Circles, squares, triangles

  • Indicators → Traffic lights, flags, signs

  • Ratings → Stars, bars, checkmarks

  • Custom Icons → Create your own rules

3. Types of Icon Sets

A. Directional Icon Sets

Shows trends and movement:

  • 3, 4, or 5 arrows
    Example:

    • Green ↑ = Increase

    • Yellow → = No change

    • Red ↓ = Decrease

B. Shapes and Symbols

Uses colored shapes instead of arrows:

  • Circles (Green, Yellow, Red)

  • Squares or Triangles

  • Helpful for dashboards and KPIs.

C. Traffic Light Icons

Mimics stoplight signals:

  • Green light → Good performance

  • Yellow light → Needs attention

  • Red light → Poor performance

D. Ratings and Stars

Represents ranking or scores:

  • 5-rating system: Stars, bars, or boxes.

  • Commonly used for customer feedback, employee ratings, or product reviews.

E. Flags and Signs

Ideal for status tracking:

  • Green flag → Completed

  • Yellow flag → In progress

  • Red flag → Pending or at risk

4. How to Apply Icon Sets

Steps:

  1. Select the range of cells.

  2. Go to Home → Conditional Formatting → Icon Sets.

  3. Choose your preferred icon style.

  4. Icons are applied instantly based on Excel’s default rules.

5. Customizing Icon Sets

By default, Excel splits data into percentiles, but you can change this:

Steps to Customize:

  1. Go to Home → Conditional Formatting → Manage Rules.

  2. Select your rule and click Edit Rule.

  3. In the Edit Formatting Rule window:

    • Change Type:

      • Percent → Uses relative distribution.

      • Number → Set exact thresholds.

      • Formula → Fully customizable.

    • Adjust the Icon Ranges:
      Example for 3-arrow set:

      • Green Up Arrow → >=80

      • Yellow Side Arrow → >=50

      • Red Down Arrow → <50

    • Optionally, hide cell values and display only icons.

6. Advanced Icon Set Examples

A. Sales Performance Tracker

Rule:

  • Green ↑ → Sales ≥ $50,000

  • Yellow → Sales between $25,000 and $49,999

  • Red ↓ → Sales < $25,000

B. Attendance Status

Rule:

  • Green circle → Attendance ≥ 90%

  • Yellow circle → Attendance 70% – 89%

  • Red circle → Attendance < 70%

C. Customer Ratings

Rule:

  • 5 stars → Rating ≥ 4.5

  • 4 stars → Rating ≥ 3.5

  • 3 stars → Rating ≥ 2.5

  • 2 stars → Rating ≥ 1.5

  • 1 star → Rating < 1.5

7. Tips for Using Icon Sets

  • Use with Formulas → Combine with IF or VLOOKUP for dynamic icons.

  • Hide Numbers → Show only icons for clean dashboards.

  • Sort by Icons → Right-click on icons → Sort by Color/Shape.

  • Combine with Color Scales → For richer visualizations.

  • Limit Overuse → Too many icons can clutter your spreadsheet.

If you want, I can prepare a one-page Excel Icon Sets Cheat Sheet showing:

  • All icon types with visuals

  • Default thresholds

  • Custom rule examples

  • Pro tips for dashboards