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.


Download Workbook


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

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.

excel conditional formatting icon sets based on text

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.

excel conditional formatting icon sets based on text


Step-01: Using VLOOKUP Function

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

  • Drag down the Fill Handle

excel conditional formatting icon sets based on text

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

3 icon sets


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.

excel conditional formatting icon sets based on text

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

3 icon sets

Then, the icons will appear in the Symbol column.

3 icon sets


Step-03: Formatting Cells

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

excel conditional formatting icon sets based on text

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
"Satisfactory";"Poor";"Medium"
  • Press OK.

3 icon sets

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

3 icon sets

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.

excel conditional formatting icon sets based on text

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

3 icon sets


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.

excel conditional formatting icon sets based on text


Step-01: Using VLOOKUP Function

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

5 icon sets

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

5 icon sets


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.

excel conditional formatting icon sets based on text

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.

5 icon sets

Then, the icons will appear in the Symbol column.

5 icon sets


Step-03: Formatting Cells

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

excel conditional formatting icon sets based on text

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
[=5]"Excellent"; [=4]"Very Good";Satisfactory"
  • Press OK.

5 icon sets

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.

5 icon sets

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

excel conditional formatting icon sets based on text

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
Cell Value
Less than
3
  • Click on Format.

5 icon sets

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
[=2]"Medium";"Poor"
  • Press OK.

5 icon sets

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

  • Press OK

5 icon sets

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.

5 icon sets

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

excel conditional formatting icon sets based on text


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.

excel conditional formatting icon sets based on text


Step-01: Using COUNTBLANK Function

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

blank or non-blank

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

blank or non-blank


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.

excel conditional formatting icon sets based on text

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

  • Press OK.

blank or non-blank

Here, we will customize the color of the symbols.

  • For the first symbol choose the Red

blank or non-blank

  • For the last symbol choose the Green

excel conditional formatting icon sets based on text

  • Now you can use the following options
Red Symbol
Operator → Greater than or Equal to
Value → 2
Type → Number

Yellow Symbol
Operator → Greater than or Equal to
Value → 1
Type → Number

  • Press OK.

blank or non-blank

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.

blank or non-blank


Example-4: Using VBA Code to Apply Icon Sets

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

excel conditional formatting icon sets based on text

Steps:

  • Apply the following formula to determine the value of the Remarks in the Symbol
=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.

VBA

  • Go to the Developer tab >> Code group >> Visual Basic

VBA

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

  • Go to the Insert tab >> Module

excel conditional formatting icon sets based on text

  • 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

Code Breakdown:

  • We have named the sub-procedure as setting_symbol and declared the rem_list as a Range, and the symbol as IconSetCondition.
  • Then we set the variable rem_list to the range G4:G13
  • FormatConditions.Delete will remove any pre-existing formatting in this range.
  • After that, we have set the variable symbol to FormatConditions.AddIconSetCondition
  • xl3Symbols is used for 3 Symbols icon sets
  • Then we used the WITH statement two times to avoid repeating IconCriteria(2) and symbol.IconCriteria(3)
  • For IconCriteria(2) we have set Type as xlConditionNumber, Operator as xlGreaterEqual, and Value as 0
  • Similarly, for IconCriteria(3) we choose Type as xlConditionNumber, Operator as xlGreater, and Value as 0

VBA

  • Press F5.

In this way, we have inserted the symbols.

VBA

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

VBA

  • After copying and pasting as values in the Symbol column and deleting the Remarks column we have got the following final figure.

excel conditional formatting icon sets based on text


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.

practice


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.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo