How to Use Conditional Formatting Icon Sets?

“Icon sets” are used as a performance indicator, progress tracking, risk assessment, comparison and analysis, and so on. They are also helpful for visualizing a cluster of data.

Download the Practice Workbook

How to Use Conditional Formatting Icon Sets in Excel

• Select the defined cells and go to the Home tab.
• From the ribbon, go to Conditional Formatting and click on Icon Sets.
• Pick an icon set according to your preference.

What Are Some Uses of Conditional Formatting Icon Sets in Excel? (4 Applications)

Method 1 – Conditional Formatting Icon Sets Based on Another Cell Value

Case 1.1 – Insert Icon Sets Based on Another Cell

• We can use different shapes of icons as conditional formatting icon sets based on another cell. To do so, remark the values in the Marks column with conditional values using the following formula.

• From the New Formatting Rule wizard, select Format all cells based on their values from Select a Rule Type.
• You can check the Show icon only box to have just an icon and remove the remark numbers.
• Select icon shapes and insert conditions.
• Click OK to finish the process.

• You will have shapes as conditional formatting icon sets.

Case 1.2 – Insert Indicator Icon Sets Based on Another Cell

• You can remark the percentage values in the completion column with conditional values using the IFS function in the following formula:

• Open the New Formatting Rule wizard and select Format all cells based on their values from Select a Rule Type.
• Pick an indicator pattern for conditional formatting icon sets. You can check the Show icon only box to have just the indicator icons and remove the remark numbers.
• Insert the conditions and click OK.

• Indicators will be set as conditional formatting icon sets based on another cell.

Case 1.3 – Insert Directional Icon Sets Based on Another Cell

• Remark the values in the Change in Market Value column with conditional values using the following formula.

• From the New Formatting Rule wizard, select Format all cells based on their values from Select a Rule Type.
• You can check the Show icon only box to have just the icons and remove the remark numbers.
• Pick the directional icons and insert conditions.
• Click OK to finish the process.

• Here’s the result.

Case 1.4 – Insert Rating Icon Sets Based on Another Cell

• You can remark the sales values of the Total Sales column with conditional values in the Ratings column using the following formula.

• Pick your rating icon sets and insert conditions.
• Click OK to finish the entire process.

• You will have the rating icons as conditional formatting icon sets based on another cell.

Method 2 – Apply a Formula for Conditional Formatting with Icon Sets

We can apply a complete formula with the AVERAGE function as the condition for conditional formatting icon sets.
We have a dataset with examination marks where we want to set icons based on the average mark value.

• We want to mark green the cells having a value equal to or more than 20 of the average value, so we have applied the following formula in the Value section.

• We want to mark yellow the cells having equal or less than 10 of the average mark. Apply the following formula.

• We have set the rest of the cells as red. Click OK to finish the procedure.

• We have our defined icons on the Marks column based on the formula.

Method 3 – Conditional Formatting Icon Sets to Compare Two Columns

• Apply the following formula in column E to find the difference in the values of those two columns:

• Set the directional icon sets as conditional formatting icon sets. For a difference greater than 0%, it will set the green icon. The yellow icon will be set for 0% difference and the rest of the cells will have the red icon.

• Here’s the comparison of columns C and D with conditional formatting icon sets.

Method 4 – Conditional Formatting Icon Sets Based on Text

Case 4.1 – Conditional Formatting 3 Icon Sets Based on Text

• In order to add 3 icon sets of conditional formatting based on text, we have applied the following formula to define the examination marks of column C in the text format in column D.

• We applied a formula with the VLOOKUP function to remark the text of column D into the number in column E.

• Set icons and conditions from the Conditional Formatting option.
• Click OK and finish the process.

• We get the conditional formatting of 3 icon sets based on text.

Case 4.2 – Conditional Formatting 5 Icon Sets Based on Text

• Apply the following formula first in column D to define the marks in the text.

• Define the text of column D into a number in column E with the following formula.

• Select a 5-set icon and set the condition for each icon.
• Apply the conditional formatting.

• We will get the conditional formatting of 5 icon sets based on text.

Case 4.3 – Icon Sets Based on Blank or Non-Blank Cells

• To count the blank cells, apply the following formula with the COUNTBLANK function in column E.

• Set icons and conditions from the Conditional Formatting option.
• Click OK and finish the process.

• We get the conditional formatting icon sets based on the number of blank or non-blank cells.

How to Create Custom Conditional Formatting Icon Sets in Excel

The IF function is a useful tool for creating conditional formatting icon sets in Excel. It checks whether a condition is met, and returns one value if TRUE, and another value is FALSE.

• Go to the Insert tab.
• Select Symbol from the Symbols option in the ribbon.

• Pick an icon according to your choice and press Insert to have it in the selected cell.

• Insert as many icons as you need.

• Apply the following formula in column D. You will get some characters based on the matched conditions.

• Select cells in column D and change the font to match the symbol (i.e. Windings).

How to Customize Default Conditional Formatting Icon Sets in Excel?

We can customize the default conditional formatting icon sets with the Icon Style option from the New Formatting Rule wizard.

Consider this dataset and conditions.

• Select the cells
• Go to the Home tab.
• Go to Conditional Formatting, choose Icon Sets, and select More Rules…

• A wizard named New Formatting Rule will appear.
• Pick an icon for every matched condition according to your choice and click OK to finish the process.

• You will get the customized icon sets instead of the default ones in cells C5:C14.

Frequently Asked Questions

Can we edit the applied conditional formatting icon sets?

Yes, we can certainly edit the applied conditional formatting icon sets. For this, select the cells having conditional formatting icon sets and go to the Manage Rules… option from Conditional Formatting. From there, you can easily edit the formula from the Edit option.

What are the types of conditional formatting?

There are 5 types of conditional formatting. They are- background color shading of cells, foreground color shading of fonts, icons, data bars, and values.

What is the default rule type for icon sets?

The three icons set is the default one in Excel. It separates the top, middle, and bottom thirds of a data range.

Conditional Formatting Icon Sets: Knowledge Hub

• Conditional Formatting Icon Sets Based on Text
• Conditional Formatting Icon Sets Based on Percentage
• Change Conditional Formatting Icon Set Color
• Conditional Formatting Icon Sets with Relative Reference
• Conditional Formatting with More than 3 Icon Sets
• Conditional Formatting: Add Custom Icon Sets
•  Conditional Formatting Icon Sets Based on Another Cell

<< Go Back to Conditional Formatting | Learn Excel