Using Icon Sets in excel 2007 with text values

[raksmith]

Hello All,

I'm unable to use Icon Sets (Conditional Formatting) in Excel 2007 based on the text values I input. Is there a way out from this?

 

[CaliKidd]

If I may piggyback on this post and bump it up, I am using Excel 2007 and have the same question.

Is the use of conditional formatting icon sets limited to numbers? Or can you use it with text values too?

For example, I can use conditional formatting to change the text value "Pass" to appear in a green font and "Fail" to appear in a red font. But could I also incorporate an icon set to make it more explicit; for example, have "Fail" not only appear in a red font, but have a red stop light appear in front of it in the cell?

I would welcome any solution, including one in VBA, if that is what it takes.

Also, if Excel 2007 cannot do this, can it be done in Excel 2010? Might be a good tactic to persuade my boss to upgrade.

 

[Jerry Sullivan]

Hi all,

The CF Icon Sets format style is definitely less flexible than other tools. I don't know if this has been changed with xl2010.

I think the only hope of getting that to work in the same Cell as your Text would be through the use of a "Formula" Value Type; however in testing this out the comparison operator (>= , > etc) seems to only recognize numeric comparisons.

As a work-around, you might consider displaying the icon sets in cells adjacent to your Text Cell and check the "Show Icon Only" option.
If you fine tune your borders, and color formatting, it could look like the Icon is in the same Cell as the Text.

Enter this formula in B2 and copy down:
=MATCH(C2,{"Fail","Retake","Pass"},0)

Here are the CF Rule Settings....

 

[CaliKidd]

Thanks, Jerry. I appreciate you taking the time to offer a solution. I am surprised that Microsoft didn't code the icon set more robustly. It seems like a relatively easy enhancement to support text value comparisons since they've already coded this in their conditional formatting logic, but I guess they have bigger fish to fry.

I'm hoping that someone on this forum will comment on whether this "bug", I mean "feature", has been fixed in Excel 2010. It's good to know, however, that your workaround solution is something that can be employed if it hasn't.

 

[smc453]

ADVERTISEMENT

Hello raksmith,

I ran into the same issue when attempting to use an icon set regarding the text values "Yes" and "No". I also needed the "Yes" and "No" values to be in the same column so that a user of the workbook would be able to use the filter and sort based upon a "Yes" or "No" criteria, rather than ones and zeros, hence my similar issue.

I resolved this in a somewhat simple way. I created a column (column A) adjacent to the column (column B) containing the ones and zeros, (the column containing the numeric values enables the icon set conditional formatting).

In the adjacent column (column A) I created a simple if statement, =IF(B2=1,"Yes","No") - both columns have headers so the formula references row 2.

**It’s is important that the column containing the text values is to the left of the numeric column**

Merge the headers of the column and add a filter, this will allow the column B to be filtered based on the values in column A while still allowing the icon set conditional formatting in column B.

Now simply hide column A and you have a clean looking column B with icon set conditional formatting and text values in the filter drop down.

Best regards,

SMC

 

[DMighty876]

Hello All,

I'm unable to use Icon Sets (Conditional Formatting) in Excel 2007 based on the text values I input. Is there a way out from this?

I was able to get an interesting workaround for icon sets with three icons, having to do with both number and conditional formatting.

First, set the number format for positive, negative, and zero values as follows:

1. Under Format Cells, under the Number tab, select Custom.
2. Type the text values you want to use in the Type box in this order: "highest";"lowest";"middle". These correspond to formatting for positive, negative and zero values, respectively.
3. Press OK.

You can then fill the original text values with positive, negative and zero values as required by your spreadsheet. Then, apply your conditional formatting with the icon sets, using the Formula option to set the first icon for positive values, the second for zero values and the third for negative values. Your icons and text should show up in a single cell.


Hope this helps!

 

[netwizz]

I was able to get an interesting workaround for icon sets with three icons, having to do with both number and conditional formatting.

First, set the number format for positive, negative, and zero values as follows:

1. Under Format Cells, under the Number tab, select Custom.
2. Type the text values you want to use in the Type box in this order: "highest";"lowest";"middle". These correspond to formatting for positive, negative and zero values, respectively.
3. Press OK.

You can then fill the original text values with positive, negative and zero values as required by your spreadsheet. Then, apply your conditional formatting with the icon sets, using the Formula option to set the first icon for positive values, the second for zero values and the third for negative values. Your icons and text should show up in a single cell.

I am unable to get this to work! I must be missing something.......

Could anyone who has got it to work please post an example??

Many Thanks!

Netwiz