How to Insert Conditional Formatting Icon Sets Based on Percentage in Excel:5 Methods

Method 1 – Inserting Shapes Icon Sets Based on Percentage

Steps

• To calculate the Percentages, select the cell D5, and then enter the following formula:

• Drag the Fill Handle to cell D12.
• Fill the range of cells D5:D12 with the Percentage mark obtained by each student.

• Add the Conditional Formatting with Icon Sets in the range of cell D5:D12.
• Select the range of cells D5:D12 and then click on Conditional Formatting from the Home tab.
• From the drop-down menu, click on the New Rule.

• In the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• In the Format Style menu, choose Icon Sets, and select 3 Traffic Lights (Unrimmed) in the Icon Styles.
• Select ‘>’ in the first condition box as shown in the image.
• Input your favorable value in the next value box.
• Select ‘>=’ in the second condition box as shown.
• In both Type dropdown options, select Percent.
• Click OK after this.

• Insert Conditional Formatting shapes Icon Sets Based on Percentage, as shown in the image below.

Method 2 – Inserting Indicators Icon Sets Based on Percentage

Steps

• Add the Conditional Formatting with Icon Sets in the range of cell D5:D12.
• Select the range of cells D5:D12 and then click on Conditional Formatting from the Home tab.
• Then from the drop-down menu, click New Rule.

• In the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• In the Format Style menu, choose Icon Sets, and select 3 Symbols (Uncircled) in the Icon Styles.
• Select ‘>’ in the first condition box as shown in the image.
• Input your favorable value in the next value box. We choose 40.
• Select ‘>=’ in the second condition box as shown.
• Input your favorable value in the next value box. We choose 20.
• In both Type dropdown options, select Percent.
• Click OK after this.

• Insert Conditional Formatting with indicator Icon Sets Based on Percentage, as shown in the image below.

Method 3 – Inserting Directional Icon Sets Based on Percentage

Steps

• Add the Conditional Formatting with Icon Sets in the range of cell D5:D14.
• Select the range of cells D5:D14 and then click on Conditional Formatting from the Home tab.
• Then from the drop-down menu, click New Rule.

• In the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• In the Format Style menu, choose Icon Sets, and select 3 Arrows (colored) in the Icon Styles.
• Select ‘>’ in the first condition box as shown in the image.
• Input your favorable value in the following value box.
• And then input your favorable value in the next value box. We choose 0
• Select ‘>=’ in the second condition box as shown.
• Input your favorable value in the following value box. We choose 0
• In both Type dropdown options, select Number.
• Click OK after this.

• Insert Conditional Formatting with directional Icons Based on Percentage, as shown in the image above.

Method 4 – Inserting Ratings Icon Sets Based on Percentage

Steps

• Add the Conditional Formatting with Icon Sets in the range of cell D5:D12.
• Select the range of cells D5:D12 and then click on Conditional Formatting from the Home tab.
• From the drop-down menu, click on the New Rule.
• In the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• In the Format Style menu, choose Icon Sets, and select 3 start in the Icon Styles.
• Select ‘>’ in the first condition box as shown in the image.
• Input your favorable value in the next value box.
• Select ‘>=’ in the second condition box as shown.
• In both Type dropdown options, select Percent.
• Click OK after this.

• Insert Conditional Formatting with Rating Icon Sets Based on Percentage, as shown in the image below.

Method 5 – Inserting Icon Sets Using VBA

Steps

• Apply Conditional Formatting to the Profit Margin,w e need to calculate the Profit Margin of the given information.
• To calculate the profit margin, select cell G5 and enter the following formula:

Doing this will calculate the profit margin of the first product on the list in cell G5.

• Drag the Fill Handle to the cell G13, fill the range of cell G5:G13 with the profit margin of the products in the range of cells B5:B13.

• After the profit margins, we can apply Conditional Formatting.
• Click on the Developer tab and then select Visual Basic.

• Once you have opened the Visual Basic window, click on Insert.
• A new dialog box, in that dialog box, click on the Insert > Module.
• In the Module editor window, type the following code:

Sub Conditional_Formatting_with_Icon_Sets()
Dim x As Range
Set x = Range("G5:G13")
x.FormatConditions.Delete
x.FormatConditions.AddIconsetCondition
With x.FormatConditions(1)
.Iconset = ActiveWorkbook.Iconsets(xl3Symbols)
End With
With x.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 0
.Operator = xlGreaterEqual
End With
With x.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 0
.Operator = xlGreater
End With
End Sub

• Close the Module window.
• Go to View tab > Macros.
• Click on View Macros.

• After clicking View Macros, select the macros that you created just now. The name here is Conditional_Formatting_with_Icon_Sets. Then click Run.

• After clicking Run, you will notice that the range of cells G5:G13 is now conditionally formatted with indicator symbols.

Download Practice Workbook

Download this practice workbook below.

Related Articles

• Conditional Formatting with More than 3 Icon Sets in Excel
  
• Excel Conditional Formatting Icon Sets Relative Reference
  
• How to Change Conditional Formatting Icon Set Color in Excel

<< Go Back to Icon Sets | Conditional Formatting | Learn Excel