Customize Excel Conditional Formatting Icons

Customize Excel Conditional Formatting Icons

In Excel 2007 and Excel 2010, you can use icon sets in conditional formatting. There are built-in icon sets, and in Excel 2010 you can Customize Excel Conditional Formatting Icons, to some extent. Here’s how to do that, and a workaround to create icons on the worksheet instead.

Built-in Conditional Formatting Icons

There is a good selection of built-in Excel Conditional Formatting Icon sets. For example, use Red, Yellow and Green stoplight icons, to highlight the good, average, and poor results in your sales data.

conditionalformaticon00

Limit the Colours

Rob emailed me recently, to ask how to limit the conditional formatting icons to 2 colours only, instead of the 3 or 4 default icon colours.

I am only interested in using 1 or 2 icons (a red X for “Off” and a Green light for “On” – not interested in the Yellow light). I want these icons to be triggered by a boolean (TRUE/FALSE) in another cell.

Create Your Own Icon Set in Excel 2010

Fortunately, if you’re using Excel 2010, you aren’t limited to the default icon sets – you can create your own icon sets , by mixing and matching from the available icons. (You can’t create your own icons, unfortunately, or change the look of the built-in icons.)

To create the icon set that Rob wants, I selected cells B2:B5, and set the following Formatting Rule.

  • The Show Icon Only option is checked
  • Green Circle icon when the value is greater than or equal to 1 (Number)
  • Red X icon when the value is less than 1 and greater than or equal to 0 (Number)
  • No Cell Icon when the value is less than 0

conditionalformaticon02

How It Works

In cell B2 there is a formula to multiply the value in cell A2 by 1:

=A2*1
That formula is copied down to cell B5.

  1. If the result in column A is TRUE, the formula result in column B is 1, and a green circle shows.
  2. If the result in column A is FALSE, the formula result in column B is 0, and a red X shows.

conditionalformaticon01

Create Your Own Icon Set in Excel 2007 and Earlier

For earlier versions of Excel, where you can’t customize the icon sets, or in Excel version where icon sets don’t exist, there is a workaround. You can use the WingDing font, combined with conditional formatting, to show coloured symbols in the cell.

There are detailed instructions in this article: Conditional Formatting Icons in Excel 2003

image
_______________