Introduction

Recap the last lesson and what we did to create our database in 2Investigate. What did we set up first?  Our record cards. What is a record? What is a field? What different views could we have for our database? What could we do with the data once we had inputted it? We looked at using the Sort, Group and Statistics functions to answer questions about our data.

Today we are going to turn our data into charts. Why might we want to do that? Sometimes looking at data in a chart makes it much easier and quicker to interpret the data. For example, in a bar chart, it is easy to see at a glance which bar is the tallest and which category that represents. Charts are also great for showing large amounts of data, but they must be clearly labelled so they are easy for people to understand.

 

What types of charts do you already know about?

 

Barcharts

Each bar in a bar chart represents a different category and its height or length shows its value or quantity. The longer the bar, the more things there are in that category.

You might use a bar chart to represent pupils' favourite meals in your school or the most common shoe size in the class.

 

Line charts

Line charts can be used to display data that has been gathered over a period of time.

The data is plotted using points, which are then connected together to form a line. This line shows how the quantity changes over time.

A line chart is a good choice for showing changes in temperature over a year or financial information, such as changes in the cost of items or services over a period of time. 

 

Pie charts

Pie charts are simply circles divided into sections. Each section represents a fraction of the total data. This enables you to easily compare sizes or to see how a total is shared between different categories.

 

Getting your data set

The chart options in Purple Mash are currently fairly limited, so you are better off switching back to Google Sheets or Excel for creating charts. It is also pretty important to have clean data for the charts to be accurate and meaningful. To assist with that, we have created a slimmed-down set of data that contains accurate data for nine countries with the same category fields that the children have researched.

Open the data spreadsheet and ask the class to do the same by typing in this URL: tinyurl.com/countryweather

 

 

Once the sheet opens, you will need to either make a copy of it (for Google users) or download it as an Excel file (for Microsoft users). Both can be done from the File menu. You or the students will not be able to edit it (which includes adding charts) unless you do this.

 

 

If using Google apps, You will need to be signed into your Google account in another tab to be able to make a copy. Then rename the copy and save it to your Google Drive by clicking Make a copy.

 

 

If you are using Microsoft Office and you have an offline version of Excel, you will be able to simply download and open the file.

If you only have Office 365, you may be able to save it straight to your OneDrive if you have the option, and then open it from there.

 

 

If you cannot save directly to your OneDrive, open OneDrive in another tab, choose Upload > File and browse for the Excel sheet that you downloaded.

 

 

These are all good file management skills for the students to practise!

Read on for guidance on making charts in Google Sheets and Microsoft Excel (see further down the page).

 

Making charts in Google Sheets

Creating charts in Google Sheets is fast and easy. Select the first column of data that you want to include in your chart. This is likely to be column A which contains the country names. Click on the column header (where the A is) to highlight the whole column.

 

 

Then hold down the Ctrl key on your keyboard and then select the column header for the second set of data you wish to add to your chart. In this example we've added Average July temperature, so we clicked on the C header.  This will highlight the second column.

 

 

Then, with both columns selected, click Insert > Chart from the top menu bar.

 

 

A chart will immediately appear on the page. You can drag it from the centre to move it around and into a space where the table data is still visible.

 

 

In the panel on the right of the screen, there are lots of options for customising the appearance of your chart. We're going to keep it fairly simple, but feel free to experiment with some of the other settings.

Let's just add a title and some labels to our axis. Select Chart and axis titles and first add your title in the Title text box.

 

 

Then switch the Chart title drop-down box to Horizontal axis title and add that. Repeat for the Vertical axis title as well.

 

 

 

You should get a chart that looks a bit like this.

 

 

 

Other settings will let you customise every aspect of your chart if wanted. 3D bars can be found in Chart style, you can change colours in Series > Fill colour. Data labels can be found in the same section as well. Feel free to experiment with other things too.

 

Making charts in Microsoft Excel

Click and highlight the first set of data to include in the chart. Here it is the names of the countries. Highlight from cell A1 to A10 only.

 

 

Then, while holding down the Ctrl key, highlight the second set of data for the chart. Here it is the average temperature in July. Highlight cells C1 to C10.

 

 

Then go to the Insert menu and click on the bar chart icon.

 

 

The chart will appear in your spreadsheet.

 

 

You can drag it around to position it and also resize it from the corner handles. This will also stretch out the axis to make it look better.

If you double-click the chart, formatting options will appear at the side of the screen. Here you can do lots of things such as edit or turn on or off the title and axis labels, format the text, adjust the scale of each axis, add and format the grid lines on the chart, alter the bar colours etc.

In Chart title, check it is turned on and add your title, format it if you wish.

 

 

In the horizontal and vertical axes sections, scroll down to find Axis title, turn it on and add the label.

 

 

In Series, you can change the fill colour of the bars.

 

 

Your chart might look a bit like this when complete.

 

 

Interpreting your charts

It's really easy to take the charts out of Google Sheets or Excel and put them into a different kind of document, so the students can add some sentences about their chart to show their understanding of what it shows. Simply click on the chart in Google Sheets or Excel and copy it by holding down Ctrl+C together on your keyboard. Then open any kind of document that allows you to place images and text together. We recommend Google Sheets or Powerpoint as they are landscape by default, which is a better fit for the chart, and they both allow free placement of text boxes and images.

Paste your chart onto the page by holding down Ctrl+V together. Explain that these keyboard shortcuts for copying and pasting are universal and should work in almost all software that allows it.

If needed, add a text box and ask the students to add a few sentences that explain their chart and make some statements about what it shows.

 

Google Sheets example

 

Powerpoint example

 

Save your work when complete.

 

Plenary

Ask the students - What is the difference between bar charts, pie charts and line charts? When would you use each? 

Take a look at your chart:

 

• Does the chart accurately represent the data that you have collected?

• Do you understand it?

• Will others understand it?

• Does the chart have a title to explain what the data is showing?

 

Ask students to share which database field they used and which chart they created. If you have the facility you could display their work on the screen from the shared area. What statements did you include with your graph to analyse the data?