USING EXCEL TO RECORD AND CALCULATE GRADESDr. Richard L.
Bowman, Director I. IntroductionA 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 using a variety of graph types. This tutorial will use this format to set up a simple system for recording and calculating students' grades in a course. To complete this tutorial, you will need the sample Excel workbook with grades for a fictitious physics course. To save this sample spreadsheet, "101F_F07.xls", rightclick on the file name and select the option, "Save Target As." Save the file in an appropriate directory under your computer account, e.g., x:\Grades. Note: This file is in Excel 2003 format but should also load fine in Excel 2007. II. Start Excel1. Begin by clicking on the Windows "Start" button, selecting "Programs" and locating the link to Excel. (Usually it will be in a Microsoft Office folder.) Once Excel is running, a blank spreadsheet page will appear ready for entering data, as shown below.
2. Move around the spreadsheet using the arrow keys. As you do notice that the cell identifier at the corner just above the spreadsheet area changes to show which cell you are visiting. 3. Close this instance of Excel before exploring the sample workbook. III. Loading and Exploring the Sample Workbook1. To load the sample workbook (which you had saved from the web page), use My Computer to locate the spreadsheet file, "101F_F07.xl s", that you saved earlier. Then open it by doubleclicking it. 2. Use the cursor keys to move around the first sheet of this workbook. Notice that in the last two columns, the cells contain equations which generate the text or numerical values seen on the spreadsheet. One of these is a mathematicaltype equation and the other column contains a programmingtype equation that actually results in text as an answer. 3. Now move to the other sheets of the workbook by clicking on the appropriate tab at the bottom of the screen. 4. Open a new blank workbook. To do this either press the New button or use the New option on the File menu. 5. Compare the width and height of the cells in the blank workbook with those in this sample workbook. To do this, select a cell and then from the Format menu select Row/Height or Column/Width. Alternately, select a row or column indicator by clicking on the respective number or letter. Then rightclick in a cell in the highlighted group and select either Row Height or Column Width. III. Entering Data and FormulasNote: At any time that you need more pages to your spreadsheet, select the Insert menu and choose "Worksheet." A new page will be placed in front of the current worksheet, and it will have the name, "Sheet#," where # will be one digit greater than the largest numbered sheet. 1. Numerical values are entered into a cell by moving to the cell in which you want to place the number, typing it in, and pressing return. Numbers are automatically rightjustified. 2. To enter some text into a cell, move to that cell and type in the text, pressing return at the end of the text you want entered. Text is automatically leftjustified. Note: If you wish to enter a number as text, simply beginning by typing a single quotation mark(') and then the numerical digits. 3. Equations need to begin with an equals sign. 4.To try data entry, create a new workbook with the New button. In cell A3, enter your name, last name first. Move to B3 and then C3, etc. Enter a series of 5 quiz scores based on a total possible score in each quiz of 5, e.g., 5, 4, 3, 5, and 3. Note: You can enter the data and move to an adjacent cell all by one press of a cursor arrow key! Try it.
Notice that once data is entered in cell B3, the whole data in A3 (your name) will not be visible. To solve this problem, make column A to be larger. Click on the "A" and then do a rightclick in a highlighted cell and select Column Width. Enter a larger number. Some trial and error may be necessary. 5. Now sum these up using the SUM function. Move to the cell just to the right of the last quiz grade, press the "f_{x}" button to the left of the window displaying the contents, if any, of the cell where the cursor is located. This area is referred to as the "formula bar." A Insert Function window will appear. From the list of functions select the "SUM" function by clicking it. (If your particular function is not displayed, select you may choose a different Category such as All.) Proceed by following the directions in the resulting windows. If the cell or range of cells is not the one desired, select the correct cells by making certain that the cells you wish to sum are visible. You may have to drag the Formula Palette window a round to clear the cells needed. 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 Formula Palette as B3:F3. Then press the "OK" button to enter the completed formula in the appropriate cell. A Function Arguments dialog box may pop up in which the cells being summed may be changed. 6. Find the average instead of the sum.. Click on the "f_{x}" button again. The Function Arguments dialog box will pop up, and now to the left of the Formula Bar will be an area showing SUM. In place of SUM click on the arrow and from the dropdown menu choose AVERAGE. Then click OK in the Function Arguments dialog box. The number in the equation cell will change from 20 to 4, if you have used the numbers I used. Note: The functions and the cell addresses can be typed using lowercase letters, and Excel will automatically convert them to uppercase during execution. 7. Some teachers like to assign a certain weight to the different categories that go into making up the final grade. For example, quizzes may be worth 25% of the total, homework might be worth 20%, a paper could be worth 15%, and the exams w ill be worth 40%. Assume the grade for each of these components are listed as so many points out of 100 and are entered in columns B, C, D, and E. Then in column F the following equation should be entered to calculate the grade for the student in row 3. Do not use the "f_{x}" button but type the equation exactly as it is, beginning with the equals sign. =B3*.25+C3*.20+D3*.15+E3*.40 Note: In a case such as this, the sum of all of the percentages should equal 100% and the coefficients used should be entered as decimals. 8. Let's exclude the lowest two scores from the average as done in #6. In place of the AVERAGE equation, enter the following: =(SUM(B3:F3)SMALL(B3:F3,1)SMALL(B3:F3,2))/(COUNT(B3:F3)2) IV. Copying and Pasting FormulasToday's spreadsheet programs, by default, copy 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. For example, find their average of a row of cells, then 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 pasted. 1. As an example, make three more rows of quiz scores similar to the one row you did earlier. Do not include the average for each student. 2. Enter the AVERAGE formula in the cell to the left of the first row. (This will probably be G3, if you are following the given example) Now copy and paste the cell with the averaging formula in it by moving to the cell and clicking, holding and dragging on the square at the lower right corner of the cell. Drag it down through all three cells. (See the two figures below.)
Note: If you are creating and copying a formula to a cell not adjacent to the original cell, then select the cell or group of cells. Press the Copy button on the toolbar. Move to the new location. Press the paste button. The cell will be copied and pasted. If a group of cells are copied, then only the one cell at the top left of the ending location needs to be selected, the Copy function in Excel will figure out how many rows and columns are involved. 3. To import the these average quiz scores into the sheet where the final grades will be calculated, select the whole column of averages and copy them to the clipboard using the Copy button. Move to the correct sheet and the first cell of the column where these averages are to appear. Now from the "Edit" menu select "Paste Special". Press the "Paste Link" button to bring all of the averages to the main page. Note: The beauty of this method is that any changes to the individual quiz grades will automatically be reflected on the main page and thus in the final grade. The sample grade workbook has used this method of copying and pasting links. To examine how this actually works, change one of the homework grades for one of the students and notice how the average is updated automatically on that page and on the final grade page. V. Changing the Width and Height of Cells and the Position and Display of Cell Contents1. Remember that to change the width or height of cells, one can select a whole column or row and from there rightclick and select the appropriate option.. Or select a cell and then from the "Format" menu, select Column and then Width or Row and Height. 2. To change the font used in cells, select the cells to be involved and then press the arrow by the font size on the toolbar (by default showing Arial and 10) and select a new font type and/or size. 3. To change the position of data in a cell, use the "Center", "Left" and "Right" justification buttons on the toolbar just as is done in a word processing program such as Word.. 4. To roundoff numbers displayed in cells, select the cell(s) and then from the "Format" menu select "Cell" and then the "Number" tab and finally the "Number" category. Change the number of digits after the decimal to the desired value and press "OK". Note: The number is not actually rounded off, only the display has been rounded. Internally, Excel still retains the value of the number to the precision that it can (usually up to 15 digits). VI. Creating Attendance SheetsSometimes it is useful to have a list of student names beside a grid of boxes in which to record attendance or grades before copying them into the spreadsheet. Excel carries out this task rather easily. Note: A grading sheet is included in the sample workbook. 1. On a blank worksheet in Excel, paste or type a list of the students in the class. Adjust column widths and row heights to allow for easy entry of handwritten data. 2. Click in the upper leftmost cell, of the region you wish to make into a grid, and drag to the bottom rightmost cell of the grid. 3. Outline the cells of the grid using the border button on the toolbar. If the type of border shown is not what you desire, press the small arrow just to the left of the border button. A list of pictures demonstrating the available bordering options will appear. Select the most appropriate one, or for even more control, choose the Format/Cells/Border menu item. 4. Finally, preview the page you have generated, as described below, and print it out. VII. Previewing and Printing the Workbook1. To rename any sheet of the workbook, just doubleclick on the appropriate name tab near the bottom of the screen. Once the name is highlighted, simple type a new name. 2. To edit or 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. Caution: Before moving on to do more editing, make sure to go back and click a second time on all of the selected tabs to unselect them. That way changes will be made only to one sheet. 4. To print a sheet, press the Print Button. 5. To print more than one sheet or the whole workbook, select all sheets of interest by the procedure outlined in #3, or select Print from the File menu and choose the pages to be printed. Appendix: ResourcesIn addition to this tutorial, the reader will probably find several other resources helpful. 1. Help in Excel 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)e. 2. Web Authoring Tutorials at Bridgewater
College's Academic Computing Web Site 3. Other Tutorials


Return to the
AcadComp Homepage Go to Academic Computing at Bridgewater College 
©1997, 2007, Richard L. Bowman Last modified: 30Aug07; by R. Bowman, rbowman@bridgewater.edu 