Software

Use Excel's conditional formatting feature to display simple icons

Understanding your data and how Excel's conditional formatting works is the key to accurately using icons to represent data.

I react quicker to symbols than words and values, but Excel displays text and numeric values, not symbols. Right? Not anymore. Using conditional formatting (in 2007 and 2010), you can quickly display icons that might mean more to your users than the values they represent.

To demonstrate this feature, I'll work with data I imported from the Products table in the Northwind database that comes with Access. You can work with any data you like, but the Products data presents some interesting challenges. The data contains a column of text values that represent whether the company still sells the product. This "discontinued" value is FALSE when the product hasn't been discontinued, meaning it's available. When the product has been discontinued, the value is TRUE. It's a bit counter-intuitive, but that's one of the challenges.

The easy way won't always work

First, let's try Excel's easiest icon-displaying solution:

  1. Select the data. For this example, that's L2:L46 (the TRUE and FALSE values).
  2. Click the Home tab.
  3. Choose Icon Sets and select an icon set.

Don't be surprised when nothing seems to happen. That's because the TRUE and FALSE values are text. When this happens, clear the icon set format because it'll show up later when you reference the formatted values. To clear the rule, repeat steps 1 through 2 above. Select Clear Rules and then choose Clear Rules From Selected Cells.

Since the text values don't work, let's try an intermediary value. Insert two columns to the right of L: Available and ABS(). Then fill both columns with the ABS() function to evaluate the TRUE and FALSE values in column L. Don't let the inclusion of column N confuse you. The technique doesn't need it, but I thought showing the absolute values aside the icons (later) would be helpful.

Now, select M2:M46 and repeat the earlier process. This time Excel displays icons, but not the icons you might have expected. Visually, the red x icons tell the user that the item is not available. Unfortunately, they don't represent the data accurately. In this case, a FALSE value in column L means the data has not been discontinued, therefore it is available.

There's no substitute for knowing your data. Excel is displaying the appropriate icons, but you might totally miss that those icons don't represent the data's intent.

Use conditional formatting

There is a fix. The icon sets rely on conditional formatting - simply modify the default rule as follows:

  1. Select the range (M2:M46).
  2. Click the Home tab.
  3. Click the Conditional Formatting dropdown in the Styles group.
  4. Choose Manage Rules.
  5. Select the rule in the list and click Edit Rule. The default conditional values are 67 and 33. We need to change these.
  6. Click Reverse Icon Order. Don't skip this step or the technique won't work.
  7. Change the red x icon value of 67 to 1.
  8. Change the yellow exclamation icon value of 33 to 1.
  9. Don't worry about the green check icon value—Excel will update it automatically, based on the new settings.
  10. Check the Show Icon Only option.
  11. Click OK twice. It works!

Let's review the conditional rules:

  • When the value is greater than or equal to 1, Excel displays the red x. (This icon represents TRUE values in column L; the item has been discontinued.)
  • When the value is less than one and greater than or equal to 1, Excel displays the yellow exclamation point. In effect, we've put this one out of business - it can never meet both conditions.
  • When the value is less than 1, Excel displays the green check. (This icon represents FALSE in column L; the item has not been discontinued.)

When changing the settings, you didn't see the last one at work - you just had to trust me. If you reopen this dialog box, you'll see that Excel updated the third and final condition for you automatically. This technique relies on a lot of internal converting. It can work great or create a total mess.

As you can see below, the icon sets don't work with just any value. The ABS() function works because it converts the text values of FALSE And TRUE, but it can't convert just any text value. In addition, if you enter a numeric value other than 1 or 0, you'll get some unexpected results. The icon set rule is working, you've just entered inappropriate data. This is a good spot for a validation rule, to protect the validity of your data.

These formatting rules are easy to implement, but be careful. Follow the data to make sure they represent the data accurately, and prepare for the unexpected. You don't have to use the pre-defined sets either. Conditional Formatting will let you specify an icon for each condition separately. I admit that the examples a bit convoluted, but I wanted you to see that the ease of use comes with potential challenges.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
oliverbartholdson
oliverbartholdson

I'm using Conditional Formatting to create KPIs for Business Intelligence in SharePoint Online. Since PerformancePoint features and other BI tools (scorecards, dashboards) are not available in SharePoint Online, I've found that using Excel Web Services to display charts and KPIs is an effective alternative to using the more advanced BI tools.

Mark W. Kaelin
Mark W. Kaelin

There are plenty of tricks and techniques made possible by conditional formatting - what are some of the unique ways you use conditional formatting?