Using Excel for Grades

Dr. Richard L. Bowman, Director
Academic Computing, Bridgewater College, Bridgewater, VA, USA 22812

I. Introduction

Modern spreadsheet programs have sophisticated functionality for analyzing and plotting data. Microsoft Excel is an example of such a program. As an example of this functionality, Excel can generate 14 different types of graphs or charts with an additional 20 customizations of these graphs. This broad diversity in approaches to visualizing data can make plotting appear to be a daunting task, particularly for students and their teachers. Microsoft has attempted to ease this burden by endowing Excel with a "ChartWizard."

The process of finding the mathematical relationship among the data stored in a spreadsheet has also increased in flexibility and functionality and thus in difficulty. For example, if the data can be plotted on an x-y scatter plot, then there are six basic types of trendlines that may be applied to the data with several options.

In this tutorial, you will learn how to perform simple data analysis and graphing and how to proceed from there to operate with more advanced techniques. If you are already acquainted with using Excel, you may wish to simply skim through the next section and begin in earnest with part III.

II. Beginning With Spreadsheets

A spreadsheet is simply a collection of cells of information in columns and rows. In each cell one can place a number, some text or an equation. Information stored in the cells of a spreadsheet can also be displayed in a variety of graphs. Modern spreadsheet programs collect and analyze data on one or more pages (called worksheets) that together form a complete data file (called a workbook).

Start Excel running by clicking on the Start button at the bottom of the screen and then selecting Programs. From there choose "Microsoft Office" and click on Excel. Excel will open up with a blank workbook. This empty workbook will have several blank sheets already available for use.

1. Numerical values are added to a cell by moving to the cell in which you want to place the number, typing the number, and pressing <Enter>. Numbers are automatically right-justified.

2. To enter some text in a cell, move to that cell and type in the text. Press <Enter> to end text entry. Text is automatically left-justified.

3. Equations need to begin with an equals sign and are followed by numbers, cell addresses, operators and/or functions in some order.

One of the most useful features of today's spreadsheet programs is their ability to cut and paste formulas in a "relative" sense. That is, you only have to type in a formula once to do a particular task on a series of cells, e.g. find their average. The cell with the equation can be copied and pasted into a whole column and the appropriate cells will be used relative to the cell into which the formula was copied so that each cell is the average of its own row.

To copy a cell with data or a formula in it, move to the cell and click, hold and drag on the square at the lower right corner of the cell. Dragging it down through all of the desired cells and then releasing the mouse button will copy the equation to all of the cells that were selected.

The procedure for saving a workbook is identical to saving a file in any of the Microsoft application programs. Use the "Save" or "Save As" option from the file menu as is appropriate.

1. To rename any sheet of the workbook, double-click on the appropriate name tab near the bottom of the screen and type in the new name after the old name is selected.

2. To edit/choose headers and footers for the selected sheet, click on "Page Setup" from the "File" menu. Then select the "Headers/Footers" tab. If you want to type in your own text, press the "Custom" button.

3. To see how the layout looks, select as many sheet as you want to preview by holding down the "Ctrl" key while clicking on the name tab of each sheet. Finally select "Print Preview" from the "File" menu and scroll through the sheets. Remember to undo the collecting together of separate worksheets by using the Ctrl and click procedure again.

III. Creating A Sample Workbook

Depending upon the academic discipline, the type of data seen most often by a faculty member will vary. So let's create two types of data sets and see how each of these might be analyzed and visualized.

Daryl has owned a small used car lot for three years. Here is a list of the number of cars he sold for each month during this time.

	2004	2005	2006
January	4	3	4
February	6	6	7
March	10	12	14
April	12	15	18
May	14	18	22
June	10	12	20
July	10	10	12
August	6	7	9
September	6	7	10
October	10	12	14
November	8	7	9
December	4	4	5

Enter this data on the first sheet of your empty workbook. (To efficiently set the style of the first column of data, use the left button of the mouse to click on the first cell in the column and, while holding down the mouse button, drag through the remaining cells of the column. Then from the toolbar select "Align Right." You may need to increase the width of the first column, also. With the column selected, from the Format menu select Columns and then Width. Alternately, move the mouse to the line between column A and B on the bar at the top of the data area and drag the column to a larger width.)

Hint: Instead of typing all of this data into Excel, one can simply highlight and copy the table from this web page and paste it directly into a blank Excel spreadsheet.

Della collected the following data on the position of a small metal ball as it dropped a half meter to the top of the lab table.

Time (s)	Distance (m)
0.00	0.025
0.05	0.039
0.10	0.071
0.15	0.138
0.20	0.219
0.25	0.327

Click on the tab for "Sheet 2" in your Excel workbook and record Della's data there (or copy the whole table here and paste it into the blank Excel sheet.).

IV. Visualizing The Data Quickly

Select the four columns and 13 rows of used car sales data (including the column headings) using your mouse. Then click on the "ChartWizard" button on the toolbar. Follow the "ChartWizard" as it proceeds to generate an appropriate chart.

For this first trial at generating a chart, accept all of the default choices until you reach "Step 3 of 4." In this dialog box, type in a name for the chart and a label for the vertical axis (the y-axis). Then let the "ChartWizard" finish its work and place your chart on the worksheet.

To check out how your chart will look if it were to be printed, select the "Print Preview" button from the toolbar. After viewing the worksheet in this way, Close the Preview window. Your chart probably looks a bit small, and it may even have crossed over into the next page if it were to be printed.

To remedy these problems (or simply to experiment), click once on the graph and hold down the left-mouse button. While doing this, drag the chart from to the right of the columns of data to below the columns. Release the mouse button to actually relocate the chart. With the chart still being selected, note that the box around the chart has little black squares at its corners and on the middle of its sides, click on one of the black squares and drag it to change the size of the chart area. Preview your results again.

Graphs in Excel are fairly easy to modify. For major changes, simply select the chart, delete it, and start over with the "ChartWizard." To fine tune your product, right-click on the part of a chart or graph that is of interest to see a menu of options. In the resulting dialog boxes, choose or change those options that you wish to change.

If a text label or title needs to be edited, click on that item once to select it. Then after a brief pause click on it again to open it for editing (as one would do in a word processor).

As the "Chart Wizard" finishes, the user has the option of placing the chart on a sheet by itself or as an object on the sheet from which it was generated. These same two options can be explored as any time by right-clicking on some "white space" around the chart or graph (for example to the right of the title of the graph). From the resulting menu, select Location.

The used car sales data can also be viewed using 3-D charts. Again select the four columns of data and then press the "ChartWizard" button. For the style of graph, choose "3-D Column." After some adjustments, this is a view of such a graph.

The falling ball data is best viewed as an scatter-plot style of graph. To begin select the two columns of data for the position of a falling ball as a function of time. Then press the "ChartWizard" button on the toolbar. Choose the "XY (Scatter Plot)" graph and use the default format of plotting only dots. Follow the "ChartWizard" in its remainder of questions and options. Experiment as you wish. This type of graph will probably not need a "Legend" which can be removed in step 3 of 4.

Modifications can be carried out as described above for the used car sales chart. One final version of this graph is shown here. The data point graphic was increased in size, the gird lines were changed, the background was set to None, and the tick marks on the axes were changed to face inward.

As a scientist, connecting data points with straight-line segments, as most students would prefer to do creating a saw-tooth plot, makes me cringe. Nature does not usually behave this way, the saw-tooth pattern is simply due to the errors in our measurement techniques. Thus this has not been done here. Other disciplines may actually use such plots, for example, to show the ups and downs of the stock market. However fitting a straight line or any other relationship to this data will have to wait until later in this tutorial.

V. Searching for Meaningful Relationships

Return to the case of the used car lot owner. Visually it appears that his business has been increasing over the past three years. Do the total cars sold per year bear this out?

To sum up the values in a series of cells, use the SUM function in an equation. In the cell just underneath the December values for 2004, type "=" (with no quotes) and then press the function (f_x) button on the toolbar. From there select the "SUM" function by clicking it. Then proceed using the Function Wizard by selecting the "Next" button. Now make certain that the cells you wish to sum are visible. You may have to drag the Function Wizard around by its color bar. Select the cells you want to sum over by pressing the left mouse button in the cell at the beginning and while holding down this button move through the cells you want to sum. Notice how the cell addresses show up in the Function Wizard as B3:F3. Then press the "Finish" button. Finally press return to enter the equation (or you may edit it as necessary).

Note: The functions and the cell addresses can be typed using lowercase letters, and Excel will automatically convert them to uppercase during execution.

Finding the average and standard deviation of the monthly sales will give us a slightly different way to look at the same data. (Note: the standard deviation is most meaningful for a variable whose values follow a normal distribution. However, here it can still give us a rough measure of the spread in the value of the monthly sales.)

Move down to the next empty cell in column A and find the AVERAGE of the monthly sales for 2004. Then move down to the next empty cell and find the STDEV of the monthly sales values. Finally copy these three cells of equations into the cells below the 2005 and 2006 monthly sales data. For the example given, here are the results.

Once a scatter plot has been completed, a trendline (chosen from many different possible relationships) may be added to the graph to model the behavior of the data. To do this, right-click on a data point on the graph and select "Add Trendline." After choosing the relationship form, a number of options may be set by clicking on that tab in the Add Trendline window. Two of the most useful options are "Display equation on chart" and "Display R-squared value on chart." Below is a plot of the dropped ball data showing with a trend line and showing the fitted equation and the R² value.

Note: The default variables for the fitted equation are such that y = f(x). However, here the equation has been edited (by clicking on it) to show the correct variables as x = f(t).

If more information is required, Excel has a number of functions available to extract this information. For example, if one can create models for the data that match a straight line, then the LINEST function may be executed.

Let's continue with our example of a dropped ball. From the fitted equation (x = 4.7571 t² + 0.0204 t + 0.0249), it appears as if the main relationship is the t² term. So we will create a t² column in our spreadsheet. Click on the distance column (B) and from the Insert menu select Column to insert a column to the left of B. In the new B column, more to the second cell and enter the equation, =A2^2 and press enter. Then copy this equation to the remaining cells by dragging this cell through row 7.

Now make an XY scatter plot with the t² and x data and fit a liner line to the data. The trendline, its equation and its R² value all look good (see below).

Now the output of the LINEST function may be explored since the data has been linearized.

Note: LINEST and a number of other Excel functions are array functions, i.e., the results of their computations are a series of values. The worksheet needs to be prepared so as to tell Excel where to place all of the output values. To create room for an array function such as LINEST and TREND, use the mouse to select the range of cells in which the values are to appear. Then enter the equation as usual. Execute the array formula by using <Ctrl-Shift-Enter> NOT just a simple <Enter>.

Continuing with the example, in an appropriate empty cell click and drag to the right two columns and then down five rows. Proceed to enter an equation by typing "= linest(" and then highlighting C2 through C7 for the y-values. Follow this by a comma, and then highlight B2 through B7 for the x-values. Follow this by another comma and type "true" and another comma and a second "true." (All of these are typed without the quotation marks.) Finally execute this function by issuing a <Ctrl-Shift-Enter> combination. Your results in the ten cells should have the values given below in the two center columns.

Slope (m)	4.832432	0.025756757	y-intercept (b)
standard error in m	0.045317	0.001447163	standard error in b
coeff. of determination (r2)	0.999648	0.002468723	standard error for y-estimate
F-statistic	11371.24	4	degrees of freedom
regression sum of squares	0.069303	2.43784E-05	residual sum of square

Note: To the left and the right of the two columns of values above are identifiers for each of the values. Check with a good statistics book or a statistics professor for more details on the meanings of these values.

VI. In Conclusion

Experiment and explore! Excel is a sophisticated program that may seem to be a bit obtuse at times, but with diligence and experience, you will prevail..

Appendix: Resources

In addition to this tutorial, the reader will probably find several other resources helpful.

From the Help menu select "Microsoft Excell Help" or simply click on the associated button on the toolbar. This button has a question mark in a blue circle. One can then choose to either search Microsoft Office Online or Office Help (within Excel itself). To gain some experience using Excel Help, search for LINEST. Note the listings includes a number of other functions, such as LOGEST and TREND. Each of these functions may be worth investigating. Also, try entering a task in the Help search box. For example, enter "find a straight line fit to data." Some of the answers are useless but several may provide really useful additional information.


	©1997, 2007, Richard L. Bowman Last modified: 03-Sep-07; by R. Bowman, rbowman@bridgewater.edu

ANALYZING AND PLOTTING DATA WITH EXCEL