A Guide to Making Line Graphs Using Microsoft Excel for BIOS 100 Using a PC

Part 1:  Curves        Part 2: Best-Fit Straight Lines

Just say you've just taken your data for an absorption spectrum and you need to generate the curve. First of all, open MS Excel and enter your data in columns like so (click here to download the dataset).  Next, put the mouse on cell A1, then click and hold the left mouse key and drag until all of the data set is blocked out like so:

Next, click on the Chart Wizard Button (or click on the Insert menu then select the chart option). 

You will then be presented with a series of options

• First, you must select your graph type.  Select XY (Scatter).
• Second,  you need to select a type of XY Scatter plot.  You want to select the option in the upper left that is just a series of dots with no line. 
• Click Next to move onto the next window

Click on the Next Button and you will be presented with a rough sketch of your graph

Click Next

The window will now have selections for graph title and labels for the X and Y axes.  Fill these in and click Next

A fourth window will pop up.  Just hit the Finish button and the screen should look like this:

Now, we need to get the best-fit straight line.  To do so:

• Place the mouse over a datapoint on one of the lines (in this case, the yellow triangles) and Right Click.  A menu will come up - select Add Trendline

A new window will pop up.  Do the following

• Select the type of line you wish.  Since this is a best-fit straight line, select linear
• Click on the Options tab and then select Display Equation on Graph

Click OK, your graph should look like the below left.  Repeat the same process for the other datasets.  Your graph should look like the below right

You're almost done.  Now all you have to do is label the data points.  

Put the mouse over the Linear (Series 3) and left click.  The entire legend will be selected.  Wait a second and left click again.  Now, just the Linear (Series 3) will be selected.  Click the Delete key on your keyboard and it will disappear.  Do the same for the other Linear sets, so your graph looks like this:

Now, all we have to do is label each series.  Put the mouse over a white area on the graph (i.e. an area near the corner where there is nothing) and select Source Data.  A new window will pop up - select Series1 and type in the data label (in this case, it is 4º C - you can make the degree sign ( º ) by holding down the Alt key and typing 167 and then letting go of the Alt key.  Neat, eh?)  Repeat for Series2 and Series3.  When you are done, the graph should look like below right

 Now you have a graph with three best-fit straight lines and the slope for each clearly labeled on the graph (i.e. in the equation y = 0.02x + 0.0146 the slope is 0.02)

Feel free to ask your TA or Lab Coordinator if you have any questions