How to Use Icon Sets in Excel to Visualize Data Trends

The article provides detailed guidance on how to use conditional formatting Icon Sets in Excel. It will teach you how to create a custom icon set that overcomes many limitations of the inbuilt options and apply icons based on another cell value.

A while ago, we started to explorer various features and capabilities of Conditional Formatting in Excel. If you havent got a chance to read that introductory article, you may want to do this now. If you already know the basics, lets move on and see what options you have with regard to Excels icon sets and how you can leverage them in your projects.

Icon Sets in Excel are ready-to-use formatting options that add various icons to cells, such as arrows, shapes, check marks, flags, rating starts, etc. to visually show how cell values in a range are compared to each other.

Normally, an icon set contains from three to five icons, consequently the cell values in a formatted range are divided into three to five groups from high to low. For instance, a 3-icon set uses one icon for values greater than or equal to 67%, another icon for values between 67% and 33%, and yet another icon for values lower than 33%. However, you are free to change this default behavior and define your own criteria.

Adding visual flair to spreadsheets through the use of icon sets is an easy way to help readers grasp insights from data at a glance. Excel’s icon sets allow you to associate different icons like arrows faces flags or shapes with cell values to indicate their status – think up, down, good, bad, etc.

Conditional formatting with colorful icon sets enhances Excel workbooks by highlighting trends, outliers, and patterns more intuitively than color scales or data bars alone This guide will teach you how to use Excel’s icon sets to transform boring tables into engaging data visualizations

What are Icon Sets in Excel?

Icon sets are a type of conditional formatting that displays a symbol or image inside a cell based on the cell’s value. The icons help visualize whether values are higher, lower, or mid-range compared to other data

For example, you could configure:

Green up arrows for values above a target
Yellow sideways arrows for values near a target
Red down arrows for values below a target

Because we easily recognize the meaning of symbols, icon sets allow readers to digest key data points faster. Plus, they add visual variety to dense spreadsheets.

Excel includes dozens of built-in icon styles under different categories:

Arrows point up, down, or sideways to show higher, lower, or neutral values
Ratings display stars, circles, or hearts to highlight top performers
Flags mark values like important deadlines or milestones
Signs use road sign shapes to indicate requirements or warnings
Symbols incorporate checks, x’s, and other handy icons
Faces smile, frown, or look neutral depending on cell values
Bars illustrate values through long horizontal bars
Colors highlight cells through solid colored icons

But the options don’t stop there. You can also customize icon sets with your own images or icons. Let’s look at how to add and configure icon sets step-by-step.

How to Apply Icon Sets in Excel

Applying icon sets requires just a few clicks thanks to Excel’s conditional formatting features:

Select the cells you want to apply icons to. This can be a range, rows/columns, or entire sheets.
On the Home tab click Conditional Formatting > Icon Sets and select the desired icon style.
The default 3-icon configuration will apply automatically based on cell values.

![Excel icon sets formats][icon-sets]

And that’s it! Excel will assign icons to the selected cells according to value thresholds. By default, you’ll get:

Green up arrow for top 33% of values
Yellow sideways arrow for mid 33% of values
Red down arrow for bottom 33% of values

The cells dynamically update when values change, allowing the icons to always reflect the latest status. Next let’s customize things further.

How to Customize Icon Sets

The default icon set options just scratch the surface of what’s possible. You can customize them to:

Show more or fewer icons
Change icons styles and colors
Set custom value thresholds
Add data bars alongside icons
Use custom images as icons

Here are some of the customization options:

Adjust Number of Icons

The default 3-icon arrangement may not always fit your needs. Fortunately, adjusting the number of icons shown is easy:

Select the icon set cells
On the Home tab click Conditional Formatting Icon Sets
Select the desired number of icons, from 2 to 5

Excel will automatically adjust value thresholds based on the new icon count.

Modify Icon Styles and Colors

You have full control over icon styles and color schemes:

After applying icon sets, select the cells
Click Conditional Formatting > Manage Rules
Edit the Icon Style, colors, and other formatting options

Experiment to find visuals that best highlight your data.

Set Custom Value Thresholds

By default, Excel splits values into equal buckets like top third, middle third, bottom third. You can create custom thresholds for more meaningful insights:

After applying icons, select the cells
Click Conditional Formatting > Manage Rules
Edit the type to Number and enter custom values like >=80 for top performers

Now icons will align to your custom data brackets.

Add Data Bars

Data bars visually enhance icons by filling cells with horizontal bars proportionate to values. Turn them on through the icon set rule manager.

Import Custom Images

Don’t limit yourself to Excel’s built-in icon sets. You can use custom images or icons that align to your data and branding:

Place images on a separate worksheet in the workbook
Apply icon sets to the target cells
In the rules manager, change icons to reference your custom images

Get creative with icons that fit your message!

Icon Set Examples and Ideas

Now that you know how to add icon sets, let’s look at some examples of when to use them to visualize insights:

Highlight Sales Leaders

Use a 5-star rating icon to highlight sales reps exceeding targets:

5 stars = >120% of target
4 stars = 100 – 120% of target
3 stars = 80 – 100% of target
2 stars = 60 – 80% of target
1 star = <60% of target

One glance quickly shows top and bottom performers.

Track Progress to Goals

Icons with gradients or data bars illustrate progress for metrics like monthly website visitors:

Green up arrow = Above target
Yellow sideways arrow = Within 10% of target
Red down arrow = Below target

The bars give a sense of how close the value is to the goal.

Flag Important Dates

Mark upcoming milestones or expiration dates using flags or stars:

Red flag = Expiring this month
Yellow flag = Expiring next month
Green star = Key date this month

No more missing renewals or critical deadlines!

Grade Test Results

For students or certification exams, use simple icons like checks and X’s:

Green check = Scored over 90%
Yellow sideways arrow = Scored 70-90%
Red X = Scored below 70%

Quickly assess who needs improvement before the next test.

Summarize Survey Responses

Symbols like hearts, stars and circles visualize survey ratings:

5 Hearts = Extremely satisfied
4 Hearts = Very satisfied
3 Hearts = Somewhat satisfied
2 Hearts = Not very satisfied
1 Heart = Not at all satisfied

Easily analyze user satisfaction at a high level.

Project Task Status

Show task status using shapes like circles and squares:

Green square = Complete
Yellow circle = In progress
Red circle = Not started
Black circle with x = Cancelled

Check progress across multiple projects with one glance.

System Alerts

Use road signs or signal icons to flag system issues:

Green check = OK
Yellow triangle = Warning
Red octagon = Critical error

Pinpoint problems across large data sets.

The examples are endless! Icon sets work for all kinds of reports and dashboards where you want to highlight trends, outliers, ratings, statuses, and progress.

Tips for Effective Icon Sets

Here are some tips to help you implement icon sets that make data truly shine:

Use icons purposefully – Don’t just add icons without a strategy. Determine what you want to highlight and choose icons that align to the meaning.
Label icons – Have text descriptions near icons like “Above Target” so meaning is clear at first glance.
Color code – Use red for negative values, green for positive, yellow for neutral. Consistent colors allow faster interpretation.
Show enough detail – Charts often visualize high-level trends better. Use icons for key details you want to highlight.
Keep it simple – Limit icon sets to important points. Too many can overwhelm.
Be consistent – Use the same icon types and colors to represent the same meanings across multiple sheets/workbooks.

With the right strategy, icon sets can transform bland Excel tables into engaging, intuitive visualizations.

Quick Recap

That wraps up our guide on using icon sets in Excel. Here are some key takeaways:

Icon sets visualize cell values through arrows, bars, shapes and symbols based on thresholds
Apply icon sets using Conditional Formatting > Icon Sets and pick a style
Customize number of icons, adjust styles and colors, set custom value rules
Examples include highlighting leaders, progress, statuses, ratings, alerts, and more
Strategize before adding icons and be purposeful in your visualizations

Adding icon sets allows readers to grasp the significance of spreadsheet values at a glance. Tap into Excel’s conditional formatting to take your workbooks to the next

How to customize Excel icon sets

If you are not happy with the way Excel has interpreted and highlighted your data, you can easily customize the applied icon set. To make edits, follow these steps:

Select any cell conditionally formatted with the icon set.
On the Home tab, click Conditional Formatting > Manage Rules.
Select the rule of interest and click Edit Rule.
In the Edit Formatting Rule dialog box, you can choose other icons and assign them to different values. To select another icon, click on the drop-down button and you will see a list of all icons available for conditional formatting.
When done editing, click OK twice to save the changes and return to Excel.

For our example, weve chosen the red cross to highlight values greater than or equal to 50% and the green tick mark to highlight values less than 20%. For in-between values, the yellow exclamation mark will be used.

Tips:

To reverse icon setting, click the Reverse Icon Order button.
To hide cell values and show only icons, select the Show Icon Only check box.
To define the criteria based on another cell value, enter the cells address in the Value box.
You can use icon sets together with other conditional formats, e.g. to change the background color of the cells containing icons.

Excel conditional formatting icon sets based on text

By default, Excel icon sets are designed for formatting numbers, not text. But with just a little creativity, you can assign different icons to specific text values, so you can see at a glance what text is in this or that cell.

Suppose youve added the Note column to your household spendings table and want to apply certain icons based on the text labels in that column. The task requires some preparatory work such as:

Make a summary table (F2:G4) numbering each note. The idea is to use a positive, negative, and zero number here.
Add one more column to the original table named Icon (its where the icons are going to be placed).
Populated the new column with a VLOOKUP formula that looks up the notes and returns matching numbers from the summary table: =VLOOKUP(C2, $F$2:$G$4, 2, FALSE)

Now, its time to add icons to our text notes:

Select the range D2:D13 and click Conditional Formatting> Icon Sets > More Rules.
Choose the icon style you want and configure the rule as in the below:
The next step is to replace the numbers with text notes. This can be done by applying a custom number format. So, select the range D2:D13 again and press the CTRL + 1 shortcut.
In the Format Cells dialog box, on the Number tab, select the Custom category, enter the following format in the Type box, and click OK: “Good”;Exorbitant”;”Acceptable” Where “Good” is the display value for positive numbers, “Exorbitant” for negative numbers, and “Acceptable” for 0. Please be sure to correctly replace those values with your text. This is very close to the desired result, isnt it?
To get rid of the Note column, which has become redundant, copy the contents of the Icon column, and then use the Paste Special feature to paste as values in the same place. However, please keep in mind that this will make your icons static, so they wont respond to changes in the original data. If you are working with an updatable dataset, skip this step.
Now, you can safely hide or delete (if you replaced the formulas with calculated values) the Note column without affecting the text labels and symbols in the Icon column. Done!

Note. In this example, weve used a 3-icon set. Applying 5-icon sets based on text is also possible but requires more manipulations.

Conditional Formatting for Icon Sets – How to use Icon Sets

How to use Excel icon sets?

In the following steps, let us look at how to use Excel icon sets. The premade Icon sets are found in the Home ribbon under the Conditional Formatting option. Click on the Conditional Formatting dropdown box. Locate the Icon sets from the list of options displayed. Click on the Conditional Formatting dropdown box from the Home ribbon.

How to add specific icons from different sets in Excel?

So if you like to add particular icons from different sets then this is the only way. Steps: Select the data range where you want to apply the icons. Next, click as follows: Home > Conditional Formatting > Icon Sets > More Rules. And soon you will get a dialog box.

How to use conditional formatting icon sets in Excel?

Now, we will use conditional formatting icon sets to visualize them. For this, at first, we will select the whole column E. Subsequently, go to the Home tab from the ribbon. Then, under the Styles group, click on the Conditional Formatting drop-down menu. Go to Icon Sets options and select the Directional icons first option.

How do I add icons to a cell in Excel?

Select the cells where you want to apply the icons, go to the Home tab, and choose “New Rule” from the Conditional Formatting drop-down list. When the New Formatting Rule window opens, select “Format All Cells Based on Their Values” at the top. At the bottom of the window, click the Format Style drop-down list and pick “Icon Sets.”