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.
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.
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.
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.
__________
[…] 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. […]
Is there a way to adjust shapes VBA compatibilty with Excel 2010??
@Jack, the sample file has Excel 2003 code on one sheet and Excel 2007 code on another worksheet. The Excel 2007 code should work fine in Excel 2010 too.
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
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
Thanks, Allan, and I’ve uploaded a new sample file, in which the face colour changes
https://www.contextures.com/excelfiles.html#UF0050
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
Thanks, Allan, and I’ve uploaded a new sample file, in which the face colour changes
https://www.contextures.com/excelfiles.html#UF0050
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!