It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. There are limitations though.
For example, you can’t get all of the icons in any colour combination that you choose. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white.
What Are Harvey Balls?
Harvey balls are round shapes, that can be used for quick comparisons. They are sometimes used to show various stages of task completion, or for customer satisfaction survey results.
I used to read the Consumer Reports magazine, and they used a variation of the Harvey Balls, in their rating system. A red filled circle was an excellent rating, and black filled circle was a poor rating.
You can read more about Harvey Balls on the Wikipedia site.
Fun fact – Someone named Harvey Ball invented the Smiley Face! You can see my Excel smiley face here.
Quick Harvey Balls – Black and White
In Excel, it’s quick and easy to show black and white Harvey Balls, with Excel conditional formatting.
Here are the steps to follow:
- First, select the cells where you want to insert the Harvey balls.
- Note: Only cells with numbers will show conditional formatting icons
- Next, on the Excel Ribbon’s Home tab, click on Conditional Formatting
- In the drop-down menu, point to the Icon Sets command
- In the pop-up menu, in the Ratings section, click on the Harvey Balls (5 Quarters icon set)
On the worksheet, the selected cells will show Harvey Balls icons, in black and white, based on the cell values.
- The cell with the highest number has a filled black circle
- The cell with the lowest number has an empty circle shape
Add Background Colour
If you’d like a splash of colour with the Black and White Harvey balls, you can add another conditional formatting rule to the cells.
This time, use one of the Colour Scales, such as Red, Yellow, Green, like I did in the screen shot below.
Now, the cell with the highest number has a filled black Harvey ball, with a red fill colour. The lowest number is in a green cell, with an empty Harvey ball.
Optional: Customize Harvey Balls
Instead of leaving the default Harvey Ball settings, with five different circles, you could customize the icon set, and use 3 balls, for a simpler look.
There are written steps on my Contextures site, and in the screen shot below, I changed the set to 3 balls, with number limits at 65, 35 and zero.
- Tip: You can use cell references for the values, instead of typing the numbers into the Rule Description.
Create Coloured Harvey Ball Icons
One way to overcome the black and white limitation with built-in Icon Sets, is to create your own icon set. I showed one way to do this last year, and you can see the details here.
Last week, Jim McGarity sent me a sample file with his version of icon sets, based on custom number formats and conditional conditional. That’s a great idea, Jim, and thanks for sharing it!
Steps to Create Custom Icons
There are 3 main steps in Jim’s technique to create Harvey balls in red, yellow and green.
- Build a lookup table with scores and symbols
- Create custom number formats with symbols
- Apply conditional formatting rules
The details for each step are in the sections below.
1) Build a Lookup Table
The first step is to build a small lookup table on the worksheet. The table has two columns:
- the number at which each level starts
- the Harvey Ball symbol for each level.
In the screen shot below, the lookup table is in cells D2:E4
Set Up the Table
To set up the lookup table, follow these steps:
- 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.
- (Optional) Change the font colour for each symbol, as shown in the screen shot above.
NOTE: The symbols are in the lookup table for reference only
2) Create Custom Number Formats
The next step is to create a number format for each symbol. These number formats will be used for conditional formatting later.
- Note: The number formats, shown below, use the ? (question mark) character, which is a spacer. That will make the numbers line up correctly on the worksheet. Visit the Microsoft website for more information on custom number formatting.
Copy First Symbol
- First, in the lookup table, select the cell with the empty circle symbol
- In the formula bar, select the icon, and press Ctrl + C, to copy it
- Press Enter, to exit the Formula Bar
Create First Number Format
- Next, select an empty cell on the worksheet
- Press the keyboard shortcut, Ctrl + 1, to open the Format Cells window.
- Click the Number tab, and at the left, click the Custom category
- Clear out any text in the Type box
- Press Ctrl + V, to paste the symbol
- Next, type a space, then two question marks, and a zero:
- ○ ??0
- Click OK, to complete the number format
Make 2 More
- Next, repeat the steps, to create custom number formats for the other two symbols.
3) Apply the Conditional Formatting
The final step is to apply the conditional formatting to the column of Scores.
- To begin, 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
- Click the Format button
- On the Number tab, select the custom format that you created earlier:
- – ○ ??0
- Next, go to the Font tab
- Select Green as the font colour, then click OK.
- The preview will show the formatting, with the empty circle icon.
- 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 the custom number formats used the question mark characters, the digits line up correctly.
- The 9, in cell B3, is at the far right of the cell
- It lines up with the final digit in the 100, in cell B6.
Download the Sample File
To download the sample file, go to the Conditional Formatting Examples page on my Contextures website.
This example is in the first download file, Conditional Formatting Basics, and it’s on the ColorIconsNum sheet.
Video: Conditional Formatting – Data Bars
Another way to show numbers visually, is with conditional formatting data bars. At a glance, you can see which cells have the largest and smallest numbers.
This short video shows how to set up Excel Data Bars, and change their default settings.
Related Articles
Create Your Own Excel Icon Set
Customize Excel Conditional Formatting Icons
Create Conditional Formatting Icons in Excel
________________
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.
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.
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.
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
This is great. Thank you for sharing. I was able to replicate it.