Lesson 3: Olympic medals

In this lesson you will use some data from the 2016 Rio Olympic games to create attractive, meaningful charts and graphs.

Download lesson files

Instructions

1. Format your spreadsheet appropriately:

  • Merge & center cells A1:G1 & make the title larger
  • Add borders around all cells in the table
  • Make column headings bold, change the background to black & the text to white
  • Make the contents of C15:G15 and G4:G14 bold

2. Use a function to calculate the medal totals for:

  • Gold, Silver & Bronze
  • All 11 nations (including Other Nations)

Bar Charts

3. Create a vertical bar chart to show the medal totals for the top 10 nations. Using the default settings it should look like this.

Edit your bar chart to:

  • Add a title: “Top ten medal-winning nations at Rio 2016”.
  • Format the bars so that they are appropriate colours.
  • Set the horizontal axis label to Nations and the vertical axis label to Medals won.

Pie Charts

4. Using only the values in Nation Code and Gold, create a pie chart to show the breakdown of the top five gold medal-winning nations.

  • Add a title: “Top Five gold medal-winning nations at Rio 2016”.
  • Using the flag images provided, format the segments of the pie chart. Set the transparency of all but the top country’s segment to 60%.
  • Display data labels, showing Nation Code and the number of medals
  • Do not display a legend.

5. Create a pie chart to show the medal breakdown for Japan.

  • Add an appropriate title.
  • Format the segments of your pie chart using appropriate colours.
  • Do not display a legend.
  • Display the series name and the number of medals outside each segment of the chart.

Evaluating a chart

Take a look at the two versions of the same chart shown below and answer the questions that follow.