PChem Teaching Lab

Using Excel 2010 - Plotting Graphs

The analysis of experimental results often involves the plotting of graphs. To do this in Excel is straightforward and will demonstrated using the data used for the Adding Experimental Data section of this tutorial.

1. Click on the Insert tab at the top of the spreadsheet.

2. Click on Scatter followed by the icon for the scatter chart with no lines shown.

3. The graph will then appear on the page.

4. Click on the Layout tab under Chart Tools.

5. Click Axis Titles - Primary Horizontal Axis Title - Title Below Axis.

6. A box labelled Axis Title will appear at the bottom of the graph. Highlight the text and type the x-axis label. In this case this is conc/ng ml-1.

7. Click Axis Titles - Primary Vertical Axis Title - Rotated Title.

8. A box labelled Axis Title will appear at the left of the graph. Highlight the text and type the y-axis label. In this case this is Absorbance.

9. Click on the Format tab under Chart Tools.

10. Select Vertical (Value) Axis Major Gridlines from the drop down menu.

11. Hit the Delete button on your keyboard.

12. Select Chart Area from the drop down menu.

13. Click Format Selection.

14. Click on Border Colour, select No Line and then click Close.

15. Your spreadsheet should now look like that opposite.
Finally click on the current chart title (absorbance in this example) and replace it with a more informative title, i.e. do not just re-iterate what the the axis titles are.
The graph is now in a format suitable for the Level 4 Teaching Laboratory.

A Note about Markers vs Lines

A general rule-of-thumb is that graphs should be displayed with data points and without lines connecting the data points (as shown in the example). When you connect two data points with a line you are claiming that the relationship between the plotted parameters is defined by that line (between those points). Thus for the example above, if the data points were connected by lines then you would be claiming that the linear relationship between the 2nd and 3rd data points is different to the relationship between the 3rd and 4th data points (since clearly the lines connecting them would not have the same slope and intercept).

An exception to the above rule is when there are too many data points on the plot for the individual data points to be (clearly) distinguished. In this case the data should be shown as a line connecting the data points and with no markers. One situation where this typically occurs, is when displaying imported spectra.

Add a Trendline (with Equation) to a Graph

Using Excel 2010 - Contents Page

1.	Click on the Insert tab at the top of the spreadsheet.
2.	Click on Scatter followed by the icon for the scatter chart with no lines shown.
3.	The graph will then appear on the page.
4.	Click on the Layout tab under Chart Tools.
5.	Click Axis Titles - Primary Horizontal Axis Title - Title Below Axis.
6.	A box labelled Axis Title will appear at the bottom of the graph. Highlight the text and type the x-axis label. In this case this is conc/ng ml-1.
7.	Click Axis Titles - Primary Vertical Axis Title - Rotated Title.
8.	A box labelled Axis Title will appear at the left of the graph. Highlight the text and type the y-axis label. In this case this is Absorbance.
9.	Click on the Format tab under Chart Tools.
10.	Select Vertical (Value) Axis Major Gridlines from the drop down menu.
11.	Hit the Delete button on your keyboard.
12.	Select Chart Area from the drop down menu.
13.	Click Format Selection.
14.	Click on Border Colour, select No Line and then click Close.
15.	Your spreadsheet should now look like that opposite. Finally click on the current chart title (absorbance in this example) and replace it with a more informative title, i.e. do not just re-iterate what the the axis titles are. The graph is now in a format suitable for the Level 4 Teaching Laboratory.