Using Icon Sets in excel 2007 with text values

raksmith

New Member
Joined
Nov 1, 2011
Messages
1
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? :confused:
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

CaliKidd

Board Regular
Joined
Feb 16, 2011
Messages
173
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

6434178551_4e2b825845.jpg


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

Here are the CF Rule Settings....

6434178529_f510738d35.jpg
 

CaliKidd

Board Regular
Joined
Feb 16, 2011
Messages
173
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

New Member
Joined
Aug 27, 2013
Messages
1

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

New Member
Joined
Jul 21, 2016
Messages
2
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? :confused:

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

New Member
Joined
Jan 26, 2017
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top