Advanced Spreadsheets – Topic 5C/C++

Java Python Advanced Spreadsheets – Topic 5

Charting – Review and extension activities

In the following activities you will revise what you learned in Term 1, and extend your skills.

Note: in some cases instructions for Windows devices and Mac may differ

Windows PC instructions will be shown at the left in green

Mac instructions will be shown at the right in blue

Activity 5a – Column chart

Use the file Wages.xlsx for this activity and create the chart shown.

1 Select the range of data to be charted: A2:B14 and D2:D14

Windows PC

Use the Ctrl key + select data

Mac

Use the Cmd key + select data

2 Click on the Insert Tab and choose 2-D Column - Clustered Column chart.

3 Change to Style. 11 (Chart Tools-Design-Chart Styles) and format as shown.

Tip: To change any chart element: Right click on it in the chart, and click the name of the element in the drop-down menu – this will open the Formatting Pane.

To change the Alignment of the X axis labels: Right click Axis, click Format Axis in the drop-down menu, in the Formatting Pane choose Size and Properties, choose Alignment, set Text Direction to Rotate all text 270o

Helpful keyboard shortcuts for selecting when working with large datasets:

CTRL+SHIFT+HOME KEY extends the selection to the beginning of your data

CTRL+SHIFT+END KEY extends the selection to the end of your data

CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell

*On a Mac, use the Cmd key instead of the Ctrl key.

4 Move your chart to another sheet, and name the sheet Activity 5a:

a Click on the chart, click on the Chart Design tab and choose Move Chart.

b Choose New sheet in the Move Chart dialogue box.

c Name the sheet Activity 5a and click OK.

5 Click the Activity 5a sheet and check your chart.

6 Save your file.

Activity 5b – Line Chart

Continue using Wages.xlsx

1 Select A2:B14 and insert a Line with markers

2 Format as shown, and Advanced Spreadsheets – Topic 5C/C++ move it to a separate chart sheet with the name Activity 5b.

Tip: Using the Formatting Pane, you will need to modify the Markers and Lines of the data series

3 Right click on the line in your chart and add a trend line. Format it to display the differences clearly (change the colour and add an end arrow).

A legend is now needed to explain the meaning of each line. Check that your display is clear to read.

Activity 5c – Line Chart 2

1 Select data for the categories as shown in the chart and insert a Line chart with markers.

2 Select the Y-axis (right click or double click) to format this axis. In Format Axis, Axis Options, Bounds, set the Minimum to 6000 and the Maximum to 7600.

3 Place the chart neatly at the side or below the spreadsheet data.

4 Add your name, class, ZID and the activity number to the footer of your worksheet.

Make sure you click on one of the spreadsheet cells, so that your footer is included on the spreadsheet and not on the chart.

5 Preview and ensure that all data and the chart fit on one page.

6 Save your file.

Combo (Combination/Double Axis) Charts

Activity 5d – Combo Chart

Use the file SOI.xlsx for this activity.

1 Open the worksheet labelled SOI.

2 In the first cell under the column heading SOI June-Nov Average calculate the average SOI from June to November for each reading, R01 to R83 (AVERAGE function).

3 Use the Temperature worksheet and follow the same instructions as above to calculate the: June –Nov Average Temperature.

4 Use the Rainfall worksheet and Sum the Total June-Nov Rainfall for each reading, R01 to R83 .

5 Go to the worksheet WinterSpr SOI Rain Temps.

6 Copy the June-Nov Average SOI (calculated in the SOI worksheet) and Paste Link into the second column of the WinterSpr SOI Rain Temps sheet.

7 Copy the June-Nov Average Temperature from the Temperature sheet and Paste Link into the WinterSpr SOI Rain Temps sheet.

8 Copy the June-Nov Total Rainfall from the Rainfall sheet and Paste Link into the WinterSpr SOI Rain Temps sheet.

9 Display all values with one decimal place

10 Save your file, ready for the next part of this activity