Star Rating Number Format

It’s Wednesday night. That means I’m drinking Tangueray and reviewing football games (for entertainment purposes only). I like to rate games on a three-star system. Three stars means refinance the house and fly to Vegas. Two stars means I really like a team. And one star means I’d have as much luck flipping a coin. Zero stars means Purdue is playing (why Purdue wins when they should lose and loses when they should win is beyond me).

Instead of putting a 1, 2, or 3 next to the team, I want actual stars.

To get that look: left align, set font to Wingdings, and use this custom number format:

[<2]"«";[<3]"««";"«««";

And to get those double chevrons (or whatever they are), hold down the Alt key and type 0171 on the numeric keypad. Now type a 1, 2, or 3 in the cell and your in like Roger Ebert.

P.S. I can never remember how to do that conditional number format thing, so I always go to PeltierTech.com to re-learn it.

9 thoughts on “Star Rating Number Format

  1. I’ll second the Tanqueray, although it doesn’t have to be Wednesday ;-)

    Of course, in Excel 2007 there is a much easier way to do this with the conditional formatting icon sets. Using the pre-defined sets gives you the ability to quickly create dashboards with traffic light signals, increasing bar graphs, etc. using percentages or numbers. This is probably one of my favorite enhancements in this version.

  2. of course depending on the future use for the cells with the stars you could keep it simple and just type “Shift 8? as many times as was required.

  3. This was a great entry, and worth its weight in gold for he reference to Peltier. I never seem to be able to find the information about number formats in Excel help, so this is a terrific reference.

  4. Protip: you don’t have to use Wingdings. Unicode defines a character for a star which looks like this: ?

    You can copy/paste or Alt+2605

  5. This is great, as I find the conditional formatting icon sets in Excel 2007 never supply what I want, and currently are not customisable.
    I look forward to them in the future, but I need these stars today, so thankyou

  6. Dick: Did you try just copy/pasting the character from my post? Alt-codes seem to be rather erratic; I could type the star with my desktop keyboard but not my laptop. The easiest way to use the star is to copy it where it has already appeared, either from a post like above or the character map…


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.