Software savvy: customize Excel's conditional formatting icons.

• Author: Lenning, Jeff
• Position: Tech Talk

If you haven't checked out Excel's conditional formatting feature recently, you're missing out on some nice enhancements. The feature formats a cell based on its value. Excel continuously monitors the cell value and updates the formatting as the cell value changes. Conditional formatting has traditionally been limited to basic cell formatting such as fill and font color, but, in modern versions of Excel, it can do much more. For example, it can create data bars, color scales and icon sets. In this column, we'll talk about icon sets and how to customize them. Excited? Me too!

Standard Icon Sets

A conditional formatting icon set is a collection of related icons inserted into the cells based on pre-defined rules. For example, if the icon set has a green icon, a yellow icon and a red icon, Excel applies the icons to the cells based on the top, middle and bottom third of the values. Sets with four icons are applied in fourths, and sets with five icons are applied in fifths.

Applying standard icon sets is pretty easy. You simply select the range and then choose the desired icon set from the Home > Conditional Formatting > Icon Sets menu. For example, to help visualize our inventory turnover data, we select the turnover values and then apply the desired icon set (Figure 1).

As you can see, the green icon is applied to the top third, the yellow icon is applied to the middle third and the red icon is applied to the bottom third. While the standard icon sets are great, we can have a lot more fun when we figure out how to customize them.

Custom Icon Sets

We can customize a few different settings, including thresholds. Instead of doing top, middle and bottom thirds, we could use top 10 percent, middle 80 percent and bottom 10 percent. We also can pick and choose the icons, reverse the icon order and opt to hide the cell value to show the icon only. These customizations are performed with the "Manage Rules" dialog box, opened with the Home > Conditional Formatting > Manage Rules icon.

To demonstrate, let's say we store our journal entry details in a table, and then compute a little journal entry summary (Figure 2).

The alert column computes the difference between debits and credits: When the difference is zero, that is good; and when the difference is not zero, that is bad. We...