The line of best fit and scatterplots in Google Sheets

Being a maths teacher, I always liked collecting data and finding the strength of the relationship between the data. Making a scatterplot and finding the line of best fit or trendline in Excel was a piece of cake. I could never do it in Google Sheets until I found a solution! It is easy to build a meaningful scatterplot, put in a trendline and find the correlation coefficient within a couple of clicks.

Using #google #sheets it is easy to insert a line of best fit in a scatterplot Click To Tweet

 

Before we delve into how to draw a scatterplot in Sheets, please indulge me as I explain what a scatterplot is and how we find the strength of the relationship between data. If we put a pot of water on some heat and measured the temperature at certain times, we would get two pieces of data that are related to each other. If we add heat longer to the pot of water, the hotter the water will become. This is called a direct correlation. If we collected the data and put it in a table we would have two variables and be able to draw a scatterplot of time versus temperature. The stronger the relationship between the two variables the stronger the correlation is. A perfect correlation is 1 and this can be represented by r2.

When we put data like this into a table and draw a scatterplot in Sheets, we would get a scatterplot that looks like this.

This is not correct as it is treating the data on the x-axis as text and not numbers. Follow these steps to change this:

  • Create a scatterplot by highlighting the data and clicking on Insert > Chart
  • Change the chart style from column to scatter chart
  • Click on Customise
  • Go to Horizontal axis
  • Un-check the box that says ‘Treat labels as text’

 

Click on Series
Check the ‘Trendline box’ and the ‘Show R2’ box.

This now displays a scatterplot of your data, shows you the formula of the trendline or line of best fit and shows you the strength of the correlation.

By following these easy steps students can hypothesise, record data, chart and analyse their results using #google #sheets. Click To Tweet

 

This allows students to gather real data, tabulate it and then present and analyse it in a meaningful way. This ticks the boxes for me as a teacher as the students are able to hypothesise about the relationship between two variables and then test it easily using Google Sheets and a scatterplot.

Check out our other blogs on combining text and numbers in cells, using macros in macros in Google Sheets and tips and tricks in Google Sheets.


Student ICT skills mapping

Students skills and fluency with ICT are just as important as teachers. As students progress through their schooling they are building a certain skill set with using ICT for learning and for life so schools need to consider what this progression looks like and where it is headed. I encourage schools to collaboratively map out students acquisition and mastery of key ICT skills.

VIEW POST

Inserting Images Into Google Forms

**UPDATED VERSION OF THIS POST available here. The new version of this post includes updated instructions and screenshots.** In this short Video Mike shows you how to easily insert images into Google Forms VIDEO HIGHLIGHTS: 1:06 See how to drop logos into your Google Forms 1:19 Uploading different images into a

VIEW POST

Top tips for working from home by a remote working team

  With Google Drawings a lot of people don’t realise that it actually sits inside Google Docs. Inside Google Docs you actually have the Google Drawings tool. Under insert you’ve got Drawings and now there are two places that you can get a Drawing from. The ‘New’ is going to

VIEW POST

Here’s What
Our Clients Say

PR Industrya

Client

In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...

 

Sign Up Now
To Get The Latest Updates