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:

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

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

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:

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.

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 thesub-procedureassetting_symboland declare therem_listas aRange,and thesymbolas IconSetCondition.
We set the variablerem_listto be the rangeG4:G13.
FormatConditions.Deletewill remove any pre-existing formatting in this range.
We set the variablesymbolto beFormatConditions.AddIconSetCondition.
xl3Symbolsspecifies3 Symbolsicon sets.
We use the WITH statementtwice to avoid repeatingIconCriteria(2)andsymbol.IconCriteria(3).
ForIconCriteria(2)we setTypeas xlConditionNumber,OperatorasxlGreaterEqual,andValue<code>as0.
Similarly, forIconCriteria(3)we chooseTypeasxlConditionNumber,OperatorasxlGreater,andValue as0.