Experiment 00

INTRODUCTION TO USING
THE EXCEL SPREADSHEET

PURPOSE

To gain an introduction to the use of the spreadsheet to perform data manipulations, data analysis, plotting, and curve fitting.
 
 

DISCUSSION

As mentioned in the INTRODUCTION, the Worksheet associated with each experiment that you do is an Excel spreadsheet document. If you have never worked with a spreadsheet before, the material below will provide you with a brief introduction. One of the goals of the Physics Laboratory is to teach you some facility with using the modern computer spreadsheet. This skill is one that will be important to you no matter what your future career aspirations are. Open up the Tutorial Workbook, fill in the header information, and follow the tutorial below on the spreadsheet as you go along.

• The Cell: The basic unit in the spreadsheet is the cell. You can think of the spreadsheet as a huge grid of cells. Each cell is located by its row and its column designation. The columns are labeled with letters, starting with A,B,..., etc. The rows are labeled with numbers, starting with 1,2,..., etc. Thus, the cell that is 5 columns over and 9 rows down is called cell E9. The usual input to a cell is a number that is typed in from the keyboard.

• Calculations: The major utility of a spreadsheet is its ability to perform "live" calculations that involve data that has been entered in certain cells to provide the results in another cell. The term "live" refers to the fact that the result will change according to the appropriate calculation if the input data is altered. For example, suppose we enter the number 2 in cell A10, and the number 5 in cell B10. You can select a specific cell with the mouse. 
Do the following calculations: place the sum of the two numbers in cell C10; the difference in cell D10; the product in cell E10; and the quotient in cell F10.

To accomplish these calculations, go to the indicated cell by selecting it with the mouse, and type in the following in the appropriate cells:


In cell C10  type = A10 + B10

D10   type = A10 - B10

E10    type = A10 * B10

F10   type = A10 / B10
 

 The "equals sign" tells the spreadsheet that you want it to perform the computation that follows it. Try these exercises and check to see if the anticipated results appear in the answer cells. Also notice that what you are typing on the keyboard appears in the contents display line located immediately above the spreadsheet.

To see why the spreadsheet saves a lot of work, change the entry in cell A10 to 3 and see what happens in C10:F10.

• Cut Copy, and Paste: Under the Edit menu, these operations allow you to Cut  an entry in one cell and put it on the Clipboard, which is a temporary storage place, to Copy an entry onto the Clipboard, and then to Paste the Clipboard contents into another cell. The Copy operation copies not only the numerical contents of the original cell, but also any indicated calculations or formulas. This feature allows one to perform repetitive calculations quite easily. For example, suppose we want to reproduce the calculations done above in cells A10:F10 into cells A11:F11 .

To do this, simply select all of the original cells, namely A10:F10, choose Copy from the Edit menu,   then move down to select cells A11:F11, and choose Paste from the Edit menu. 
Pick any two numbers to play with and enter them in cells A11 and B11. Notice how the calculations are performed in cells C11 through F11.

To check on what is going on, select any of these cells, say D11, and look at the contents line just above the spreadsheet. You should see the indicated calculation for subtraction, namely: = A11 - B11.

When the cells were pasted, the cell reference was kept relative to the source cells; that is, the references in D11 are from A11 and B11 , not A10 and B10, as they were in the line that was copied. 
If you do not want this kind of relative referencing to happen, there is a way to specify a specific, fixed cell location in a calculation.

For instance, suppose the spreadsheet numbers need to be multiplied by the same number over and over again. Assume that this number is entered in cell H10. Then, if the number in cells A10 and A11 are to be multiplied by this same number, the reference to cell H10 is made absolute by using dollar signs, as in $H$10. 
Enter 10 in cell H10. If you want to multiply the contents of cells A10 and A11 by 10 and put the result in cells G10 and G11, respectively, you simply select cell G10 and enter: = A10 * $H$10. Then Copy  cell G10 and Paste it into cell G11. The absolute reference is kept during the copy and paste operation.

• Functions: Much more sophisticated operations than simple arithmetic can be performed by using the Function command under the Insert menu. As a simple example, we know that taking the average of a collection of numbers is accomplished by adding them all up and then dividing by the number of entries. Excel has a built-in function that will automatically calculate the average of a group of numbers. Suppose we have the exam grades of 50 students entered in cells A12:A62 on the spreadsheet.  The average can be evaluated in cell B12 by typing the following expression in that cell  =   AVERAGE(A12:A62).  Note that the equals sign tells the spreadsheet that we're doing a calculation; the function itself is represented by "AVERAGE".  Excel contains a huge number of functions that can be used to make complicated calculations simple.  You will encounter a few of these in some of the future Worksheets.