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.
=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
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
Green Symbol
Operator → Greater than
Value → 0
Type → Number
Yellow Symbol
Operator → Greater than or Equal to
Value → 0
Type → Number
- 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
"Satisfactory";"Poor";"Medium"
- 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.
=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
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
[=5]"Excellent"; [=4]"Very Good";Satisfactory"
- 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
Less than
3
- 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
[=2]"Medium";"Poor"
- 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.
=COUNTBLANK(B4:F4)
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
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.
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
Operator → Greater than or Equal to
Value → 2
Type → Number
Yellow Symbol
Operator → Greater than or Equal to
Value → 1
Type → Number
- 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
=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
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
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 statementtwo 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
- 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.