Brief Outline on How to Use Excel-2000                     Yau2001.02.05

 

                                          Part I: Applying Linear Fit to Data Points

                                          Part II:  Plotting Multi-Series of Data on the Same Graph

 

                                                    Part I: Applying Linear Fit to Data Points

                                                                        a)To Enter Data

                                                                        b) To Graph Points (without showing curve)

                                                                        c) To Adjust Graph Before Printing

                                                                        d) To Print the spreadsheet

                                                                        e) To Return to the Spreadsheet to Make Any Adjustments to the Data

                                                                        f) To Obtain Slope and y-Intercept without Going Through Graph

a)To Enter Data:                                                                                                Example for Practice:

Each box in the spreadsheet is a “cell” and is referred by column letter and row number (e.g. A1, A2, B1, B2, etc.)

 

1.  If you don’t want to bother with putting a column heading in the spreadsheet, skip to Step 2 below.  In cell A1 type a brief heading for the data for the x-axis.  If the heading exceeds the size of the cell, press ENTER and continue in the next cell below.  Alternatively, you can expand the width of the cell by placing your cursor on the division border between A and B at the top of the column and drag it to the right.

 

      In cell B1 type a brief heading for data for the y-axis.

 

 

 

 

Type Molarity in cell A1, press ENTER, and type of HCl in A2

 

 

 

 

Type Molarity of H+ in cell B1.  For practice, expand the width of the column so that the heading fits within the cell. 

2.  Enter your data for x-axis and y-axis in columns A & B, respectively, using either ENTER, arrows or TAB. [.00045 can be entered as 4.5E-4].  You may wish to leave a blank row between the data and the heading, but be sure each of pair of x and y data appears on the same row.

Leave one line blank, so begin at cell A4 and enter in column A:

.002, .001, .005, .009

and enter beginning at cell B4:

.0019, .0011, .0049, .0091

3.  If you wish to sort data in numerical order, highlight both columns of data (Take care you highlight only the data!), then click on Data, then click on  AZ¯.  Usually you would want to list data by order of increasing x values.

Sort (by Column A, Ascending)

 

 

b) To Graph Points (without showing curve):

4.  Highlight the data you want graphed.  Click on INSERT, CHART, and select XY (Scatter).  On the right you will see 5 Chart sub-types.  Select the top one (showing points without lines).  Click on Next> at the bottom of the window.  Click again on Next>.

5.  You are in the Titles Folder at Step 3 of 4.  Type in your titles for the graph, the x-axis and y-axis.

Note that by convention, when we say A vs. B, A is on the y-axis, and B is on the x-axis!

Example: Chart Title is Molarity of H+ vs. Molarity of HCl; x-axis is [HCl] and y-axis is [H+].

6. Go to the Gridlines Folder and click on Major gridlines under Value (X) axis.  Go to the Legend Folder and remove the check on Show Legend.  Now you are ready to click on Next> at the bottom.

7.  Choose As New Sheet and click on Finish.  (If you have only a few data, you probably would want to print the data on the same page as the graph.  In that case, choose As Object In,)

8.  Place the cursor in the middle of the graph (not on any points or lines) and double click.  Change the Area color to white, then click on OK.

9.  Click on Chart at the top of the screen, and then on Add Trendline.  Under the Type Folder, choose Linear.  Go to the Options Folder and click on Display equation on chart.  Click on OK.  I recommend you drag the equation to the top right hand corner of the screen, just above the graph.

10. If you wish the line to extend to the y-axis, note the lowest x-value.  Go back to the Trendline window (see Step 8), and go to the Options Folder.  Inside the Forecast, Backward type in your lowest x-value.  This will extend the line backwards by that amount (which brings it to x = zero).  Note that in this folder you have the option of forcing the line through zero.  This is not something you would automatically do.  You must have a good reason to force your line through zero.

In this example, our lowest x-value is 0.001.  So type 0.001 inside the space under Forecast, Backward.

       

c) To Adjust Graph Before Printing:

11. Double click on any part of the graph area, and you will get options on color of border, color of graph area (background.)  If you wish to save on ink or time, choose white for background.  Double click on either axis and you can adjust the scales, ticks, change to scientific notation, etc.

12. To print, click on the Print icon, or click on File, then on Print.

Change background to white, but leave border as gray or black.

 

In this example, there is no need to change scales, etc.

Print the graph.

 

d) To Print the spreadsheet:

13. To print the spreadsheet, highlight the area you want printed, then click on the Print icon, or click on File, then on Print.

Print the spreadsheet.

 

      

e) To Return to the Spreadsheet to Make Any Adjustments to the Data:

14.  Click on tab at the bottom of the screen.

Click on Sheet 1

15. To return to graph, click on appropriate Chart number.

Click on Chart 1

 

f) To Obtain Slope and y-Intercept without Going Through Graph:

This is a quick way to get the slope and y-intercept if you don’t wish to print out the graph.

16. At the spreadsheet, highlight any two blank cells in the same row.

Highlight cells A9 and B9 in Sheet 1.

17. Type =linest (specify cells for x and y data, const, stats).  Const is explained in example to the right, and stats can be ignored unless you want a more detailed statistical interpretation.  You must type the equal sign in front of linest.  Do not type any spaces in between.

=linest(B4:B7,A4:A7) if you want y-intercept calculated normally.

=linest(B4:B7,A4:A7,false) if you want to force line through origin, in which case, b = zero.  In this example, do not force line thru origin.

17. Hold down CTRL and SHIFT and press ENTER.  The slope will appear in the left cell, and the y-intercept in the right cell you had highlighted in step 15 above.

Slope appears in cell A9, and y-intercept appears in B9.  For the data above, the slope of 1.007742 appears in A9 and the y-intercept of -3.290032E-05 appears in B9.

 

Note 1:  To enter °C, type oC then highlight the zero. Click on Format, Cells, and select Superscript.

 

Note 2:   SCIENTIFIC NOTATION:  Data can be entered at the spreadsheet either in exponential or decimal form.  To change the format of the data (for example from decimal to scientific, click on Format on the Toolbar, then select Cells, Number, Scientific.  To change back to decimal form, select Cells, Number, and General.  To change to scientific notation at the graph, double click at the axis you wish to work on.  Select Format Axis in the dialog box that appeared.  Select the Number folder tab, and click on Scientific.

 

Tips:  It is not always desirable to present the scale in scientific notation at the axes, especially if the exponent changes midway.  For example, a scale that reads 0.008   0.009   0.010   0.011 would appear as

                                                            8E-3    9E-3   1E-2    1.1E-2, which is correct                    

  but one might prefer to see          8E-3    9E-3  10E-3    l l E-3 instead.


 

                                                  Part II:  Plotting Multi-Series of Data on the Same Graph

                                                                        a) To Enter Data

                                                                        b)To Graph Points (without showing curve)

                                                                        c) Curvefitting to a mathematical relationship

                                                                            (Do this only if you need a quantitative relationship.)

                                                                        d) To Print the Spreadsheet and the graph

Note: Curvefitting with Excel is not as automatic as it is with CurveExpert.  If you had to plot only one series of points, curvefitting is best done with CurveExpert.  The advantage to using Excel is when you have more than one series of data.  Excel will allow you to use different symbols and colors for each series, and fit each series with its own curve.  That is something CurveExpert cannot do.

Suppose concentration of Product A vs. time data are as follows:

(0min, 0M), (1min, 0.2M.), (2min, 0.4M), (4min, 0.8M), (5min, 1.0M)

and concentration of Product B vs. time data are as follows:

(0min, 0M), (4 min, 0.16M), (3min, 0.09M), (6min, 0.36M), (8min, 0.64M)

The following section of the tutorial will show you how to plot the two series of data on the same graph and a) enable you to see qualitatively the trend in each series, and b) try to find a mathematical relationship in each series.

 

a) To Enter Data                                                                                         Example for Practice:

18. Enter your first series of points as before (described in Steps 1-3) in columns A and B.  Keep the data for column A in ascending order.

Enter the data for Product A as follows:

Begin at cell A1 and enter number of minutes in column A:

0, 1, 2, 4, 5

and beginning at cell B1, enter the concentration in column B:

0, 0.2, 0.4, 0.8, 1.0

19. Enter your second series of points in columns A and C, taking care that the type of data in column A is the same type as that of the first series of points (in Step 18.)  Again make sure data in column A is in ascending order.

 

(If the two series have the very same data in the A column, the data in the C column can be matched with the numbers in the A column that are already there.)

 

Enter the data for Product B as follows:

Begin at cell A6 and enter number of minutes in column A:

0, 3, 4, 6, 8

and beginning at cell C6, enter the concentration in column C:

0, 0.09, 0.16, 0.36, 0.64

 

 

 

b) To Graph Points (without showing curve):

20.Highlight the data you want graphed.  Click on INSERT, CHART, and select XY (Scatter).  On the right select the top graph (showing points without lines).  Click on Next> at the bottom of the window.

Highlight columns A thru C from 1 to 10, and follow directions on the left.

21.At the Data Range Folder, select Series in Columns.  At the Series Folder, select Series 1, and to its right, type in a label for the first series.  This will identify the points of Series 1 in the Legend.  Now do the same for Series 2.  Click on Next>.

Label Series 1 as “Product A”, and Series 2 as “Product B”.

22. You are now at Chart Wizard – Step 3 of 4.  In the Titles Folder, type in the titles for the graph, x-axis and y-axis.

Type in “Concentration of Products vs. Time” as the title, “Time in min” for the x-axis , and “Concentration of Products in M” for the y-axis.

23. At the Gridlines Folder, you decide whether you want gridlines for both the x- and y-axis.  At the Legend Folder, place a check at Show legend, select Bottom for Placement (This allows more space for the graph).  Click on Next>

If the purpose of the graph is to show a qualitative trend, gridlines are not necessary, and in fact a distraction.  Moving the legend to the bottom of the page is strongly recommended, especially if you have a long title for each series.

24. You are now at Step 4 of 4.  Select either “As new sheet” to have the graph take up the whole page, or “As object in” to have the graph on the same page as the spreadsheet.  Click on Finish.

Make your decision as to where you want the graph to appear.  Select As new sheet for a bigger graph.

25.  If you had chosen to place the graph on a separate page, you can move back and forth between graph and spreadsheet by clicking either Chart or Sheet at the bottom of the window.

 

If you want to make any corrections on the graph, click on Chart in the Toolbar at the top, and choose Chart Options.  Alternatively, place your cursor on the item you want to work on and right-click.  For example, if you want to change the scale in the x-axis, you would point the cursor at the x-axis and right click.  If you want to change the color of the graph area, you would point the cursor at a blank area of the graph area and right click.

 

c) Curvefitting to a mathematical relationship: (Do this only if you need a quantitative relationship.)

26. Click on one of the points on the graph.  This highlights all the points in that series.  Click on Chart, then on Add Trendline.  In the Type Folder, select one of the trends.  At the Options Folder, check Display equation on chart.  If you wish the trendline to be extrapolated to the edge of the graph, you can do so at Forecast by setting the Forward and Backward (see Step 10).  Click on OK.

Click on one of the points for Product A.  In the Type Folder.  Since these points appear to lie on a line, it would be appropriate to select Linear.

 

At the Options Folder, check Display equation on chart.  Note: The displayed equation can be dragged to another spot if necessary.

27. If you wish to change the type and thickness of the trendline, right-click on the trendline (away from any gridlines or points).  Select Format Trendline.  At the Patterns Folder  you can make your adjustments for the trendlines.

Follow directions on the left.

28. Repeat Steps 26 & 27 for the second series of points. Follow directions in Step 8 to change the color of the graph area, etc.

It is strongly recommended that you change the graph area to white.  Default color is gray and will use up a lot of ink.

 

d) To Print the Spreadsheet and the graph:

29. If you had chosen to place the graph on a separate page, the graph can be printed at the graph window.  Click on File and Print.  To print the spreadsheet, return to the spreadsheet (click on Sheet to return to the spreadsheet), click on File, Print Area, and Set Print Area.  Then highlight the data you want printed before printing.

 

If you had chosen to place the graph on the same page as the spreadsheet, you can print the data and the graph on one page.  Click on File, Print Area, and Set Print Area.  Then highlight the area (data and graph) you want printed before printing.