Categories
Excel

How to Use Icon Sets with Conditional Formatting in Excel

You can use icon sets in Microsoft Excel to visually represent your data with arrows, check marks, flags, and other objects.

While icon sets are just one of the options available in the conditional formatting toolset, participants that I meet during Microsoft Excel training are always keen to add some visual interest to their spreadsheets – and icon sets are easily the most popular choice.

As with many concepts in Excel, an example will help you learn how this feature works!

Example: Using an Icon Set to Enhance Your Worksheet

Look at the following spreadsheet. It contains a list of employees and their salaries. We can utilize an icon set to visually indicate which employees have the highest and lowest salaries.

List of employees' salaries

To use an icon set to make this information more visual, do the following.

  1. Select cells B2:B11.
  2. Click Conditional Formatting in the Home > Styles group.
    A list of options appear.
  3. Point to Icon Sets then make a selection from the list.
Icon Set, 3 Traffic Lights (Unrimmed)

I chose 3 Traffic Lights (Unrimmed). Here is what my worksheet looks like, with the icon set applied.

Data with an icon set applied

Salaries with the green circles are in the top third of the range, yellow represents midrange salaries, and the red circles indicate the lowest salaries in the range.

These icons have a simple style that makes it easy for any spreadsheet user to understand how the salaries compare to one another.

Customize Icon Sets to Enhance Your Worksheet

You can customize any icon set to enhance the way information is displayed in your Excel worksheet. There are so many options in this app!

If you need to change how icons (or colors) are allocated, do this.

  1. Select cells B2:B11.
  2. Click Conditional Formatting in the Home > Styles group.
    A list of options appear.
  3. Click Manage Rules…
    The Conditional Formatting Rules Manager dialog box appears.
  4. Select Icon Set then click Edit Rule…
    The Edit Formatting Rule dialog box appears.
Edit Formatting Rule dialog box

Notice that a green circle is displayed when a salary is 67% or above for the highlighted range of values. Salaries in the 33% – 66% range are considered mid-range. Salaries less than 33% are in the lowest range.

You can easily enter different percentages, if you want to change how the icons are applied.

I include icon sets in my work all the time. It’s a feature that instantly makes any worksheet way more user friendly!

Keep It Simple: Only Show Icons

If you’re concerned about keeping peoples’ exact salaries confidential, you could select the Show Icon Only checkbox in the Edit Formatting Rule dialog box (pictured in the previous section) and click OK.

Here is how the sample worksheet would look with the Show Icon Only option selected.

Show Icons Only

How Many Icon Packs Does Excel Come With?

Excel comes with 20 icon sets containing various shapes and styles that you can adapt for your projects.

Think about it… you can create a new dashboard to display information without code or programming knowledge… is there anything this app can’t do?

Gotta love Excel!

Other Conditional Formatting Options

While this article is primarily about icon sets, I thought you might like to see some of the other conditional formatting options that are available for your worksheets.

Here is the same salary list formatted with different options. The list on the left has been formatted with Data Bars. The list on the right is using Color Scales.

Conditional Formatting

What’s Next?

Icon sets can make it much easier for users to understand a worksheet – and there are many options you can customize! Just configure the settings in a way that will work best with your information.

If you’ve enjoyed learning about icon sets in Microsoft Excel, you might want to check out our complete guide to basic formatting in Excel.

See you next time!

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.