Plotting & Charts:

A useful way to view and present your data is with a proper graph, which provides a visual summary of your experiment. Historically, Excel has been very poor at producing scientific graphs, although the situation has improved considerably. Excel provides many different types of charts, which are primarily intended for business use. The chart type appropriate for calibration curves is the X-Y scatter plot. We will use this tool to plot the data generated in the preceding exercises for the equations y = 2x + 5 and y = x2 + 2. In the process, you will learn how to:

  • produce simple graphs (exercise 1)
  • specify titles, legends, and axis labels (exercise 1)
  • change the axis numbering scheme (exercise 2)
  • specify the correct number of decimal places (exercise 2)

Tips & links:

Download an Excel file of the data

View a sample of a properly formatted graph

Skip to exercise 1

Skip to exercise 2

Exercise 1:

Open the spreadsheet from the previous exercise, and select the data for the straight-line equation. You can do this by (a) click-dragging across the cells containing the data (B1 to C16); or (b) by click-dragging (or clicking while holding down the shift key) on the label buttons for columns B and C at the top of the spreadsheet window. Note that the values for the x variable are in the selected column on the left, while the values for the y variable are in the selected column on the right:

Excel Chart Wizard

Skip to exercise 2

Use a scatter plot with no connecting lines for a calibration curve

Do not use Excel's Line chart types for calibration curves - this type does not do what you think!

  1. Select Insert→Chart. The Chart Wizard dialog box will appear.
  2. There are many types of charts to choose from. Select the XY (Scatter) plot and click the Next button.
  3. We can choose to plot multiple graphs on the same chart. We won’t do that here. In fact, Excel has already specified the proper data series for each axis, so we do not need to change anything. You can, however, change the legend text in the Series tab. Click on the Next button to continue.
  4. You can enter chart titles, axis labels, and other display characteristics for the chart. Change what you want, then click Next.
  5. Finally, you can specify whether the chart should be shown in the current worksheet, or whether it should stand alone in a separate worksheet. Click Finish and the chart should appear.

The other scatter plot types interpolate line segments between points in order; sort the data by increasing x value to avoid strange effects!

Exercise 2:

Repeat exercise 1, only this time use the parabola data in columns E and F. Give the graph a title such as “Potential Energy Well”, and give the axes the following labels:

Separation r (nm)
Potential energy U (x1E-19 J)

Once a chart is produced, you can change all aspects of its appearance. For instance, right-clicking on either axis allows you to access the Format Axis... dialog (you can also click on the axis to select it, then use the Format→Selected Axis... menu item):

One weakness of Excel is that it still does not allow mixed fonts, subsrcipts, or superscripts in chart titles or axis labels.

Use letter u for µ, and 1E-06 for 10-6, etc.

Note that it is almost impossible to use the more correct ‘Quantity symbol / unit’ (quantity divided by unit) notation.

Format Axis Dialog

Using the fields in the Scale tab, set the y-axis to display values from 1.5 to 3.5, with major divisions every 0.5 units. Likewise, use the Number tab to display the y-axis numbers to 2 decimal places.

Similarly, set the x-axis to display numbers to three decimal places, and to show negative values as -n.nnn rather than in accounting style as (n.nnn)

Finally, right-click (Mac: cmd-click) on the different parts of the chart and explore the various contextual menu items that come up to remove the grid lines (Chart Options...) and background colour (Format Plot Area), and change the symbols to open, dark blue squares (Format Data Series...)

Note the Logarithmic scale and Values in reverse order items - useful for IR spectra!

The basic plots for both exercises should look like this before adjusting the scales and format:

Specimen Graphs

Continue to Functions & Equations...