CIS 110 – Introduction to Computer and Information SciencesSpreadsheet Lab using MS ExcelJust as a word processor is the correct software tool for solving the problem of creating text documents, a spreadsheet is often the ideal tool for performing analyses of numerical data in a tabular or row-and-column format.A location in the grid you see below is called a cell. A cell may contain a numeric value, a text label, or a formula (which calculates a result). The cells have a unique address or reference composed of the column then row: for the cell with “Smith” below, the address would be A7. These references are used by formulas to perform calculations.1. Launch Microsoft Excel. Find it by opening the Start menu then the Programs sub-menu. Alternately, you may find the green and white X icon right on the Desktop. Excel will open with a new blank spreadsheet. Immediately save it (File>Save) with a name you specify in a folder you can find again on the hard drive. During the course of this lab be sure to save the file periodically so you will not lose all of your work should Excel lock up.2. Quickly recreate the example skeleton for a spreadsheet gradebook. Just click on a given cell and start typing. For best results use the exact same cells as above!! Pressing <Enter> or moving to another cell with mouse or cursor keys will save what you just typed in the cell. Alignment and bold/italic/underline effects are applied from the toolbar just as in Word (changes apply to current cell or a selection of multiple cells). TYPE YOUR FULL NAME BESIDE “INSTRUCTOR:” INSTEAD OF “JOHNSON”.3. To get the weights to show up as percent, highlight the cells and click on the % toolbar button.4. Go ahead and make up some names and grades to fill in for the two rows below “Jones”. Leave the Average cells blank.5. Obviously we would like to have the spreadsheet calculate the average for us. This is implemented by adding a formula: a. Click on the cell under Average that currently has the value 84.15.b. Enter the formula directly in the cell: = average(B7:E7) average() is a built-in function and B7:E7 uses a colon to show a range of cells is included.c. Click on the cell below and you will see that a different number is displayed in the cell where you just entered the average formula.Click back on the original cell, and you can still see and edit the underlying formula up in the formula bar (fx text box below the toolbar).6. Re-typing this formula for each student (which would need changes for the new row) is inconvenient so instead we use the fill-in features of Excel: make sure the current cell highlight (bold black rectangle) is onthe cell where you entered the average formula. Notice the little black square at the bottom right of the highlight. Carefully place the mouse cursor over this spot and you will see the cursor changes to a +. Hold down the left mouse button and drag a rectangle downward to include the cells in all the rows whichhave student scores in them, and release. continued…Click on the different cells which now have averages and look at the formula bar for each. Note the cell ranges were automatically updated for the current row (B8:E8 instead of B7:E7, etc). This happened because Excel uses relative references by default – if we copy/fill-in a formula it assumes you still want torefer to the same relative positions in the new row (or column).7. Note this average calculation did not use the assigned weights. Click back on the first cell where you entered the average function and change it to a weighted average where (do not type the line below!):weighted avg = (score1 x weight1 + score2 x weight2 + … + scoreN x weightN) / (sum of the weights)If the weights add up to 1 then the division drops out.For this we have to write our own formula in Excel:a. Type “=” (without quotes) then press the left arrow key – note the new colored & dashed highlight. You can “drive” around the spreadsheet to build a formula rather than having to type all the addresses.b. Move the new highlight to the Exam1 score in the same row then press the asterisk * for multiplication. Then “drive” up to the weight for Exam1 and press +. The formula continues to build. Move on to next score for Exam2 and repeat for each score. After capturing the Quiz score & weight, just press <Enter> instead of another plus. Your formula should look something like this (once you click on the cell again):=B7*$B$4+C7*$C$4+E7*$E$4+D7*$D$4 (currently without the $ signs, * is multiplication symbol) If you did not match the original cell placement your particular addresses will be different. It is OK.c. Note the dollar signs above. You need to click up in the formula bar and carefully add them as shown to the cell references for the weights. This turns them into absolute references that will not change/move when you copy the formula.d. Once done, grab the fill handle (little square at corner of cell) and drag down to replace the old averages.8. We would like to also see the class average scores for each test. Here is a nice shortcut:Select a the cells with scores for Exam1 This selection will be downward – do not include “Exam1” label. Stretch the selection down below the last score to include the next empty cell. Find the Σ toolbar button and press the little down arrow beside it. Pick Average from the drop-down menu.9. Next, only select the cell containing the result. Grab the fill-in handle and drag your average formula to theright underneath all the other scores including the column with the individual student’s averages. Go back to the far left and enter the label “Averages”.10. Click on the Exam1 score for Smith. Change the value and press enter. Notice how his overall average, the Exam1 class average, and the overall class average changed. This is the automatic recalculation feature at work. Try several different scores for Smith and some other students. This ability to quickly explore the effects of various changes aids what is known as “what-if” analysis.11. It would be nice to make the failing averages stand out. Select the student averages (rightmost column). Inthe main menu click Format then Conditional Formatting…a. In the dialog box that appears, modify the condition by changing “between” to “less than”b. In the right empty text box, enter 60.c. Click on the format button and change the Color to