Excel Conditional Formatting Icon Sets Based on Percentage

When working with big amounts of data, it is often important to format the data in Excel in specific ways to improve readability. If you are curious to know how you can apply Conditional Formatting with Icon Sets Based on Percentage, then this article may come in handy for you. In this article, we discuss how you can apply Conditional Formatting with Icon Sets Based on Percentage in Excel using VBA with an elaborate explanation.

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

We will use five effective and tricky examples to insert Conditional Formatting Icon Sets Based on Percentage in Excel. Here, we will illustrate 5 examples of inserting Conditional Formatting Icon Sets: shapes Icon Sets, directional Icon Sets, indicators Icon Sets, rating Icon Sets, and indicators Icon Sets with a VBA code. This section provides extensive details on the five ways. You can use either one for your purpose, they have a wide range of flexibility when it comes to customization.

Example 1: Inserting Shapes Icon Sets Based on Percentage

We will find the Percentage of the marks obtained by each student first, and then we will apply Conditional Formatting with Icons to each Percentage value

Steps

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

• Then drag the Fill Handle to cell D12.
• Dong this will fill the range of cells D5:D12 with the Percentage mark obtained by each student.

• Now we will 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 on the New Rule.

• Then in the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• Next 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.
• And then input your favorable value in the next value box.
• After then select ‘>=’ in the second condition box as shown.
• In both Type dropdown options, select Percent.
• Click OK after this.

• As a consequence, you will be able to insert Conditional Formatting shapes Icon Sets Based on Percentage, as shown in the image below.

Read More: Excel Conditional Formatting: Add Custom Icon Sets 

Example 2: Inserting Indicators Icon Sets Based on Percentage

In the below dataset, we will use the indicator Sets of Icons to Conditionally format the completion column Based on the Percentage value. With this Formatting, the readability and clarity will increase manifold.

Steps

• Now we will 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 on the New Rule.

• Then in the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• Then 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.
• And then input your favorable value in the next value box. We choose 40.
• After then select ‘>=’ in the second condition box as shown.
• And then input your favorable value in the next value box. We choose 20.
• In both Type dropdown options, select Percent.
• Click OK after this.

• As a consequence, you will be able to insert Conditional Formatting with indicator Icon Sets Based on Percentage, as shown in the image below.

Read More: Excel Conditional Formatting Icon Sets Based on Another Cell

Example 3: Inserting Directional Icon Sets Based on Percentage

In the below dataset, we are going to demonstrate how you can apply Conditional Formatting by inserting directional Sets Icons Based on Percentage. The dataset shows different company’s stock price and their changes in stock prices in terms of Percentage. We are now going to use directional Icons to show the status of each company’s stock’s recent performance.

Steps

• Now we will 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 on the New Rule.

• Then in the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• Then 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.
• And then input your favorable value in the next value box.
• And then input your favorable value in the next value box. We choose 0
• After then select ‘>=’ in the second condition box as shown.
• And then input your favorable value in the next value box. We choose 0
• In both Type dropdown options, select Number.
• Click OK after this.

• As a consequence, you will be able to insert Conditional Formatting with directional Icons Based on Percentage, as shown in the image above.

Read More: Conditional Formatting Icon Sets Based on Text in Excel

Example 4: Inserting Ratings Icon Sets Based on Percentage

Now, we are going to demonstrate how to insert Conditional Formatting rating Icon Sets Based on Percentage in Excel. The dataset shows different Tasks and the total completion of each task. We are now going to use rating Icons to show the ratings of the total sales of the products

Steps

• Now we will 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 on the New Rule.
• Then in the Edit Formatting Rule dialog box, select Format all cells Based on their values from the Select a Rule Type option.
• Then 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.
• And then input your favorable value in the next value box.
• After then select ‘>=’ in the second condition box as shown.
• In both Type dropdown options, select Percent.
• Click OK after this.

• As a consequence, you will be able to insert Conditional Formatting with Rating Icon Sets Based on Percentage, as shown in the image below.

Example 5: Inserting Icon Sets Using VBA

For the demonstration purpose, we are going to use the below dataset where we implement a VBA macro to apply Conditional Formatting with Icon Sets Based on the Percentage value in Excel. Which will increase the readability and the clarity of the document manifold without any big hassle.

Steps

• Before we apply Conditional Formatting to the Profit Margin, we need to calculate the Profit Margin of the given information first.
• 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.

• Then drag the Fill Handle to the cell G13, doing this will fill the range of cell G5:G13 with the profit margin of the products in the range of cells B5:B13.

• Now as we have the profit margins, we can apply Conditional Formatting.
• To begin, click on the Developer tab and then select Visual Basic.

• Once you have opened the Visual Basic window, click on Insert.
• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, 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

• Then close the Module window.
• After that, go to View tab > Macros.
• Then 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.

Conclusion

To sum it up, the issue of applying conditions Formatting with Icon Sets Based on Percentage is answered here by 5 different examples. Among them, the VBA Macro method requires prior VBA-related knowledge to understand from scratch.

For this problem, a macro-enabled workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

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