Use the data set Summer Olympics 3 Countries Medal Study which we formatted in the previous section – reference to the data here is the transposed data. (see colourful image on the previous section)

We’ll create two kinds of charts, and format them to present the data as we want it to look. pdf printable: Formatting Charts

Pie Chart for Gold, Silver, Bronze medals

Sum the number of each medal. Type in =sum(B2:J2) (using a transposed table) to an empty cell. Let’s use K2. Use the autofill handle to sum for silver and bronze.

Create the pie chart: click and drag over ‘gold, silver, bronze’. Hold down ‘ctrl’ or ‘command’ while you click and drag over the three totals. Click on the insert chart button.

This brings up something like:

Now lets format it to look something like:

Here are some suggested formatting steps:

Double click on the chart to bring up the chart editor dialogue box:

Let’s select each of those menu items in turn.

Chart style: I chose 3D. It looks good, however mathematically if it is tilted a lot it can distort the visual impression of the data. The tilt isn’t so big, and I’m not trying to persuade anyone to make any changes with this data, so I’m good to go ahead with the good looking 3D pie chart.

Pie Chart: Under ‘slice label’ I chose value, as it is interesting to see the total number of medals. Sometimes it is more informative to choose percentage. Right now, I’m wishing I’d taken the time to include all the summer olympics rather than just a snapshot of them – it is good to invest time in collecting data. Change up the font etc as you wish.

Chart & axis titles: Got to have a title. The title should be informative, precise and reasonably succinct.

Series: Here you can change the segment colours. I chose a gold-ish colour for gold etc.

Legend: Here you can choose between having the sections labelled (default) or having a legend. I chose a legend at the bottom.

Save the chart as an image: Click on the three stacked dots on the top right corner to save, share etc the chart, independent of the rest of the sheet.

Line Graph for Rank

Let’s create a line graph to show how Canada has ranked has gone up and down over the years.

Click and drag over the cells from 1924 through to 2016. Hold down ‘ctrl’ or ‘command’ while you click and drag over the cells containing the rank information. With all 18 cells selected, click on the ‘insert chart’ button. As both data values are numerical, it will bring up a line chart to begin with:

Let’s format it to:

Double click on the chart to bring up the formatting dialogue box and select ‘customize’ which looks different for a line chart than a pie chart:

Now let’s go through each item in turn:

Chart Style: I chose to change the background colour. I didn’t make it smooth, as the line is not a continuous function. The line is only there to emphasize how the ranking has gone up or down. Otherwise, the line does not have any mathematical meaning.

Chart & axis titles: Let’s enter a title to the chart, to the vertical axis and the horizontal axis.

Series: I chose to change the line thickness and the point size & shape. I tried out other things too but in the end left them alone.

Legend: I chose ‘none’ here, as the axes have titles already.

Horizontal axis: I changed the min and max on the horizontal data so that I could see my data clearly. I chose the min ‘1920’ and the max ‘2020’ – a little before and after my data collected. I also changed the font of the title, but as I see that I forgot to put in the title, that had little effect.

Vertical axis: I only changed the font to match the chart title.

Gridlines: This is possibly the most important formatting for a line chart like this. Well calculated gridlines helps people to read the data. Select ‘horizontal axis’. Now, since I’ve gone from 1920 to 2020, my data spans 100 years. Olympics are every 4th. I’d like to show every fourth year from 1920 to 2020. That’s easy, as . We need to include a grid line for zero, so we need 26 grid lines.

Now select ‘vertical axis’. I wanted to show every 5 from zero to 45. Now , add a grid line for the zero grid line – we need 10 grid lines.

Organize your data

Sometimes the data on your spreadsheet is not ready to be turned into a chart. Here’s a video showing how to turn survey data into a google sheet chart.

---