Excel’s Harvey Balls using Conditional Formatting

Excel’s Harvey Balls using Conditional Formatting

Here is another classic request. I would like to present Harvey Balls embedded in my Excel file.

For those who are not sure what are Harvey Balls, well, these are the simple circle with colored portion representing visually quantity. They look something like this:

harvey-balls-1

The challenge is how to convert values of 0-4 into these shapes.

One option is to use a special font that has regular digits mapped to their Harvey Ball representation, for example, the zero character will type the image:

harvey-balls-2

The advantage is that it works across all office clients including PowerPoint and not just limited to excel.

The disadvantage is that there is a need to install the right font in order to see the Harvey Balls. Another disadvantage is that the font is flat. You can apply colors to it but it is not the same as having full image with many colors, shades and all of the other effects.

If the of use fonts works for you can search for “Free Harvey Ball font” and you can find many such fonts. Here is just one of these: http://www.ambor.com/public/hb/harveyballs.html

We will now review a second method that is built into Excel. It is using a feature called Conditional Formatting on cells and it can be used for many things including highlighting of rows and conversion of values to stars or smiley faces or whatever.

Let’s get started!

We will start with a simple table:

harvey-balls-3

This is the data that we will use and the goal is to automatically present the right Harvey Ball for each number.

Next, we will select column A (with the data) and click on the “Conditional Formatting” button:

harvey-balls-4

There are multiple types of “Conditional Formatting” we will select “Icon Sets”.

This option will open an additional sub-menu of already defined types of icon sets:

harvey-balls-5

And from there you select the Harvey Balls in the “Ratings” section. And that is all what you need to do.

If all is well now column A should now look something like this:

harvey-balls-6

The same idea can be done for other images such as the following:

harvey-balls-7

And finally you can customize many options, for example, select “Show Icons Only” to remove the original data from the table:

harvey-balls-8

You can read more about conditional formatting at the following location:

http://office.microsoft.com/en-us/excel-help/quick-start-apply-conditional-formatting-HA010370614.aspx

To see more Microsoft Excel tips and tricks also visit my site at how-to-use-excel


2 thoughts on “Excel’s Harvey Balls using Conditional Formatting”

  • 1
    Miles on September 21, 2015 Reply

    Nice!
    I’m looking at basing this on colors and or alpha values now.
    This helps a lot. Cheers!
    M

  • 2
    Divesh Parmar on August 9, 2016 Reply

    Hi – is there any way to get Harvey Balls that are more granular? Perhaps 0-6 instead of 0-4?

    Thanks!
    Divesh

Leave a Reply

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