Spreadsheets - Charts and Trend lines

Data analysis is like deciphering a secret code. Often, the raw numbers don't reveal the whole story. This is where scatter plots and trendlines come in. They act as powerful tools to visually explore relationships between two sets of data. By plotting points and drawing a best-fit line, scatter plots help us identify trends, correlations, and potential outliers. Let's delve into this world of visualization and see how to harness the power of scatter plots and trendlines to unlock insights hidden within your data.

In this tutorial we will plot the %MgCl2 versus the density of a series of solutions.  The data is as follows.  Cut

% MgCl2 Density (g/mL)
10 1.084
14 1.120
18 1.154
20 1.173
23 1.210
26 1.261
29 1.270

 

Step 1: Open Google Sheets

  1. Start Google Sheets: Click the following link to open a Google Sheets.
  2. Click "Blank Spreadsheet" to start a new spreadsheet.

Step 2: Prepare Your Data

  1. Labeling for Clarity: In cell A1, type % MgCL2 and in cell B1 type Density (g/mL). These labels will make your graph easier to understand.
  2. Data Organization: We're studying the relationship between %MgCl2 and density (in g/mL).  In column A (cells A2:A8), enter the %MgCL2. In column B (cells B2:B8), enter the corresponding densities beneath each heading cells. Alternatively you could copy the data from the table above and paste into the spreadsheet (paste into cell A1).

Step 3: Creating the Scatter Plot

  1. Highlighting the Data: Select the data range you want to plot. Click on cell A1 (%MgCl2), hold down the "Shift" key, and then click on cell B8 (density). This will highlight both columns of data.
  2. Chart Time!: Click on the "Insert" menu at the top of the sheet and choose "Chart." A new window will appear with various chart options.
  3. Scatter Plot Selection: Click on the scatter plot icon (it usually looks like circles connected by a line). A scatter plot of your plant height vs water amount will be created and displayed on your sheet.

Step 4: Adding a Trendline

  1. Click on the Chart: Click anywhere on the chart to select it. Then click on the kebab icon (), in the upper right of the chart and click the "Edit Chart" menu item and then "Customize" tab.
  2. Trendline Magic: Select "Series" and then select the "Trendline" checkbox.
  3. Linear Trendline: A new window will appear with trendline options. Choose "Linear" to fit a straight line to your data points.

Bonus Step: Displaying the Equation

  1. Equation Insights: While you still have the trendline options window open, select "Use Equation" under the Label dropdown. This will add the mathematical equation of the best-fit line to your scatter plot.
  2. Customize for Clarity: You can further customize the chart by clicking on different chart elements (like the axes or data points) and formatting them using the options in the chart editor window.

Remember: The trendline represents a general linear relationship between your data points. It may not perfectly fit every point, but it helps visualize the overall trend.

This is a basic introduction to creating scatter plots with trendlines in Google Sheets. As you explore further, you can experiment with different chart types, customize the look and feel of your graphs, and use them to analyze various kinds of data sets.