In this section, use the prepared data Summer Olympics 3 Countries Medal Study and format the information on Canada from:

to:

The process is very similar on Excel and on Google Sheets. pdf printable: Formatting

Formatting

1. Transpose the data. To do this, open a new sheet (+ on bottom left of the screen). Click and drag from A1 through to I10. Press command C or control C for copy. Click on the new page. Right click in cell A1. Choose ‘paste special’ and then ‘paste transpose’ to transpose the table.
2. Row titles. Select row 1 and make bold.
3. Column titles. Select column 1 and make bold.
4. Gold medals. Select only A2 through J2 and colour gold.
5. Silver medals. Select only A3 through J3 and colour silver.
6. Bronze medals. Select only A4 through J4 and colour bronze.
7. Rank. Select B8 through J8 and choose ‘format, conditional formatting, colour scale’ The darker the green, the closer to the ‘maximum value’. Notice how different the rank is in 1984.
8. Border. 1984 is quite a striking year according to this data. Was there something going on? Let’s box the data to draw attention to this year. Select G1 through G9 and play with the border tabs until you’ve got the border you want.
9. Wider rows. Now lets select the rows by clicking on the header of row 1 through row 9. Lets use the arrows on the row header to make the rows wider.
10. Centre cells. Now let’s find the tab that lets you centre the contents – left to right and up to down.
11. Wider/Narrower columns. Now let’s select the columns by clicking on the header or columns A through J. Use the arrows on the column header to make the columns as wide/narrow as you like.
12. Wrap text. Some words are too big for their cells. Select column A, and choose ‘text wrapping’.

Formulae

1. First, let’s calculate the total competitors for each year. In B7 type =B5+B6 then press enter. This should add the male, female competitors.
2. Now, lets use the ‘autofill handle’. Click on B7. Notice the small box on the bottom right. Click on the box, and drag it from B7 through to H7.
3. Hide rows 5 and 6. Right click on the header for row 5, and select ‘hide’. The same for row 6.
4. Now in L1, type ‘averages’ and make bold.
5. In L2 type =average(B2:J2) and press enter. Use the autofill handle from L2 through to L9, and box this column.

Summary Page

On a new page, let’s enter the rank from each country by linking the sheets together. First, enter years and countries as shown in the image below. Then, in the appropriate cell,

• type = to start a formula;
• go to the page where the value is and click on the cell required;
• return to the summary page and press enter.

This process links and copies the data from that cell into the summary sheet. Use the autofill handle to complete the row for Great Britain, then repeat for the other two countries.

Video 1:

Video 2: