MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Icon Sets for Text?


November 10, 2017 - by Bill Jelen

Icon Sets for Text?

Can you use an icon set for cells that contain text? No, that won't work. But for one specific situation where you want icons for exactly three words, such as Yes, Maybe, No, there is a cool solution in this article.


Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2067 icon sets for text alright
  • another great question from the
  • Knoxville Tennessee zimmer this one was
  • from Kristin I was in the process of
  • showing how to use icon sets on numbers
  • like this those cool icons and Kristin
  • raise our hands oh wait is there any way
  • to apply icon sets to taxed like she had
  • 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
  • three now hey if you found this video on
  • YouTube hoping that you're gonna be able
  • to set icon sets up for text and you
  • have more than three and I apologize
  • this video is not going to handle well
  • you're gonna want to do but here's how
  • my super secret crazy way of doing this
  • for three ratings what I'm going to do
  • is I'm gonna change those ratings to
  • numbers minus one zero and one like that
  • and then over here my 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'm gonna somehow
  • get the the words are replaced with zero
  • one and minus one 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 all
  • right so now once you have these zeros
  • ones and minus ones 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 taxed I'll here's the
  • super-secret thing I want to do I'm
  • gonna go into the number group to the
  • dialogue launcher and I'm gonna go to
  • custom and in custom it's possible to
  • specify three 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 zero all right 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 gonna say great
  • like that and then a semicolon
  • so the first zone is positive the second
  • zone is negative for negative I'm gonna
  • say fail in quotes has to be in quotes
  • and then another semicolon this is what
  • to do if there's
  • zero and if it's zero then I'll put it
  • in average like that all right 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 awesome right now
  • hey here's the hassle sure there's icon
  • sets that handle five or four 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
  • three conditions I can't trick Excel
  • into giving me different words beyond
  • those three so that's where the whole
  • thing falls apart but still a hopefully
  • a cool trick if you have three different
  • sets of text like that icon sets are
  • discussed in my book power excel with
  • MrExcel click that I on the top right
  • hand corner to check all right wrap up
  • of this quick episode can you apply
  • additional formatting I can unset the
  • text now no but if you have three or
  • less than three ratings you can use the
  • trick in this episode convert those text
  • writings to 1 0 and minus 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 that'll
  • get the awards to appear instead of
  • numbers oh yeah I wanna thank you for
  • stopping by we'll see you next time for
  • another net cast 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 that would work so
  • the first thing I did here is add the
  • numbers 1 through 5 1 through 5 0
  • updating a 1 2 3 4 or 5 and went in and
  • applied conditional formatting and one
  • of the icon sets that has five icons
  • right
  • so just choose anything the ones with
  • five 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 five icons
  • you want to use you don't have to use
  • the ones there they're built in okay and
  • then over here I planned out for each
  • number what text I wanted to have up
  • here so one two three four five and of
  • course I'm being very literal here in
  • real life you're gonna have you know
  • awesome horrible whatever whatever
  • whatever all right and then just
  • straight out custom number format to all
  • cells I handled the three zone so
  • normally it's positive negative and zero
  • but you can also specify now this first
  • zone is for less than to less than two
  • in our case is gonna be one semicolon
  • less than three well we know if it's not
  • if it didn't get caught by that one less
  • than three means it's gonna be - it's a
  • warrants a red diamond and then for
  • everything else I'm gonna say yellow
  • flag which handles my number three case
  • it also erroneously marks four and five
  • with yellow flag but we're gonna handle
  • that in the next step
  • all right so then conditional formatting
  • new rule we're gonna format only cells
  • that contain cells that are greater than
  • or equal to four 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 hello 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
  • four or five and so if it's less than
  • five we say green flag if it's anything
  • else which in this case it's only gonna
  • be five we say cold 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 in this case me has to prove well
  • no you could do it in Excel

Download File

Download the sample file here: Podcast2067.xlsm

Title Photo: Meditations / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.