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.

Select Insert→Chart→X Y (Scatter). This will insert a basic scatter plot of the data into the current sheet. (In older versions of Excel, this would invoke a Chart Wizard.) Alternatively, you can choose this option from the Insert tab of the task ribbon

insert a scatter plot from the menu

insert a scatter plot from the task bar

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 typedoes not do what you think!

Once the plot is inserted, you will want to format it properly. You should:

  • Add a descriptive title
  • Label the x and y axes appropriately
  • Format the axis values to show the correct decimal places
  • Adjust the start and end values for each axis to focus on your data

You can do this in two ways: select Format→Format Selection to enable the formatting pane within the spreadsheet window; or use the Chart Design and Format tabs of the task bar.

insert a scatter plot from the menu

insert a scatter plot from the task bar

Add any missing elements to your plot. You can double-click on any text element to edit it directly within the chart. Right-click on the x or y axis to access the formatting pane; alternatively, click the Format Pane button in the Format tab of the task bar. This will allow you to change the axis limits, increments, and number formatting, as well as adjust font size and colour.

insert a scatter plot from the menu

The other scatter plot types interpolate line segments between points in order; sort the data by increasing x value first 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, as described in the preceding section.

format the plot axes and scales

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.

You can use either the ‘Quantity symbol / unit’ (quantity divided by unit) or ‘Quantity symbol (unit)’ notation. Use the latter if you have units like g/mol

Using the fields under Axis Oions, set the y-axis to display values from 1.5 to 3.5, with major divisions every 0.5 units. Likewise, use the Number option 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 on the different parts of the chart and explore the various contextual menu items that come up to remove the grid lines, set the background colour, and change the symbols to open, dark blue squares.

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...