Excel: Conditional formatting using icon sets?

Question

I would like to format the Subtotal row such that within each Subtotal cell is the total and an "icon set" icon that will show if that month is greater than or less than the previous month's total.

I tried the following conditional formatting rule from scratch highlighting Feb thru Dec's subtotal cells:

Hoping that it would apply a green "up" icon if that month's subtotal was greater than the previous month's, the yellow "same" icon if it was the same, and a red "down" icon if it was less than the previous month.

However, I ended up with an error:

So...any ideas on how I can accomplish what I'm after?

Answer 1

Assuming you are using Excel 2007 and above, the builtin icon sets in the conditional formatting does not really have the ability to do the trending you are requiring. There are a few different ways to skin this cat.

Let's take this data table:

January  |  February  | March  | April
  $10    |    $20     |   $15  |   $15

Method 1

This method you would create an IF formula and then output the character you needed to represent the up/down/sideways arrow based on the formula. You would have to add a column between each month/subtotal in order to achieve this. The font style for each of the arrow columns would be wingdings and then you can format the arrows however you wanted with conditional formatting based on the Wingdings character. Once you do the formula (and conditional formatting) then you just copy/paste it to the other columns.

The formula would be this:

=IF(A2<B2,CHAR(233),IF(A2=B2,CHAR(232),CHAR(234)))

The codes are as follows:

é = Up Arrow = char 233
è = Sideways (right) arrow = char 232
ê = Down Arrow = char 234

---

To conditionally format the arrows you would select the arrows and apply the "Highlight Cell Rules, Text that Contains" and for your value you would put =char(233) and select your formatting. Then add that rule for each character.

Method 2

You can use the icon sets but you must set up the parameters on a per column (subtotal) basis. You will click in the February sub total column and apply the Conditional Format for the Icon set (Home, Conditional Format, Icon Sets, Directional Arrows). It will show an arrow. Now click back on Conditional Format, then Manage Rules. Click on the Icon Set and click "Edit Rule".

For the Up Arrow rule, change it to read ">" and for "Type" select "Number" then click the Formula bar button under "Value" and then click the January cell. Then press Enter. This will give you the value, $A$2. It must have absolute values using the $ and can't simply be a relative value of A2.

For the next arrow select the yellow left-to-right arrow. Select ">=" and change "Type" to "Number". Then in the "Value" field type in the same thing for the Up Arrow rule.

For the next arrow select the red down arrow if it isn't already. Click OK then click OK.

Repeat these steps for each successive month.

Answer 2

If others come across this issue, you can kind-of use relative referencing... at least this is faster than setting up the rule for each cell or column manually. First do one cell (you must only choose one cell), and use INDIRECT to reference the value, e.g. to relative reference a column value inside the conditional rule (or could use rows if needed, etc...):

=INDIRECT("R9C" & COLUMN(), FALSE)

Then simply copy the cell, and Paste Formatting to the rest of the cells you need the icon set or color scale to apply. If you relative reference both row and column, you must paste one cell at a time. If as in this example case formula I've mentioned above you only relative reference a column but row is absolute, you can then paste a whole column at a time.
There are other ways to speed this up, but you get the gist of it.