Creating Graphs Using a Spreadsheet

Nothing beats a good data plotting program for graphing (check out SciDAVis for an easy to use, free, multi-platform solution), but you can also create a variety of graphs using spreadsheets such as the one in Open Office or Excel (Microsoft Office). What follows works for Excel 2007 and Open Office 4. Other versions may have different menus and options. Here's how to take your tabular data from lab and create a graph. These instructions assume you will set the independent axis on the horizontal and the dependent axis as the vertical. This is the typical case but there are exceptions (see note at end). Remember, the independent axis presents the input parameter you set (e.g., a power supply voltage or a mass) and the dependent axis presents the output parameter (i.e., the item you are interested in and have measured as an outcome such as a resulting current or change in position).

1. Open a new worksheet. In the first column (column A), enter the text for the legend. This is particularly important if you're plotting multiple datasets on a single graph. Starting in the second column (column B), enter values for the horizontal (independent) axis on the first row of the worksheet. In like fashion, enter values for the vertical (dependent) axis on the second row. For multiple trials, enter the values on subsequent rows. For example, if you are setting a series of voltages in a circuit and then measuring the resulting currents, the voltages would be in row one and the currents in row two. If you changed the circuit components, reset the voltages, remeasured the currents and wish to compare the two trials, then the new set of currents would be in row three and so on. Each of these rows would have their identifying legend in column A with the numeric data starting in column B. Specifically, the legend text for the first data set would be in cell $A$2 and the numeric values would be in cells $B$2 through $X$2 (where X is the final data column), for the second set the legend text would be in cell $A$3 and the numeric values would be in cells $B$3 through $X$3, etc.

2. Select/highlight all of the data (click the first cell, in the upper left corner, and drag the mouse over all of the cells used).

3. Select the Insert menu and choose Chart. Ordinarily you will use an XY Scatter chart. There are other options but this is the one you'll need in most cases. A simple Line chart is not appropriate in most cases. You might get a graph that "sort of" looks correct but the horizontal axis will simply represent the measurement sequence (first, second, third) rather than the value you set.

4. You can customize the appearance of the chart. In general, you can edit items by simply double-clicking on the item or by using a right-mouse click to bring up a property menu. This will allow you to add or alter gridlines, axes, etc. You can also stipulate variations such as using data smoothing, adding a trend line, etc. It is possible to change the axes to logarithmic or alter their range; and fonts, colors and a variety of secondary characteristics may be altered.

5. Once your chart is completed, you may wish to save the worksheet for future reference. To insert the chart into a lab report, select the chart by clicking on it, copy it to the clipboard (Ctrl+C), select the insertion point in the lab report, and paste (Ctrl+V).

6. In those odd instances where you need to reverse the dependent and independent axes such as a VI plot of a diode where currents are set and resulting voltages are measured, but you want the voltage on the horizontal, some spreadsheets have an axis swap function. If not, you'll need to swap the data ranges for the chart axes. For example, following the instructions above, your indendent/horizontal axis is row one. The data are in cells $B$1 through $X$1. The dependent data are in cells $B$2 through $X$2. These ranges can be seen in the chart's Data Series or Data Range menu or dialog box. It will say something like: "X Values: =Sheet1!$B$1:$F$1" and"Y Values: =Sheet1!$B$2:$F$2". Simply swap the row numbers so that it says  "X Values: =Sheet1!$B$2:$F$2" and "Y Values: =Sheet1!$B$1:$F$1".

Here is an example worksheet showing a plot of two resistors.


[Home] [MVCC Home]

2016 Jim Fiore