Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles icon Excel Icon Sets – Excelchat

Excel Icon Sets – Excelchat

There is a special kind of conditional formatting where icons are displayed in cells based on a given condition.  Instead of formatting the cells by changing the fill color, text formats or border design, value icons are used to indicate the value of the cells.  

Figure 1.  Final result:  Icon Sets

How to use the icon sets?

Suppose we have below data on class attendance.  We want to add conditional formatting on the number of students present for the day.  

Figure 2.  Sample data

We follow these steps:

  • Select the cells we want to format, column C
  • Click Home tab > Conditional Formatting > Icon Sets

There are four types of icon sets: Directional, Shapes, Indicators and Ratings.  

Figure 3.  Icon Sets options

  • Select the Directional 3 Arrows

Three-Icon Set

Figure 4.  Output: Three-Icon Set

The icons of red, yellow and green arrows will instantly appear beside the values in column C.  For a three-icon set, the selected values will be divided into three parts.  The green upwards arrow will be assigned to the top one-third, the yellow sideward arrow to the middle values, and the red downwards arrow to the lowest one-third.  

Four-Icon Set

We can also use the four-icon set where values will be divided into four parts and each icon will be assigned to one part.  Figure 5.  Output: Four-Icon Set

Set the Rules

We can customize the formatting and set the rules that we want to apply.  Here are the steps:

  • Select the cells we want to format, column C
  • Click Home tab > Conditional Formatting > Icon Sets
  • Click More Rules

Figure 6.  New Formatting Rule dialog box

  • In the New Formatting Rule dialog box, set the following conditions
      • Select a Rule Type: Format all cells based on their values
      • Icon Style: 3 Symbols (Uncircled)
      • Icon Type: Number
      • Icon Value: >= 28, <28 and >=25, <25

 

 

 

  • Click OK

The new rule we have created will be applied to column C.  A green check symbol will be applied to values above 28, a yellow exclamation point to values 25 to 27, and a red “x” to values less than 25.  

Figure 7.  Output: Setting the Rules

When we check the Show Icon Only check box, the values will be hidden and the selected cells will show only the icons.  

Figure 8.  Show Icon Only check box

Figure 9.  Output: Show icons only

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc