Conditional Formatting Icon Sets Based on Text in Excel

If you are looking for ways to use conditional formatting icon sets based on text in excel, then you are in the right place. By applying the icon sets we can easily determine what is the text for this cell after seeing only the icon. To do this job easily, let’s get into the main article.

How to Use Conditional Formatting Icon Sets Based on Text in Excel: 4 Examples

Here, we have the following dataset containing records of marks of some students with the remarks of these marks. By using icon sets we can highlight these remarks easily. In the following 4 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 3 Icon Sets Based on Text in Excel

In this section, we will apply 3 icon sets based on the remarks- Satisfactory, Medium, and Poor. To do this we have added an extra column- Symbol and added a table numbering the remarks to apply icons easily.

Step-01: Using VLOOKUP Function

• Apply 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. Then the VLOOKUP function will return the number for this lookup value.

• Drag down the Fill Handle

In this way, we have added the numbers for the corresponding remarks.

Step-02: Applying Conditional Formatting

Now, we will add the symbols depending on the values of the Symbol column.

• Select the range G4:G13 and then go to the Home tab >> Conditional Formatting dropdown >> Icon Sets dropdown >> More Rules.

After that, the New Formatting Rule wizard will appear.

• Choose the Format all cells based on their values option and select the following

• Press OK.

Then, the icons will appear in the Symbol column.

Step-03: Formatting Cells

• To change the numbers to texts, select the range G4:G13 and press CTRL+1.

Afterward, you will have the Format Cells dialog box.

• Under the Number tab, select the category as Custom, and enter the following in the Type box

• Press OK.

In this way, you will have the texts beside the symbols.

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

• Copy and paste the contents of the Symbol column as values in this column. By doing this our deletion of the Remarks column will not affect the Symbol column anymore.

After deleting the Remarks column we have the final look of our dataset like the below figure.

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

Example-2: Using Conditional Formatting 5 Icon Sets Based on Text in Excel

Here, we have 5 types of remarks as- Excellent, Very Good, Satisfactory, Medium, and Poor. So, to apply icons based on these texts we need 5 icon sets. And this example will show you the procedure of applying 5 icon sets.

Step-01: Using VLOOKUP Function

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

In this way, we have added the numbers for the corresponding remarks.

Step-02: Applying Conditional Formatting

Now, we will add the symbols depending on the values of the Symbol column.

• Select the range G4:G13 and then go to the Home tab >> Conditional Formatting dropdown >> Icon Sets dropdown >> More Rules.

After that, the New Formatting Rule wizard will appear.

• Choose the Format all cells based on their values option and select the Icon Style as 5 Quarters
• Choose the operator Greater than or Equal to, Number Type, and write down 5,4,3,2 serially for the first four quarters.
• Press OK.

Then, the icons will appear in the Symbol column.

Step-03: Formatting Cells

• To change the numbers to texts, select the range G4:G13 and press CTRL+1.

Afterward, you will have the Format Cells dialog box.

• Under the Number tab, select the category as Custom, and enter the following in the Type box

• Press OK.

After applying this formatting we are having 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 will use Conditional Formatting.

• Select the range G4:G13 and then go to the Home tab >> Conditional Formatting dropdown >> New Rule.

After that, 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.

Afterward, you will have the Format Cells dialog box.

• Under the Number tab, select the category as Custom, and enter the following in the Type box

• Press OK.

Then, you will be taken to the New Formatting Rule dialog box again.

• Press OK

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

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

After deleting the Remarks column we have the final look of our dataset like the below figure.

Read More: Excel Conditional Formatting: Add Custom Icon Sets

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

Here, 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-01: Using COUNTBLANK Function

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

In this way, we have got the number of blank cells in a row to the corresponding cells of the Symbol column.

Step-02: Applying Conditional Formatting

Now, we will add the symbols depending on the values of the Symbol column.

• Select the range G4:G13 and then go to the Home tab >> Conditional Formatting dropdown >> Icon Sets dropdown >> More Rules.

After that, the New Formatting Rule wizard will appear.

• Choose the Format all cells based on their values option and select the following

• Press OK.

Here, we will customize the color of the symbols.

• For the first symbol choose the Red

• For the last symbol choose the Green

• Now you can use the following options

• Press OK.

In this way, the icons will appear in the Symbol column where Green is indicating that there are no Blank cells, Yellow represents only 1 Blank cell in that row, and finally 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

In this section, we will apply a VBA code to insert icons resembling the Remarks.

Steps:

• Apply the following formula to determine the value of the Remarks in the Symbol

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

After that, you will get the Visual Basic for Applications window.

• Go to the Insert tab >> Module

• Use 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.

In this way, we have inserted the symbols.

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 we have got the following final figure.

Read More: Excel Conditional Formatting Icon Sets with Relative Reference

Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to use Conditional Formatting icon sets based on text in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

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