Adding a trendline to graphs in Excel
Quick instructions on how to assemble graphic in Excel that include standard deviations and trendlines (i.e., regression lines).



Abstract
Enter data in cells
You should already know how to do this much!


Calculate averages

  • Select the first cell in the column to the right of the individual trials.
  • Go to Insert, then Function (or hit the Fx button on the toolbar).
  • Choose Average from the options.
  • Highlight the first run of cells to average and hit OK.
  • Copy the first average into the rest of the cells (this will copy the formula, not the number, so each average will be calculated individually).


Calculate standard deviations

Do essentially the same thing in the next column over, except this time choose the Standard Deviation function instead of Average.


Plot graph #1 (all five individual trials)

  • Highlight all the cells to be included in the graph (this includes both X and Y values).
  • Go to Insert, select Chart (or hit the Chart button on the toolbar).
  • Select Scatterplot, then OK.


Add trendlines

  • Click on a value in a "run" on the graph (this should highlight the entire series).
  • Right click.
  • Select Add trendline.
  • "Linear" is the default, so OK.
  • Repeat this for all trials.


Plot graph #2 (average of trials)

  • Highlight the cells to be included in the graph (this includes both X and Y values, i.e. Force and Average Deflection).  Since there are columns in between, use the Control key to separate what is highlighted.
  • Go to Insert, select Chart (or hit the Chart button on the toolbar).
  • Select Scatterplot, then OK.


Add trendline

Same as before.


Add standard deviations

  • Click on a value in the "run" on the graph to highlight the entire series.
  • Right click.
  • Select Format data series.
  • Go to Y Error Bars.
  • Choose Both (positive and negative running), then OK.
You're finished!


Copyright Alexplorer.
Back to the index