Conditional Formatting Icon Sets Based on Text in Excel

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:

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:

• 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:

• 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:

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:

• Press OK.

After applying this formatting we have Excellent for 5, Very Good for 4, and Satisfactory for 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:

• 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:

• 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:

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:

• 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:

• 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:

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

• 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