Consider the following dataset containing some students marks, with remarks on these marks. By using icon sets we can highlight these remarks easily. In the following examples, we will consider some different scenarios and apply these icon sets to specific texts.

We have used *Microsoft Excel 365 *version for this article; you can use any other version according to your convenience.

### Example 1 – Using Conditional Formatting with 3 Icon Sets Based on Text

Let’s apply **3 **icon sets based on the remarks *Satisfactory*, *Medium*, and *Poor*. We have added an extra column, ** Symbol**, and a table numbering the remarks to enable applying the icons easily.

#### Step 1 –Â Using VLOOKUP Function

- Enter the following formula in cell
**G4**:

`=VLOOKUP(F4,$B$16:$C$18,2,FALSE)`

Here, **F4 **is the **lookup value**, **$B$16:$C$18 **is the **lookup table**, **2 **is the **column index** number of this table, and **FALSE **is for an **exact match**. **The VLOOKUP function** will return the number for this **lookup value**.

- Drag down the
**Fill****Handle**to copy the formula to the cells below.

Numbers are added for the corresponding remarks.

#### Step 2 –Â Applying Conditional Formatting

Now we can add icon symbols depending on the values of the **Symbol **column.

- Select the range
**G4:G13.** - Go to the
**Home**tab >>**Conditional Formatting**dropdown >>**Icon Sets**dropdown >>**More Rules**.

The **New Formatting Rule **wizard will appear.

- Choose the
**Format all cells based on their values**option and select the following:

**Icon Style â†’ 3 Symbols (Circled)**

__Green Symbol
__

**Operator â†’ Greater than**

**Value â†’ 0**

**Type â†’ Number**

__Yellow Symbol
__

**Operator â†’ Greater than or Equal to**

**Value â†’ 0**

**Type â†’ Number**

- Press
**OK**.

The specified icons will appear in the **Symbol **column.

#### Step 3 – Formatting Cells

- To change the numbers to text, select the range
**G4:G13**and press**CTRL+1**.

The **Format Cells **dialog box opens.

- Under the
**Number**tab, select the category as**Custom**, and enter the following in the**Type**box:

`"Satisfactory";"Poor";"Medium"`

- Click
**OK**.

The text associated with the numbers appear beside the symbols.

Now we can remove the **Remarks **column because it is redundant.

- Copy and paste the contents of the
**Symbol**column as values in this column, so we can delete the**Remarks**column without affecting the**Symbol**column.

After deleting the **Remarks **column, the final look of our dataset is as follows:

**Read More: **Conditional Formatting with More than 3 Icon Sets in Excel

### Example 2 – Using Conditional Formatting with 5 Icon Sets Based on Text

Here, we have **5 **different remarks – *Excellent*, *Very Good*, *Satisfactory*, *Medium*, and *Poor*. So to apply icons based on these texts we need **5 **icon sets. Let’s apply them.

#### Step 1 –Â Using VLOOKUP Function

- Enter the following formula in cell
**G4**:

`=VLOOKUP(F4,$B$16:$C$20,2,FALSE)`

Here, **F4 **is the **lookup value**, **$B$16:$C$20 **is the **lookup table**, **2 **is the **column index** number of this table, and **FALSE **is for an **exact match**.

- Drag down the
**Fill****Handle**to copy the formula to the cells below.

Numbers for the corresponding remarks are added.

#### Step 2 –Â Applying Conditional Formatting

Now we can add the symbols depending on the values of the **Symbol **column.

- Select the range
**G4:G13.** - Go to the
**Home**tab >>**Conditional Formatting**dropdown >>**Icon Sets**dropdown >>**More Rules**.

The **New Formatting Rule **wizard will appear.

- Choose the
**Format all cells based on their values**option. - Select the
**Icon Style**as**5 Quarters** - Choose the operator
**Greater than or Equal to**,**Number**Type, and enter**5,4,3,2**serially for the first four quarters. - Click
**OK**.

The icons will appear in the **Symbol **column.

#### Step 3 –Â Formatting Cells

- To change the numbers to text, select the range
**G4:G13**. - Press
**CTRL+1**.

The **Format Cells **dialog box will appear.

- Under the
**Number**tab, select the category as**Custom**, and enter the following in the**Type**box:

`[=5]"Excellent"; [=4]"Very Good";Satisfactory"`

- Press
**OK**.

After applying this formatting we have ** Excellent **for

**5**,

**for**

*Very Good***4**, and

**for**

*Satisfactory***3**,

**2**, and

**1**. So we have to format the cells again to separate the cells with values

**2**and

**1**from

**3**(Satisfactory). To do this, we use

**Conditional Formatting**.

- Select the range
**G4:G13.** - Go to the
**Home**tab >>**Conditional Formatting**dropdown >>**New Rule**.

The **New Formatting Rule **dialog box will open up.

- Select the
**Format only cells that contain**option and choose the following in the**Format only cells with**boxes:

**Cell Value**

**Less than**

**3**

- Click on
**Format**.

The **Format Cells **dialog box will appear.

- Under the
**Number**tab, select the category as**Custom**, and enter the following in the**Type**box:

`[=2]"Medium";"Poor"`

- Click
**OK**.

The **New Formatting Rule **dialog box will open again.

- Click
**OK**.

Now we can remove the **Remarks **column because it is redundant.

- Copy and paste the contents of the
**Symbol**column as values in this column.

After deleting the **Remarks **column, the final look of our dataset is as follows:

**Read More: **Excel Conditional Formatting: Add Custom Icon Sets

### Example 3 – Using Conditional Formatting Icon Sets Based on Blank or Non-Blank Cells

Suppose we have some blank cells and non-blank cells in the dataset. Depending on the number of blank cells in a row we will apply an icon corresponding to that row in the **Symbol **column.

#### Step 1 – Using COUNTBLANK Function

- Enter the following formula in cell
**G4**:

`=COUNTBLANK(B4:F4)`

Here, **the COUNTBLANK function** will count the number of blank cells in the range **B4:F4**.

- Drag down the
**Fill****Handle**to copy the formula to the cells below.

The number of blank cells in each row are filled into the **Symbol **column.

#### Step 2 – Applying Conditional Formatting

Now we can add the symbols depending on the values of the **Symbol **column.

- Select the range
**G4:G13.** - Go to the
**Home**tab >>**Conditional Formatting**dropdown >>**Icon Sets**dropdown >>**More Rules**.

The **New Formatting Rule **wizard will appear.

- Choose the
**Format all cells based on their values**option and select the following:

**Icon Style â†’ 3 Traffic Lights (Unrimmed)**

__Green Symbol
__

**Operator â†’ Greater than**

**Value â†’ 0**

**Type â†’ Number**

__Yellow Symbol
__

**Operator â†’ Greater than or Equal to**

**Value â†’ 0**

**Type â†’ Number**

- Click
**OK**.

Let’s customize the color of the symbols.

- For the first symbol choose the
**Red**.

- For the last symbol choose the
**Green**.

- Enter the following as the options for these symbols:

__Red Symbol__

**Operator â†’ Greater than or Equal to**

**Value â†’ 2**

**Type â†’ Number**

__Yellow Symbol
__

**Operator â†’ Greater than or Equal to**

**Value â†’ 1**

**Type â†’ Number**

- Click
**OK**.

The icons will appear in the **Symbol **column, where **Green **indicates that there are no **Blank **cells, **Yellow **represents only **1 Blank **cell in that row, and **Red **is for **2 **or more **Blank **cells.

**Read More:** Excel Conditional Formatting Icon Sets Based on Another Cell

### Example 4 –Â Using VBA Code to Apply Icon Sets

Let’s apply a **VBA** code to insert icons resembling the ** Remarks**.

__Steps__**:**

- In cell
**G4**, enter the following formula to determine the value of the:*Remarks*

`=VLOOKUP(F4,$B$16:$C$18,2,FALSE)`

Here, **F4 **is the **lookup value**, **$B$16:$C$18 **is the **lookup table**, **2 **is the **column index** number of this table, and **FALSE **is for an **exact match**.

- Go to the
**Developer**tab >>**Code**group >>**Visual Basic**

The **Visual Basic for Applications** window will open.

- Go to the
**Insert**tab >>**Module**.

- Enter the following code in your created module:

```
Sub setting_symbol()
Dim rem_list As Range
Dim symbol As IconSetCondition
Set rem_list = Range("G4", Range("G4").End(xlDown))
rem_list.FormatConditions.Delete
Set symbol = rem_list.FormatConditions.AddIconSetCondition
symbol.IconSet = ActiveWorkbook.IconSets(xl3Symbols)
With symbol.IconCriteria(2)
Â Â Â .Type = xlConditionNumber
Â Â Â .Operator = xlGreaterEqual
Â Â Â .Value = 0
End With
With symbol.IconCriteria(3)
Â Â Â .Type = xlConditionNumber
Â Â Â .Operator = xlGreater
Â Â Â .Value = 0
End With
End Sub
```

**Code Breakdown****:**

`We name the`

`sub-procedure`

`as`

`setting_symbol`

`and declare the`

`rem_list`

`as a`

`Range,`

`and the`

`symbol`

`as`

`IconSetCondition.`

`We set the variable`

`rem_list`

`to be the range`

`G4:G13.`

`FormatConditions.Delete`

`will remove any pre-existing formatting in this range.`

`We set the variable`

`symbol`

`to be`

`FormatConditions.AddIconSetCondition.`

`xl3Symbols`

`specifies`

`3 Symbols`

`icon sets.`

`We use`

**the WITH statement**`twice to avoid repeating`

`IconCriteria(2)`

`and`

`symbol.IconCriteria(3).`

`For`

`IconCriteria(2)`

`we set`

`Type`

`as`

`xlConditionNumber,`

`Operator`

`as`

`xlGreaterEqual,`

`and`

`Value`

`<code>as`

`0.`

`Similarly, for`

`IconCriteria(3)`

`we choose`

`Type`

`as`

`xlConditionNumber,`

`Operator`

`as`

`xlGreater,`

`and`

`Value`

`as`

`0.`

- Press
**F5**.

The symbols are inserted.

By following **Step 03 **of **Example 1 **we can convert the numbers into their corresponding texts.

After copying and pasting as values in the **Symbol **column and deleting the **Remarks **column, our dataset looks as follows:

**Read More:** Excel Conditional Formatting Icon Sets with Relative Reference

**Download Workbook**

**Related Articles**

- Excel Conditional Formatting Icon Sets Based on Percentage
- How to Change Conditional Formatting Icon Set Color in Excel

**<< Go Back to Icon Sets | Conditional Formatting | Learn Excel**