Excel Smiley Face Chart-Interactive Adjustable

Happy Face Chart

A few years ago, I added a sample workbook on my site, that features an adjustable Smiley Face Chart.

Type a number between 0 and 100, and the mouth changes from a smile to a frown, based on the score.

Excel Smiley Face Chart-Interactive Adjustable
Excel Smiley Face Chart-Interactive Adjustable

Score Cell Data Validation

The Score cell, H3, has data validation, to ensure that users enter a valid number.

Below, you can see the data validation settings that are used in the Score cell.

Excel Data Validation settings

When you change the value in the Score cell, the Worksheet Change event code runs, and changes the curve of the mouth. You can see the code in the next section of this article.

A couple of years ago, John Walkenbach announced his scientific breakthrough in Smiley Chart development, using a scatter chart to create the smile.

I remained loyal to my version though, and it has served me well over the years.

Using the Smiley Chart in Excel 2007

The Smiley Face chart is still very popular, and is downloaded hundreds of times every month. So, I opened it in Excel 2007 recently, and was surprised to find that it didn’t work correctly. I changed the score, and nothing happened.

First, I made sure that I had enabled macros, because the event code needs to run when the code is changed. Sometimes it’s easy to overlook that Security warning, just above the Formula bar.

Macros were enabled, so that wasn’t the problem. Eventually, I figured out that the degree settings for the smile are different in Excel 2007, so I added a new page to the sample workbook, using those settings.

Why did the setting change? I have no idea. Anyway, here’s the code, showing the minimum and maximum settings, for both Excel 2007 and Excel 2003.

HappyFace02

Download the Smiley Chart Sample File

You can download the zipped Smiley Chart sample file from my Contextures site. On the Sample Files page, look for DV0018 – Happy Face Gauge.
__________

11 thoughts on “Excel Smiley Face Chart-Interactive Adjustable”

  1. […] Wonderful Excel blog Do you use Excel to manipulate of visualize data? Then her is a blog that you may find interesting: Contextures Blog. I heard about it a couple of days ago, I think it was from the Tableau blog. This blog shows you all sorts of tips and tricks using Excel. Some of which require some VBA coding of macros, such as the Smiley Face Chart. […]

  2. hi,
    Can anybody help me with excel
    I want to make excel worksheet with charts,graphs and pie and place a smile on the sheet to show result of good,bad and anxiety.
    I want to link the outcome of profit.e,g..0 to 50 dollars =bad 51 to 74 dollars=anxity 75 to 100 dollars good and automatically
    smiley appear on sheet while these ranges are made.
    thanks
    sara

  3. Hi This great
    I am learning all the time
    Question I have is can you condition format the happy face to red,gree,amber based on a score
    I have used a wheel graph that changes colour based on set sla score can this be done to the face as this would be awesome

  4. This is great
    do you know if there is a way to change the colour based on a score example
    less than 60% = red
    60%- 90% Amber
    90%-100% Green
    that would be great tried to look and do but no success as a beginner

  5. Thankyou so much
    I have had to look so far and wide to find out how modify the face expressions. Then I found your tutorial and it worked first try!

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.