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:
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:
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:
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:
There are multiple types of “Conditional Formatting”. Select “Icon Sets”. And that will open additional sub menu of already defined types of icon sets:
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:
The same idea can be done for other images such as the following:
And finally you can customize many options, for example, select “Show Icons Only” to remove the original data from the table:
You can read more about conditional formatting at the following location:
To see more Microsoft Excel tips and tricks also visit my site at how-to-use-excel/manha10.com