Kristin asks if you can add an icon set for Text cells? There are two workarounds in this episode. The first is for up to three icons. The outtake shows another way for five icons.
Can you apply Conditional Formatting Icon Sets to Text?
No
But, if you have three (or less) ratings, you can use the trick in this episode.
Convert your text ratings to 1, 0, and -1
Apply the Icon set
Use a custom number format of "positive";"negative";"zero"
This will get words to appear instead of numbers
Can you apply Conditional Formatting Icon Sets to Text?
No
But, if you have three (or less) ratings, you can use the trick in this episode.
Convert your text ratings to 1, 0, and -1
Apply the Icon set
Use a custom number format of "positive";"negative";"zero"
This will get words to appear instead of numbers
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2067: Icon Sets for Text Alright, another great question from the Knoxville, Tennessee seminar.
This one was from Kristin.
I was in the process of showing how to use icon sets on numbers like this, you know those cool icons, and Kristin raised her hands, “Oh wait, is there any way to apply icon sets to text?”, like she has some ratings.
And I asked her a question.
I held my breath when I asked this question, I said, “How many different ratings do you have?” And the answer that she gave me was the perfect answer of 3.
Hey, if you found this video on YouTube hoping that you're going to be able to set icon sets up for text and you have more than 3, then I apologize this video is not going to handle what you want to do.
But here's how my super secret crazy way of doing this for 3 ratings.
What I'm going to do is I'm going to change those ratings to numbers: -1, 0, and 1, like that.
And then over here, the VLOOKUP that Kristin was using to get those ratings in or maybe was an IF statement, I don't know whatever it was.
I want to somehow get the words are replaced with 0, 1 and -1, like that.
And we don't have no one failing right now so let's throw some fails in there, like that, so we make sure that we have some fails, alright?
So now, once you have these 0s, 1s and -1s, I'm going to go into: Conditional Formatting, Icon Sets and set it up, like that.
But you're saying, “No, no, that's not.
Still hoping our problem, that's not text.” Over here is the super secret thing I want to do.
I'm going to go into the Number Group to the Dialog Launcher and I'm going to go to Custom, and in Custom it's possible to specify 3 different zones.
What to do if the number is positive, what to do if the number is negative and what to do if the number is 0.
Alright, so if we have a positive number, if we have a 1, that's where I want the word Great to appear in quotes.
I'm going to say “Great”, like that and then a semi-colon.
So the first zone is positive, the second zone is negative.
For negative, I'm going to say “Fail” in quotes, has to be in quotes and then another semicolon.
This is what to do if there's a 0.
If we have a 0, then I'll put in “Average” like that, alright.
Look I'm cheating, we're getting words to appear and then the Icon Sets and we'll change someone here from 79 to 95.
They changed from average to great.
The icon changes from the yellow exclamation point to the green checkmark.
Woohoo!
That’s awesome, right?
Now, hey, here's the hassle.
Sure there's Icon Sets that handle 5 or 4.
That's not the limiting factor and like we use this one or that one.
My problem is the Custom Number formats can't handle more than 3 conditions.
I can't trick Excel into giving me different words beyond those 3, so that's where the whole thing falls apart.
But still, hopefully a cool trick if you have 3 different sets of text like that.
Icon sets are discussed in my book, Power Excel with MrExcel.
Click that “i” in the top-right hand corner to check.
Alright, wrap- up of this quick episode: Can you apply Conditional Formatting Icon Sets to Text?
No, but if you have 3 or less than 3 ratings, you can use the trick in this episode.
Convert those text ratings to 1, 0 and -1, apply the Icon Set to those numbers and then you use a Custom number format in this format where we have in quotes: What to do if it's positive?
What to do if it's negative?
What to do if it's 0?
Help, there's a bug there, hang on.
That should be a semicolon.
Semicolon between each of those zones not a comma.
There we are.
That will get the words to appear instead of numbers.
Oh yeah, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
How we should have a little theme song for its - I'll take video time.
You know, anytime I say that something can't be done, I learned that, of course, it can be done.
You could have 5 different words and 5 different Icons.
That would work.
So, the first thing I did here is add the numbers 1 through 5.
1 through 5.
See it’s up there - 1 2 3 4 5, and went in and applied Conditional Formatting, and one of the Icon Sets that has 5 icons, alright.
So just choose any of the ones with 5.
And then, Manage the Rules, and in that one, change all of these to be numbers, change all of these to be greater than or equal to and then 5 4 3 2.
And then you can open these little dropdowns here and choose the 5 Icons you want to use.
You don't have to use once they're built in.
Okay, and then over here, I planned out for each number what text I wanted to have up here.
So, 1 2 3 4 5 and of course, I'm being very literal here.
In real life you're going to have, you know, awesome, horrible whatever whatever whatever ,alright?
And then, just straight out Custom Number Format to All Cells.
I handled the three zones.
So normally it's positive, negative and 0 but you can also specify.
Now, this first zone is for less than 2.
Less than 2, in our case it’s going to be 1, semicolon.
Less than 3 - well we know if it's not- if it didn't get caught by that one less than 3 means it's going to be 2.
It's a warrant say Red Diamond.
And then for everything else, I'm going to say Yellow Flag which handles my number 3 case.
It also erroneously marks 4 and 5 with Yellow Flag but we're going to handle that in the next step.
Alright, so then, Conditional Formatting, New Rule, we're going to Format only cells that contain, cells that are greater than or equal to 4 and then we go into Format.
Now, let me go back to the one I already set up here.
Conditional Formatting, Manage Rules, this one here Edit the Rule.
In the Format tab, we can change the Fill Color, we can change the borders, we can change the Font.
Oh, but we can change the Number Format.
And so here, I put in a Custom Number Format.
Remember we're only here if we're at 4 or 5.
And so if it's less than 5, we say Green Flag.
If it's anything else, which in this case it's only going to be 5, we say Gold Star.
Insane, amount of stuff to do but it's one of those things that as soon as someone says- as soon as someone says, “Oh, you can't do that in Excel.” Well, you know, someone, some smart- like, in this case, me has improved.
Well, no.
You could do it in Excel.
This one was from Kristin.
I was in the process of showing how to use icon sets on numbers like this, you know those cool icons, and Kristin raised her hands, “Oh wait, is there any way to apply icon sets to text?”, like she has some ratings.
And I asked her a question.
I held my breath when I asked this question, I said, “How many different ratings do you have?” And the answer that she gave me was the perfect answer of 3.
Hey, if you found this video on YouTube hoping that you're going to be able to set icon sets up for text and you have more than 3, then I apologize this video is not going to handle what you want to do.
But here's how my super secret crazy way of doing this for 3 ratings.
What I'm going to do is I'm going to change those ratings to numbers: -1, 0, and 1, like that.
And then over here, the VLOOKUP that Kristin was using to get those ratings in or maybe was an IF statement, I don't know whatever it was.
I want to somehow get the words are replaced with 0, 1 and -1, like that.
And we don't have no one failing right now so let's throw some fails in there, like that, so we make sure that we have some fails, alright?
So now, once you have these 0s, 1s and -1s, I'm going to go into: Conditional Formatting, Icon Sets and set it up, like that.
But you're saying, “No, no, that's not.
Still hoping our problem, that's not text.” Over here is the super secret thing I want to do.
I'm going to go into the Number Group to the Dialog Launcher and I'm going to go to Custom, and in Custom it's possible to specify 3 different zones.
What to do if the number is positive, what to do if the number is negative and what to do if the number is 0.
Alright, so if we have a positive number, if we have a 1, that's where I want the word Great to appear in quotes.
I'm going to say “Great”, like that and then a semi-colon.
So the first zone is positive, the second zone is negative.
For negative, I'm going to say “Fail” in quotes, has to be in quotes and then another semicolon.
This is what to do if there's a 0.
If we have a 0, then I'll put in “Average” like that, alright.
Look I'm cheating, we're getting words to appear and then the Icon Sets and we'll change someone here from 79 to 95.
They changed from average to great.
The icon changes from the yellow exclamation point to the green checkmark.
Woohoo!
That’s awesome, right?
Now, hey, here's the hassle.
Sure there's Icon Sets that handle 5 or 4.
That's not the limiting factor and like we use this one or that one.
My problem is the Custom Number formats can't handle more than 3 conditions.
I can't trick Excel into giving me different words beyond those 3, so that's where the whole thing falls apart.
But still, hopefully a cool trick if you have 3 different sets of text like that.
Icon sets are discussed in my book, Power Excel with MrExcel.
Click that “i” in the top-right hand corner to check.
Alright, wrap- up of this quick episode: Can you apply Conditional Formatting Icon Sets to Text?
No, but if you have 3 or less than 3 ratings, you can use the trick in this episode.
Convert those text ratings to 1, 0 and -1, apply the Icon Set to those numbers and then you use a Custom number format in this format where we have in quotes: What to do if it's positive?
What to do if it's negative?
What to do if it's 0?
Help, there's a bug there, hang on.
That should be a semicolon.
Semicolon between each of those zones not a comma.
There we are.
That will get the words to appear instead of numbers.
Oh yeah, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
How we should have a little theme song for its - I'll take video time.
You know, anytime I say that something can't be done, I learned that, of course, it can be done.
You could have 5 different words and 5 different Icons.
That would work.
So, the first thing I did here is add the numbers 1 through 5.
1 through 5.
See it’s up there - 1 2 3 4 5, and went in and applied Conditional Formatting, and one of the Icon Sets that has 5 icons, alright.
So just choose any of the ones with 5.
And then, Manage the Rules, and in that one, change all of these to be numbers, change all of these to be greater than or equal to and then 5 4 3 2.
And then you can open these little dropdowns here and choose the 5 Icons you want to use.
You don't have to use once they're built in.
Okay, and then over here, I planned out for each number what text I wanted to have up here.
So, 1 2 3 4 5 and of course, I'm being very literal here.
In real life you're going to have, you know, awesome, horrible whatever whatever whatever ,alright?
And then, just straight out Custom Number Format to All Cells.
I handled the three zones.
So normally it's positive, negative and 0 but you can also specify.
Now, this first zone is for less than 2.
Less than 2, in our case it’s going to be 1, semicolon.
Less than 3 - well we know if it's not- if it didn't get caught by that one less than 3 means it's going to be 2.
It's a warrant say Red Diamond.
And then for everything else, I'm going to say Yellow Flag which handles my number 3 case.
It also erroneously marks 4 and 5 with Yellow Flag but we're going to handle that in the next step.
Alright, so then, Conditional Formatting, New Rule, we're going to Format only cells that contain, cells that are greater than or equal to 4 and then we go into Format.
Now, let me go back to the one I already set up here.
Conditional Formatting, Manage Rules, this one here Edit the Rule.
In the Format tab, we can change the Fill Color, we can change the borders, we can change the Font.
Oh, but we can change the Number Format.
And so here, I put in a Custom Number Format.
Remember we're only here if we're at 4 or 5.
And so if it's less than 5, we say Green Flag.
If it's anything else, which in this case it's only going to be 5, we say Gold Star.
Insane, amount of stuff to do but it's one of those things that as soon as someone says- as soon as someone says, “Oh, you can't do that in Excel.” Well, you know, someone, some smart- like, in this case, me has improved.
Well, no.
You could do it in Excel.
