Exploratory Analysis with Excel

When it comes to data science, we often find that people first start out learning using Microsoft Excel.  Inevitably, as students progress, Excel starts to appear quaint as they transition to more powerful systems like R or Python. After a while, however, a lot of people end up coming back and realizing that maybe Excel isn’t so bad after all. 

When it comes to data science, we often find that people first start out learning using Microsoft Excel.  Inevitably, as students progress, Excel starts to appear quaint as they transition to more powerful systems like R or Python. After a while, however, a lot of people end up coming back and realizing that maybe Excel isn’t so bad after all. 

Although data scientists sometimes look down their nose at Excel, we’ve put together a tutorial, along with data scientist David Taylor, that shows that you can do a suprising amount of complex analysis just using basic Excel fucntions. 

We set out to to prove this by walking folks through an analysis of baby names from California based on the United States Social Security Baby Names Database. The Social Security database, which goes back to 1880, has some weird and wonderful anomalies.

For example, David uncovered an interesting (and bizarre) trend of boys’ first names ending in the letter “n” during the latter half of the 20th century.  This is reflected in the provided GIF, which also covers the tragic rise and fall of the letter “d.”  “N”s truly meteoric ascent is one of many fun, and telling, insights that can uncovered through applying some simple Excel functions to a large data set. 

Also, if you explore the names, you’ll notice a trend that impacts girls names far more than boys: a quick rise from obscurity to popularity, then as the name becomes too trendy, a descent to obscurity again.  With boys, you generally see more steady patterns (like the letter “n” at the end of names). Look up names like Mason, Ethan and Jayden, you’ll see them all rise from obscurity to prominence in the 2000s, and many of them are just starting to dip again as of 2013.

Did you know that babies only started routinely having social security numbers starting in 1986? You can see this in the data too. For example, a baby would be much more likely to have the name “Peter” on his official documents than the nickname “Pete”. But if, when a young man or older filled out a tax return or applied for Social Security, he would be more likely to use the name he went by in day-to-day life, which might be a nickname he’d been called since he was a boy.

Finally, as one would expect from anecdotal experience, there is more diversity in names now than there was fifty years ago. In addition, female names are more diverse than male names. Perhaps parents want their girls to stand out more? The changes in diversity track pretty closely between the sexes. This suggests that the difference is due to something intrinsic to the difference between girls’ and boys’ names, not momentary trends. Perhaps the explanation is simple: there is more diversity in girls’ names because there are more spelling variations in girls’ names, like ‘Ann’ and ‘Anne’ and ‘Anna’.

All of these insights were uncovered using intermediate Excel functions like pivot tables, pivot charts, ratios, and filters. We hope this tutorial will help beginners (and experienced data scientists alike) learn some basic steps to take when they first confront a huge chunk of data and want to do some exploratory analysis.