Recently I saw a link about the World Happiness Report. What is it?  “…a landmark survey of the state of global happiness …which ranks 155 countries by their happiness levels” Can you guess what my first question was? 

Is There An Excel File?

Why? If an attempt is made to quantify happiness then there must be numbers. If there are numbers then there’s probably an Excel file! Before I dove into the data I had a more common question:

How Do They Calculate Happiness?

Seriously. How can happiness be quantified? It’s a bold undertaking! I scanned various documents found on their website (17 docs in total). I was impressed with all of the research. It’s a very serious and detailed study. 

Document ‘HR17.PDF‘ is the main report. There is so much information in this document! I found chapter 5 ‘The Key Determinants of Happiness and Misery‘ to be quite interesting.

Document ‘HR17-Ch6_wAppendix.pdf‘ explores happiness at work:

Happiness is typically defined by how people
experience and evaluate their lives as a whole.
Since the majority of people spend much of
their lives at work, it is critically important to
gain a solid understanding of the role that
employment and the workplace play in shaping
happiness for individuals and communities
around the world.

Excel Files

OK, back to the data! Here are the original Excel files: online-data-chapter-2-whr-2017.xlsx and final-data-for-figures-in-chapter-2-whr-2017.xlsx  

Workbook online-data-chapter-2-whr-2017.xlsx contains five sheets including the same three sheets found in final-data-for-figures-in-chapter-2-whr-2017.xlsx

What’s In The files?

Sheet ‘Figure2.2WHR2017′

Countries are ranked by the ‘Happiness score’ found in column B. Columns E to K contain these happiness factors:

• GDP per capita
• Social support
• Healthy life expectancy
• Freedom to make life choices
• Generosity
• Perceptions of corruption
• Dystopia (1.85) + residual

How did they calculate the final ‘Happiness score’? I used the SUM function to determine that adding up the factors listed above is almost the same as the ‘Happiness score’.  Differences were very minor (usually exactly the same until the 4th decimal). How they calculated the various factors is another story.

Sheet ‘Data behind Table 2.1 WHR 2017’

More than twenty columns of factors per country per year. Most countries have data from 2008 to 2016.

Sheet ‘Figure2.3 WHR 2017’

Countries are ranked based on ‘Changes in happiness score‘. Nicaragua, Latvia and Sierra Leone are the top three with the highest positive change.

What Can I Add?

I decided to study the data by creating some quick pivot tables and charts. Here is my Excel file. 

First I added a Scroll Bar. Why? A chart displaying all 155 countries would be too difficult to read. The scroll bar lets me view 30 countries at a time.

As interesting as it was to see how individual countries ranked I wanted to see average rank per region. I couldn’t find Country to Region listed in the Excel file but I did find it in ‘StatisticalAppendixWHR2017.pdf’. I copy/pasted and cleaned up the data (sheet ‘Region & Country’).  The region and country were together in the same cell like this “Western Europe Austria”. For some reason I couldn’t think of an easy way to separate it. Text To Columns wasn’t an option and Flash Fill only partially worked. So, I used an array formula in column B.

Then I created a couple of Pivot Tables (Pivot Chart based on one of them) with a slicer that would filter both Pivots and easily view the data.

Jordan Goldmeier , Mynda Treacy and Leila Gharani are probably cringing. Maybe I should take their Excel Dashboard courses.

Can Excel Make You Happy?

I know….it seems like a silly question. But for me I think Microsoft Excel does contribute to my happiness! I enjoy learning Excel (such a powerful, versatile and always evolving software!), creating solutions for people and participating in the online Excel community.

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy playing with my dogs Cali (she doesn’t like Excel) & Fenton, learning Spanish and playing Chess.