Converting text to Icon Set

[lawrencemiller]

I have a column in excel with "Yes", "No", and "Maybe". Is it possible to create a green icon set for yes, yellow for maybe, and red for no? I would like to add the icon set in the same column. For example:


A B C
Lawrence Miller | (green icon) Yes | Comments

 

[DRSteele]

I believe that you will require a helper column to achieve your objective. Or you can convert the Y/M/N to 1,0,-1 and apply the CondFrmt.

See here: Excel 2007 : Conditional formatting of text cells with icon sets

 

[lawrencemiller]

How can I convert the Y/M/N to 1,0,-1?

I tried to make a custom formatting and typed in: " "Y"; "M"; "N"; @ but this does not seem to convert the numbers.

 

[DRSteele]

Oh, you can make a conditional format using Yes Maybe No but you cannot do it easily with icon sets.

If you like, you can just create a CondFrmt rule that makes a cell background colour of Green for Yes, Yellow for Maybe and Red for No. Would that work for you?

 

[lawrencemiller]

The only problem with changing the background color is that I use background colors to indicate something else in another row of cells. Ideally, icon sets would be the method to use. I know that its possible to add an icon set in another separate column, but preference is in the cell as the "Yes", "No", "Maybe" column.

 

[DRSteele]

Here is a workaround for your stringent requirements.

1) Select the Y_M_N column and conduct Search and Replace All three times: for Yes (replace with 1), for Maybe (replace with 0) and for No (replace with -1)

2) Select the 1_0_-1 column and apply a Custom Number format as follows in the correct order for positive number, negative number, zero: <strike></strike>"yes";"no";"maybe". Use upper case if you like.

3) Select that same column and invoke the conditional formatting procedure, select Icon Sets and choose one. The rule will recognise our new cells of <strike></strike>1_0_-1 and apply a green for Yes, a yellow for Maybe and a red for No. If you look at the rule you will see that it's doing so based on Percent; you can change them to Number (using 1 and 0 as the inputs) and it will apply that same formatting.

4) Leave a note somewhere on the worksheet to instruct users to enter 1_0_-1 in the column instead of Yes_Maybe_ No. It's actually easier than typing YMN I think.

Best of luck!