UPDATING AN EXISTING SPREADSHEET

From Compsci1

UPDATING AN EXISTING SPREADSHEET

The Gradebook document would benefit from some cosmetic changes, such as the following.

1. . There should be double lines to set off different parts of the spreadsheet. It is often useful to include double lines between headers and the data, and before summary totals.
2. . It would be useful to include more formulas to increase the information content of the spreadsheet. For example, an average for each of the sets of grades so that you and your students can see where they stand in relation to the rest of the class on any particular assignment or test. A highest score and lowest score for each set of grades would also be useful.
3. . It would be a good idea to lock cells containing data that you consider especially important to prevent you or someone else accidentally destroying the cell contents. It takes time to put together spreadsheet templates and other documents. Some cells will contain functions that were tricky to figure out. Locking them will make it difficult to lose your work.
4. . Finally, Excel can do some of the thinking for you if you include a LOOKUP Table to figure out the grades for your students based on their percentage score at the end of a reporting period.

Let's deal with these problems one at a time. In this section you'll learn how to handle the first three improvements. Using the LOOKUP function will be dealt with later in this lesson.

Dividing up the spreadsheet to make it easier to read

After you have fixed change #1, the Gradebook document will look similar to Fig. 5.3. Breaks between sections of a spreadsheet help the eye locate important data when scanning the page, whether on screen or on paper. In Excel, the easiest way to do this is to insert an extra row or column at the point where you want to create a division between different parts of the spreadsheet, and reduce the width of the row or column so it appears as a double line. Follow these steps to update the spreadsheet along the lines of Fig. 5.3.

• On the left edge of the screen, click on Row 13 to select the entire empty row

Now you want to reduce the row height so it looks like a double line dividing Rows 12 and 14.

• From the Format menu select Row/Height..., then in the Row/Height dialog box type the number 4.5 to replace the default height, and click OK

You need to create a similar dividing line after Row 23, which holds the data for the last student in the roster. This is because you are shortly going to include new formulas in Rows 25 through 27.

• Reduce the height of Row 24 in the same way as you did Row 13, then Save the changes you have made

There are a couple of other ways to change the height of a row or the width of a column. One way is to position the mouse on the line between the row or column headers at the point where you want to change the height or width. The mouse changes to a cross hair with arrows. Then hold down the mouse button and drag to change the height or width of a row or column respectively. A small box appears when you do this (try it for yourself) which tells you the height you are selecting. You already learned in Lesson 4 how to do this for column width.

Another way is to right click in a cell in the row or column whose height or width you want to change. In the pop up menu you would select to adjust row height or column width, and proceed from there. Very simple.

Adding formulas to the spreadsheet 

Excel comes with many built-in functions for the spreadsheet. There are ten categories of functions, including Math & Trig functions, Statistical functions, Logical functions, and Financial functions, among others. These functions allow the spreadsheet to be tailored to meet the needs of myriad numerical data applications from Accounting to Zoological research.

• From the Insert menu select Function... or click on the Insert Function (fx) icon in the data entry bar (Fig. 5.4) to take a look at the functions you can use
• Click to pull down the menu of categories, then select the All option in the menu
• Scan through the list of over 200 built-in functions in the Select a Function scroll box

Depending on your math, computing, statistics, or accounting background, you might recognize many of them.

• Click on the Cancel button to close the Insert Function dialog box

Experience is the best way to learn how they work. In Lesson 4 you already learned to use the Sum function, and also you created your own formula to calculate the Percentage for each student.

Calculating an average for each of the grade columns Next, you are going to add three new functions to the Gradebook: the Average, the Max, and the Min functions. You want the value representing the average of the scores in a column to appear in a cell in Row 25 at the bottom of each of the columns where you want to calculate an average.

Fig. 5.6 illustrates how your spreadsheet will look after you have inserted the formulas in the appropriate cells.

Fig. 5.6 Average scores for each column

Notice in Fig. 5.6 that the last cell in the column of first names has the row label Average. You need to do the same in your Gradebook.

• Select cell B25, type the label Average, and press Tab twice to move to cell D25

A word about automatic calculation When Automatic Calculation is switched on, Excel immediately carries out any calculations that are necessary when you make any changes to the data in the spreadsheet. So you don't have to remember to do this yourself. Whenever your spreadsheet is small (like the Gradebook document) you will hardly be aware that Excel is working for you like this. If your spreadsheet were large, however, you might want to wait till you tell Excel to recalculate all the formulas, because of the time it can take to carry out this task.

• From the Tools menu select Options... then, in the Options dialog box, click on the Calculation tab (Fig. 5.7)
• Check to see that the radio button next to Automatic is selected (indicating that Automatic Calculation is already selected)
• If not, click on the radio button next to Automatic and click OK

Back to the task at hand

The average is computed by adding (SUMming) the set of scores for an assignment or test, and then dividing by the number of scores in the set. You could put together this function yourself, of course. If you know what it would be for the first column of scores, write it in the box below.

It would look something like this: =SUM(D14:D23)/10. But since Excel has a built in Average function, you may as well use it. Here is the complete set of steps to include the Average function in your spreadsheet.

• Make sure cell D25 is still selected (the cell under the first column of scores), then from the Insert menu select Function...
• Select the Statistical category, then select the AVERAGE function from the list of Statistical functions and click OK

Excel pastes the function into the data entry bar towards the top of the spreadsheet window and then selects the cells in the column above the cell in which you want to put the average function. It also presents a new dialog box (Fig. 5.8).

Fig. 5.8 The function is first pasted into the Entry bar

This Function Arguments dialog box selects the set of cells above the cell containing the new formula, explains what the Average function does, and asks you to confirm the formula by clicking the OK button. At this stage, you can change any of the cell addresses in the data entry box. Notice that Excel has included cell D24 (an empty cell) as part of the set of cells to be averaged. This will not affect the result because Excel assumes that an empty cell is not to be included in the calculation of the function’s result.

The formula in the Data Entry bar towards the top of the spreadsheet window should read =AVERAGE(D14:D24). Is this the exact wording of the formula on your screen?

If it is, click OK; if it is not, correct it, then click on OK

Now look at cell D25. It should contain the average for the scores in Column D. There are three problems that can occur:

1. If you see a series of pound signs (#######...) this indicates that you need to widen the column a little so there is enough space for the average score. Either use the Format Column/Width... option, or drag with the mouse to make the column wider. If you don't see the pound signs (#######...), all well and good.
2. If a Bad Formula prompt pops up on the screen, check the formula in the entry bar again, compare it to the Average formula above, and make any corrections.
3. If the values being averaged have no decimal places, then the default number of decimal places will be zero (0). But it would be useful to show at least one decimal place. Here are the steps to change the precision of a decimal number.

• From the Format menu select Cells..., then in the Format Cells dialog box (Fig. 5.9) select the Number category, and set the number of decimal places (also called precision) to 1 and click on OK

Fig. 5.9 Changing the precision of a decimal in the Format cells dialog box

Assuming all is well, your next task will be to copy this formula into the adjacent cells to the right under the other columns of scores (cells E25 through H25).

• Cell D25 should still be selected
• Use the mouse to point at cell D25, then hold down the mouse button and drag across to Column H (so cells D25, E25, F25, G25, and H25 will all be selected) then from the Edit menu select Fill/Right...

That's all there is to it. Adjust the column widths if you see pound signs (#) instead of averages. It's all good practice.

• Time to save all that hard work (Ctrl-s)

Displaying the Highest and Lowest scores for each column of scores

Now that you know how to use the Function... option from the Insert menu, and in particular since you just used it to put the Average function in your spreadsheet, you can complete the exercise that follows. You might find it best to read over the directions before carrying them out, since they're a bit tricky at first.

• Put a heading in cell B26 (Max score) and in cell B27 (Min score)
• Put the Max function in cell D26 (listed as Max( ) in the Statistical category in the Insert Function dialog box)—this function is used to find the highest score for each set of scores
• Drag down with the mouse to select cells D14 through D23, the cells you want included as arguments in the Max function (D14:D23), and click OK
• In cell D27 put the Min function (listed as Min( ) in the Statistical category in the Insert Function dialog box) and drag with the mouse to select cells D14 thru D23 once again, then click OK
• Just as you did when you worked on the Average function, copy the new Max and Min functions across to the corresponding cells under columns E thru H using the Fill/Right... option in the Edit menu

When you're done, your spreadsheet should resemble that illustrated in Fig. 5.10 on the next page.

Locking (protecting) important cells

Excel allows you to protect the contents of a cell by locking or protecting it. This means that neither you nor anyone else will be able to change the contents unless you remove the protection. This feature is useful to prevent accidental loss of data, and will also help prevent others from interfering with the data you have collected.

Since all the data in a Gradebook are important, it would be a good idea to protect everything. The process to do this is the same as if you were protecting a single cell, or a few cells, except that you select every cell.

• Press Ctrl-a to Select All the cells in the spreadsheet
• From the Tools menu select Protection/Protect sheet..., then click on OK in the dialog box that pops up on the screen

Fig. 5.10 The Gradebook document after adding the functions (your data may be different)

Notice that you have the option to include a password to remove protection (unlock the locked cells). Now all the cells in the Gradebook are unable to be changed unless you select Unlock Cells from the Option menu. If you wanted to lock just certain cells you would first select the cell or cells and turn on the Protection tool for just that cell or those cells.

If you try to change a locked cell, you will get a warning dialog box (Fig. 5.11) telling you that you can’t change anything unless you first remove the protection.

Fig. 5.11 Protection warning dialog box

For this reason, later in this tutorial, when you get this warning message, you will need to remove the protection on cells in order to make updates.

Time to save the changes you have made to the Gradebook (ctrl-s). This should be becoming instinctive for you by now.

Dividing the spreadsheet into panes

Often a spreadsheet, or any type of Excel document for that matter, will be too long or too wide to view all at once on the screen. Excel has a useful feature called panes You can split any window horizontally or vertically into sections. This will allow you, while working or scrolling in one section, to keep the other section(s) fixed in place.

• To see how this works, from the Window menu select Split...

Excel immediately divides up the worksheet into four sections, using a vertical and a horizontal split bar (Fig. 5.12).

Fig. 5.12 Excel’s vertical and horizontal split bars

You can change the position of the split bars by positioning the mouse arrow on a split bar and drag across or down the screen.

• Try this for practice— position the mouse arrow on either the vertical or horizontal split bar (you'll get a cross-hair cursor) and hold down the button while you drag across or down the screen

Notice how easy it is to compare different parts of a spreadsheet in this way. The pane or splitting screen feature is available in all Excel applications, but you probably will find that you use it most frequently while working with spreadsheets, especially large ones. Removing panes

• From the Window menu select Remove Split

Your spreadsheet should be restored to its original format.