Create Colored Harvey Balls in Excel

It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. These were introduced in Excel 2007, and improved in Excel 2010. However, you still can’t get all the icons in any colour. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white.

harveyballs01

As a workaround, you can create your own icon set. I showed one way to do this last year, and you can see the details here. That technique used formulas to get a symbol from a lookup table, and the cells had to be formatted in Wingding font.

createicons01

Show Symbols With Number Format

Last week, Jim McGarity sent me a sample file with his version of icon sets. Instead of using a formula to pull the icon into the cell, Jim created custom number formats. Then, he used those custom formats in the conditional formatting. Brilliant idea, Jim, and thanks for sharing it!

Colored Harvey Balls in Excel blog.contextures.com

The advantages to Jim’s technique are:

  • No formulas, which can slow down a large file
  • Icon and number can be shown in the same cell, or separate cells
  • The worksheet cells can use any font – a symbol font isn’t required

The disadvantage is:

  • This doesn’t work in Excel 2011 for the Mac, where number formats aren’t part of conditional formatting.

We’ll use Jim’s technique to create Harvey balls in red, yellow and green.

Build the Lookup Table

You could use this technique without a lookup table, but it’s easier to manage the range limits if you have them on a worksheet, instead of hard coded into the conditional formatting rules. So, we’ll build a table that shows where each level starts, and the symbol for each level.

To set up the lookup table:

  • In D2:D4, type the minimum score for each level –  0, 35 and 65
  • In cells E2:E4, use Excel’s Insert Symbols feature to enter the empty circle, half-filled circle, and filled circle, from the Segoe UI Symbol font.
    • harveyballs02
  • (Optional) Change the font colour for each symbol, as shown in the screen shot below.
    •  harveyballs03

NOTE: The symbols are in the table for reference only – you could delete these later, after you set up the number formatting.

Create the Custom Number Formats

Next, we’ll create a number format for each symbol:

  1. Select the cell with the empty circle symbol
  2. In the formula bar, select the icon, and press Ctrl + C, to copy it
  3. Press Enter, to exit the Formula Bar
  4. Select an empty cell on the worksheet, and press Ctrl + 1, to open the Format Cells window.
  5. Click the Number tab, and click the Custom category
  6. Clear out any text in the Type box
  7. Press Ctrl + V, to paste the symbol
  8. Type a space, then two question marks, and a zero:  ○ ??0
    TIP: If you just want the icon, don’t include the other characters in the number format.
    You can even create a number format without an icon – just use a space character, so nothing will appear in the cell.
  9. Click OK, then repeat the steps for the other two symbols.

NOTE: The question mark is a spacer, and it will ensure that the numbers line up correctly. Visit the Microsoft website for more information on custom number formatting.

harveyballs04

Apply the Conditional Formatting

The final step is to apply the conditional formatting to the column of Scores.

  • Select all the cells with scores – cells B2:B9 in this example
  • On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
  • Click Use a Formula to Determine Which Cells to Format
  • For the formula, enter:   =B2>=$D$2
    TIP: Make sure that the first reference is to the active cell – B2 in this example 
    • harveyballs05
  • Click the Format button, and on the Number tab, select the custom format – ○ ??0
  • On the Font tab, select Green as the font colour, then click OK.
  • The preview will show the formatting, with the icon.
    • harveyballs07
  • Click OK, twice, to close the Conditional Formatting windows.

Repeat the steps for the other two symbols, then close the Conditional Formatting window.

The Completed Formatting

After you’ve applied the formatting, the icons will appear to the left of the number in column B, separated by a space. Because we used the question mark characters, the digits line up correctly. The 9, in cell B3, is at the far right of the cell, and lines up with the final digit in the 100, in cell B6.

TIP: If you want the number in black font, show the icons in separate cells, that are linked to the score cells. When you create the custom number formats, paste in the symbols, and don’t enter the other characters.

harveyballs08

Download the Sample File

To download the sample file, please visit the Conditional Formatting Examples page on my Contextures website. This example is in the 2010/2007 version of the download file, on the ColorIconsNum sheet.

 

________________

You may also like...

5 Responses

  1. KL says:

    Here is another way (less flexible, and slightly less intuitive :)), using just number formats: [Green][>65]●??0;[Color45][>=35]◐??0;[Red]○??0

    • Thanks KL! Sometimes it’s good to have a less flexible option.

    • Jim McGarity says:

      Because KL’s technique uses number format directly (without going through conditional formatting), it also works on Excel 2011 for the Mac. Mac users should also note that while the choices in Excel 2011’s “Insert…Symbol” dialog pale in comparison with those in Excel 2010 for Windows, they can also copy/paste symbols from the Mac’s native Character Viewer into Excel for use with this technique.

  2. Doug H says:

    Great Post! I also had issues with the built in conditional formatting icon set of Harvey Balls in terms of some additional functionality (i.e., color and the visual concept of quarters 0%,25%,50%,75%100% that it represented.) I also took the approach of a different font style (Arial Unicode MS) to resolve this.

  3. willie says:

    Great work around. The Harvey balls are increased in size proposition to the text but the ball does not fill out the cell. To get the ball to fill out the actually cell size the font is 26. At regular font of 12, the balls are very small. How do you increase the size of the ball without going over a font size of 12.

    thanks in advance

Leave a Reply to Doug H Cancel reply

Your email address will not be published. Required fields are marked *