Excel’s Harvey Balls using Conditional Formatting

A revised version of this content is now available at blog.officewizkid.com


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 black portion representing visually quantity. They look something like this:

Excel's Harvey Balls using Conditional Formatting.1.

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

One option is to use fonts. The fonts will include images that will convert values such as 0 into images like:

Excel's Harvey Balls using Conditional Formatting.2

The advantage is that this method works with all office clients including power point and not just limited to excel. The disadvantage is that in this method there is a need to install the right font in order to see the Harvey Balls. Another disadvantage is that font is really a black and white shape. You can apply colors to it but it is not the same as having full image with many colors, shades and any other effect.

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

The second method that we will review today is different. It is using the built in excel ability to implement 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:

Excel's Harvey Balls using Conditional Formatting.3

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

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

Excel's Harvey Balls using Conditional Formatting.4

There are multiple types of “Conditional Formatting”. Select “Icon Sets”. And that will open additional sub menu of already defined types of icon sets:

Excel's Harvey Balls using Conditional Formatting.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 look something like this:

Excel's Harvey Balls using Conditional Formatting.6

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

Excel's Harvey Balls using Conditional Formatting.7

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

Excel's Harvey Balls using Conditional Formatting.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/manha10.com

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

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

  1. Brilliant, there is nothing as satisfying as using Excel properly, I have already progressed to editing the icon sets…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s