Excel Pivot Tables At the Olympics

Are you too old to compete in the Olympics? Maybe you’re not as bendy as those 16-year-old figure skaters, but there might be other sports with athletes about your age.

Olympic Athlete Data

Athlete bios are posted on the Vancouver 2010 Winter Games website, and I compiled that data, then created a few Excel pivot tables, to analyze the athletes’ ages.

  • Which Winter Olympic sports have the oldest athletes?
  • Which countries send the youngest participants?
  • Do similar age groups compete in different sports?
  • Who wears the wildest pants?

With our Excel pivot tables, and some pivot table grouping, we can find the answers to those pressing questions. Well, maybe not the pants issue, but let’s look at the age questions.

Create Excel Pivot Table

Using the Olympic athlete biographical data, I created an Excel pivot table with:

  • Sport in the Row Labels area
  • Age in the Values area

The pivot table default for the Age field is to show the Sum of Age, and that isn’t too helpful for this analysis.

Olympic2010Age01

Show Maximum Values in Pivot Table

Instead of using the Sum function, I’d like to see the Maximum age for athletes in each sport. Fortunately, there is a quick way to change the summary function that is used for a pivot table value field.

To change the summary function in an Excel pivot table Values field, follow these steps:

  • Right-click on one of the values in the Age field.
  • Click Summarize Data By
  • In the pop-up menu, click click Max
Show Maximum Values in Pivot Table
Show Maximum Values in Pivot Table

Sort the Values

After changing the summary function to Max, the pivot table shows the highest age in each sport.

Next, I can sort the list in descending order by Age. That will highlight the sports with the oldest competitors.

  • Alpine skiing is at the top of the list – that was a surprise!
  • Short track speed skating has the lowest maximum age

Olympic2010Age03

Show a Count of Athletes

Maybe there’s only one Alpine skier, and they’re really old.

To compare the number of athletes in each sport, I’ll add the athlete’s name field to the Values area, and it will appear as Count of Name.

Show a Count of Athletes
Show a Count of Athletes

Except for the last two items, there’s a good number of athletes in each sport, with Alpine Skiing as the second largest group.

See Athlete Age by Country

If we replace Sport with Nationality in the Row Labels area, we can see the maximum age and athlete count for each country.

Olympic2010Age05

That 51-year-old alpine skier is from Mexico, and is the only athlete from that country. Coincidentally, Great Britain sent 51 athletes, but the oldest is 45.

To see the average age per country, you can change the summary function to Average, then sort the ages in ascending order. The lowest average ages are from countries with a small number of athletes.

Olympic2010Age06

Filter by Count of Name

To see the average ages for the larger contingents, we can filter the countries by the Count of Name value. Click the drop down arrow for the Nationality field, click Value Filters, then click Top 10. I selected to see the Top 10 items by Count of Name.

Filter by Count of Name
Filter by Count of Name

The pivot table now shows only the countries with the largest number of participants, sort by average age. There’s not much difference in the average ages among countries in the top 10 list.

Olympic2010Age08

That’s not too encouraging! If I want to compete in the next Winter Olympics, I should move to Mexico, and take up alpine skiing.

Age Range in Selected Sports

Finally, let’s see the age range in a few of the ice sports. I removed Nationality from the Row Labels, and added Sports. Then, I filtered the list, to show only four of the sports – curling, figure skating, ice hockey and speed skating.

Olympic2010Age09

Figure skating has the narrowest age range, and curling has the widest. Maybe I can stay in Canada, and learn how to curl.

Instead of showing the individual ages on the chart, I can group the ages into 5 year bands. Right-click on an Age, and click Group. Then enter 5 in the By box, and click OK.

Olympic2010Age10

The chart looks less like the Rocky Mountains, and it’s easier to see the age ranges for each sport.

Olympic2010Age11

Download the Data

I’ve saved the athlete bio data in a zipped Excel file that you can download, and use it to create your own pivot table. Let me know if you make any surprising discoveries.

To get the sample file, go to the Excel Sample Data page on my Contextures site. Then, for details on what’s in the file, go to the section named Sample Data – Winter Athletes.

The zipped Excel file is in xlsx format, and does not contain any macros.

Congratulations

And congratulations to Alexandre Bilodeau, from Canada, for winning a gold medal in Men’s Moguls at the Vancouver 2010 Winter Olympics.

Yes, it was the first Olympic gold ever won by a Canadian on home soil.

___________

One thought on “Excel Pivot Tables At the Olympics”

Leave a Reply

Your email address will not be published.

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