Graphing With Excel & Google Sheets

USING EXCEL (Office 365 Version Updated 12/6/2019): 

1) Create a table in Excel with your "x" values in the first column and "y" values in the second column.

2) Highlight all of that data as shown (notice the data labels and data are selected but the title is not-- that's important) and make sure the data is formatted as numeric:

  • Highlight your entire data table
  • do a right mouse click to bring up a new menu
  • select "Format Cells"
  • Select "Number"
  • Change "Decimal Places" to 2

3) Select *Insert* and *Scatter* from the "Recommended Charts" at the top of the screen:

4) Notice no line is present on the graph (yet!). Click on one of the data points on the graph and then right click to select "Add Trendline" from the pop-up menu as shown below

 

4a) Now you have to use your own math skills to determine the TYPE of trendline you want Excel to draw. The data above shows some data higher than average and some data lower than average so let's see what a "Linear" trendline:

Notice that the trendline "agrees" with the data quite well!

4b) What happens if we have data that is definitely NOT linear...for example:

a quick eyeball of the data shows us pretty quickly that it ain't linear. In fact, the altitude is going up pretty close to the square of the time. So let's try that again with a different trendline.

As as before, click insert and add scatter plot:

Hmmmm... something is odd here. You may have noticed that our altitude changes very nearly with the square of the time; which pretty much shouts that we should choose an 'exponential' scale. But that's grayed out... ugh.

It turns out that the mathematical model that excel uses to make an 'exponential' trendline won't work if the initial value is (0, 0).... it has to do with logs or some such.

The GOOD news is that selecting a "Polynomial" Trendline works very nicely (see above).

If your data works BEST with an "Exponential" Trendline you'll have to delete the first line of data if it is (0,0) as shown below:

 

5) Now you have to add labels and such by clicking on the blue "+" sign as shown:

With labels edited:

Notice that you can edit colors, line types, data marker shapes and all manner of things. Try not to spend TOO much time on that as it can be a bit of a time sink!

6) NOTICE that you can cut and paste the graph out of the current page into a brand new tab where it will be a little easier to adjust/edit/print (The graph is linked so if you change the data it will change the graph too

 

GRAPHING WITH GOOGLE SHEETS:

NEW FORMAT (1/19/2018)

1) Go to Google Docs, Select New and Google Sheets

2) Type or paste in your data with x values in the first column and y values in the second column as shown below:

3) From the Google Sheets Menu Bar select <Insert> <Charts> and the default bar graph appears (we pretty much NEVER use bar graphs by the way):

3) Change the graph type to Scatter by clicking on the "Chart Type" drop down list in the upper right part of that chart screen. The chart will change:

4) Now we have to add a line-of-best-fit (Trend Line). Double click on one of your data points on your Google Chart. The following screen appears.

5) Use the slider bar to scroll down a bit until the Treadline Option appears. Select that option by clicking on it and the trendline *should* appear:

6) Make sure that "type" is set to "Linear". There is a bit of a bug here in that sometimes the trendline won't appear on your graph after clicking on the Trendline option box. If that happens, click the drop down button below the "Type" option and reselect Liner... that should do it!

Explore using the various options to format your report for font, font size, font weight, title etc...

OLD GOOGLE SHEETS/CHARTS FORMAT

1) Go to Google Docs, Select New and Google Sheets

2) Type or paste in your data with x values in the first column and y values in the second column as shown below:

3) Highlight/Select the data as shown below:

4) From the menu bar at the top of the screen select "Insert" and then choose "chart" to see the display shown below:

5) Making the Graph

a) Select the "Chart Types" tab at the top of that screen and scroll down to "Scatter Plot" as shown below

 

b) Now select the "Customization Tab" at the top of the screen. Scroll down to the very bottom and find "Trendline". Change the dropdown there to "Linear"

c) Then click on the "Insert" button to place that chart on your sheet

NOTE: Most of the editing/customization is done on this screen BEFORE you click on insert. Although some editing can be done afterwards, you may find it easier to just delete the existing graph and start over from scratch.

NOTICE also that you can cut and paste the graph into a new tab. The data is linked so the graph will change as your data changes