CogSci 3 -- Excel
Due Saturday, November 7th; 11:59 PM 2009

Goals

This assignment will introduce you to some of the common functions of Microsoft Excel, using the Office Macintosh 2004 version.

1. Learn some spreadsheet terms and concepts (e.g., "cell", "reference", etc.)
2. You'll make up a simple spreadsheet with some sample student quiz scores.
3. Import some data values and turn them into Excel columns
4. Sort and join data
5. Use the "Find What/Replace With" dialog
6. Use some wild cards in "Find What/Replace With" dialogs
7. Create some simple formulas to summarize the data
8. Use the logical if function
9. Create a simple chart
10. Create a pivot table to summarize your data
11. Use some text functions

We assume that you will be doing the assignment in the class Macintosh lab, or other campus Macintosh lab. This assignment is "turned in" by saving your work to your hw5 folder on your Macintosh File server.

Important Note:

• We've added more student data to the assignment. All of the figures should be taken as examples rather than an exact copy of what your spreadsheet should look like.

Starting Excel

Locate Excel in the Applications Folder (i.e., Applications->Microsoft Office 2004-> Microsoft Excel) and start it.

Excel terms

• Excel documents are called worksheets,
• Multiple worksheets are contained in a workbook.
• The worksheet is a grid of rows identified by their number and columns identified by letters (A-Z), or pairs of letters (AA-AZ, BA-BZ, etc).
• The intersection of a row and column creates a cell.
• One cell is the active cell, which is the one you have clicked on and has a heavy border, though you may not see it on the screen if you scrolled.
• Cells are named for the column and row they are in, column name first; e.g., A3, E43, BB10, etc. That name is called the cell reference.
• Immediately above the row containing the column letter names is the formula bar.
• The left portion of the formula bar (above column A) is the name box, which has the cell reference of the active cell. (It can also be used to select a new active cell by typing a new cell reference into it.)

Getting Help While Using Excel

Start up Firefox

Importing Data

When you copy and paste between applications, you usually want to "get back" exactly what you "put in". Excel and spreadsheets are a little different in this respect. You want to "put in" text and "get back" columns (and rows) of data. That is, you want Excel to recognize the data as being organized as a table in columns and rows.

When you paste data into a worksheet, Excel uses the TAB character (or csv format, a comma) as the column delimiter. A delimiter is a specially recognized character that is used to separate things, columns in this case. That is, in the text you paste, a TAB tells Excel when to start a new column; somewhat like a <td> in XHTML. The RETURN (end of line) character at the end of each line tells Excel to start a new row; sort of like a </tr> (or perhaps the <tr> that starts the next row).

The Data: Student Scores

A0011   15   17   15   15
A0021   10   13   16   19
A0024   13   19   14   13
A0036   20   19   20   20
A0032   19   16   18   18
A0002   15   17   13   15
A0041   20   19   19   16
A0031   17   17   18   19
A0012   20   20   19   20
A0033   11   19   19   18
A0020   19   12   18   14
A0022   18   19   17   17
A0005   12   17   16   14
A0008    8   15   10   16
A0030   13   18   17   14
A0027   16   20   19   19
A0029   18   19   20   19
A0018   17   18   12   20
A0037   18   19   16   20
A0023   19   13   14   15
A0019   18   17   17   20
A0009   16   18   14   12
A0007   13   15        18
A0035   16   20   19   19
A0038   16   15   19   19
A0034   19   16   19   19
A0003   17   18   18   19
A0010   14   17        15
A0013   15    6   17    8
A0006   19   20   19   19
A0004   15   18   20   17
A0028   19    0   20   20
A0026   13   19   18   19
A0025   19   13   17   18
A0016   10   19   13   19
A0015   15   13   18   14
A0017   11   13   15   11
A0014   17   17   12   12

A0001   13   15   14   11

Copy and Paste the Scores

It will look a little funny. That's because all the data has been pasted into the A column, not into 5 columns (A through E) as you would have hoped.

The problem is that when you copy text from Firefox (or even from a SSH window), there may be no TAB characters. Firefox (and SSH) convert the TABs to spaces when the text is printed on the screen. Thus, when you copy, there may be no TAB delimiters to tell Excel when to start the next column.

For this type of "paste" operation, Excel has a nifty feature called "Text to Columns" which is located in the Data menu. The "Text to Columns" wizard dialog takes you through a series of steps that allow you to describe (to Excel) the format of the data that you are importing.

Text to Columns

1. Now select "Text to Columns..." in the Data Menu
2. A dialog will open showing you the first line of the data, about like this:

A0013    15     6    17     8

3. The dialog lets you choose either delimited or fixed width to specify what text should go into which columns.
1. "Delimited", of course, means there is a special character, like a comma or TAB, which specifies where to end one column and to start the next. There might be several of the same delimiter per row if you wanted to have more than just 2 columns.

2. Fixed width, on the other hand, means that the same column in every row has the same number of characters in it. If a particular cell in that column has less than that number of characters, spaces (blank characters) are used as padding to make up the difference. Thus, the size for each column is a fixed number of characters. Different columns can have different widths, so long as every cell in a given column is the same width (including the padding) as every other cell.

4. The table of scores was created and displayed in a fixed width font, so every thing lines up nicely. We'll have Excel treat it as a fixed width object. Select 'fixed width' and click Next.
5. The next dialog of the wizard (called Step 2 of 3) lets you specify the spacings between the columns. Click between each pair of columns of data to create a column break. You can change the spacing by dragging the lines between the columns. Click Next.
6. Step 3 of 3 in the dialog is about setting the data types. You can just click finish.
7. Your data should be transformed into a 5 column table.

Save As

Before you go any further, save a copy of your work so far. Open the File->SaveAs dialog. Change the name of your document to your account name (e.g., cg3fxx.xls). Save it in your hw5 folder on the file server-- i.e., in hw5 located in the cg3fxx folder in the "Class Resources". Create the hw5 folder, if one doesn't already exist.

Note: if you have trouble accessing the class server via the "Class Resources" folder, you should review the "turn in" document and use one of the alternative methods to turn your assignment in.

Note that in Office 2004, Excel has an option (in the SaveAs dialog) to add the .xls filename extension to your document name, to be compatible with the Windows operating system. You should make sure that box is checked.

Insert a New Row

1. Select all of row 1 by clicking on the number 1 on the left edge of the worksheet..
2. The whole row should be highlighted.
3. Select Cells (or Rows)... from the Insert menu.
4. The IDs and quiz scores should move down one row, and a new empty row should appear as row 1.

Column Headings
Typing in Data

1. In cell A1, put Student ID
2. In cell B1, put Quiz 1
3. In cell C1, put Quiz 2
4. In cell D1, put Quiz 3
5. In cell E1, put Quiz 4

The Student Names

Bart Simpson A0008
Lisa Simpson A0016
Montgomery Burns A0021
Cecil Terwilliger A0032
Selma Bouvier A0010
Beatrice Simmons A0026
Ned Flanders A0013
Nelsen Hunts A0031
Maude Flanders A0002
Edna Krabappel A0009
Jebidia Springfeld A0004
Otto Manns A0003
Cornelius Talmadge A0035
Clancy Wiggum A0005
Patty Bovier A0007
Kent Brocmann A0019
Herbert Powel A0030
Hans Molemen A0006
Martin Princess A0038
Janey Hagstreem A0028
Seymour Skinner A0018
Ralph Wiggum A0034
Todd Flanders A0025
Apu Nahasapeemapetilon A0015
Troy McClure A0020
Dewey Largoon A0023
Nick Rivera A0011
Krusty Clown A0001
Joseph Quimby A0022
Elizabeth Hoover A0029
Allison Taylor A0037
Barney Gumbel A0014
Waylend Smithers A0017
Silvia Winfield A0024
Rod Flanders A0036
Manjula Nahasapeemapetilon A0027
Langdon Alger A0041
Samantha Stanky A0033
Homer Simpson A0012

Create New Columns

Copy and Paste the Names and IDs

Again, all the data has been put into one column, the A column. Observe how the information you pasted seems to extend into column B! If you look closely, however, you will see that there is no column divider line in those cells between column A and B now. (If you scroll below the table, the vertical border line between cell A and B will be visible again.) This is Excel trying to show as much information in a cell as possible by extending over blank cells. It is also the reason the information stops abruptly in column C where the cells are not empty.

Copy and Paste Again

Text to Columns Again, But...

1. Select the cells to be worked on (they are probably still selected due to your paste operation).
2. Data->"Text to Columns"
3. Then select "fixed width", and then click Next.
4. Click Next again to see how to separate your data into columns. But there is no obvious way to drag the lines to separate all the columns cleanly.
5. Don't click Finish.
6. So the "fixed width" option is not going to work. But what about delimited? Click the "Back" button in the "Text to Columns" Wizard until you can select "Delimited" instead of "Fixed Width". Then click Next.
7. There are "spaces" (hereafter as just spaces or SPACES without the quotes) which seem to separate the words. So select Space as a delimiter.
8. Using space as a delimiter gives you 3 columns: first-name, last-name, and then ID. The first and last names will be separated! Darn.
9. So, click Cancel in the "Text to Columns" dialog.

If only ...

...
A0002 Nick Rivera
A0015 Apu Nahasapeepetilon
A0004 Enda Krabappel
...

But with your data, which has the names first and then the IDs, you will need different tool (wizard) to split it up into columns like you want.

Lets look carefully at some of the lines of data.

Bart Simpson A0008
Homer Simpson A0016
Montgomery Burns A0021

Using Replace

The typical use of the "Find What/Replace With" dialog replaces a fixed value or text string in your document another fixed value, like replacing "Simpson" with something like "Dolts". Let's try it as an exercise to see how it works.

1. Your column A with the names and IDs should be still selected, if not reselect it
2. Select Edit->Replace; you may have to select the down pointing arrows at the bottom of the Edit menu to get the Replace option to appear.
   1. In the "Find What:" part of the dialog, type in "simpson" (note: lowercase 's' at the beginning!). This is called the "target text" that the Replace command will look for.
   2. In the "Replace With:" box, type: "Dolt" but with out the '"' (quotes). This is the "new or substitution text" that will replace the target text.
3. Click the "Replace All" button. The "Replace All" function will replace every instance of the target text in your selection in a single operation. On the other hand, the "Replace" button will do one substitution at a time under your control.

Note that you used all lower case in the search target, but found targets with capital letters. Excel must be using a case insensitive search.

Close the Replace dialog and select Edit->Undo (Replacement) to restore the Simpson names.

Let's try another exercise.

1. Your column A with the names and IDs should be still selected, if not, select it by dragging through it or use good old Shift+Click
2. Select Edit->Replace
   1. In the "Find What:" part of the dialog, type in "simpson" again as the target text.
   2. In the "Replace With:" box, type nothing. That is, make sure the "Replace With:" text box is empty.
3. Click the "Replace All" button.

Close the Replace dialog and select Edit->Undo (Replacement) to restore the Simpson names.

Wild Cards

For example: suppose you had a list of words: art, ant, bat, cat, rat, brat, saint, smart, paint, plant, and tart. The pattern "*art" would match: art, smart, and tart. [Note: the fact that "*art" matches "art" is an example of where the "*" matched zero characters.] The pattern "s*" would match saint, and smart. And "*t" would match all the words, as does "t*"; why? Remember, the "*" can match 0 characters.

You should be able to use the "*" wild card in a Replace dialog to delete the student names, leaving only the ID.

1. Make sure that IDs and Names are in the A column are still selected. If not, Shift+click is a very handy way to select a range (group of adjacent) of cells in Excel. Click in the first cell you want, then (perhaps scroll) and Shift+click in the last cell you want; all the cells in between will be selected as well.
2. Edit->Replace
3. In the "Find What:" box, type: *A0
   Again, the "*" is a wild card which matches any number of characters (including no characters). We are telling Excel to search for cells containing "A0". and those cells will match all the text from the left of the line, up to and including the 'A0' (though we discovered earlier that the search is case insensitive). Note that '0' refers to zero, not 'O'. The Excel "*" wild card will match as many blocks of text possible. So if the text has 2 (or more) "A0"s in it, the "*A0" would match the all the text from the left to the rightmost "A0".
4. Make sure the "Replace With:" box is empty.
5. Click Replace All. All the text found (i.e., the student names) are replaced with nothing; i.e., deleting them.
6. Only the IDs (A0008) should remain.

Select Edit->Undo (Replacement). We'll have to modify your Replace so the "A" is not removed. Now:

1. Use "*A0" as the target text, and "A0" as the substitution text. This is probably the best, as it explicitly defines the target should only match the text up to and including the "A0" part of the ID.

A Wildcard to Delete the IDs, Leaving the Names

Now, to eliminate the IDs, an obvious thing to do is to use the Replace dialog and enter: "A*" in the "Find What:" box.

Use Replace again to remove the student IDs. as follows:

1. Edit->Replace
2. In the "Find What:" box, type: A*
3. Make sure the Replace With: box is empty.
4. Click Replace All
5. The IDs (A0008) should disappear and only the names should remain.

Select Edit->Undo (Replacement) to put the IDs (and mangled names) back.

? versus *

For instance, "A???0" would match all the "words" starting with A, having exactly 3 characters followed by a '0' (zero); this pattern would match the ID "numbers", but probably none of the names, because there are no zero digits in the names.

By contrast, "A*0" will match the leftmost "A" in the names (if there are any), the rest of the name, and then the "A" in the ID and then up to the rightmost '0' in the IDs. For example, in the line "Lisa Simpson A0016", "A*0" would match the "a" in Lisa through the rightmost 0 in A0016. Actually, even our "A???0" has a problem with "Kent Brockman A0019", where it matches the "an" in Brockman: "an A0". In cases where there 2 or more possible fixed length patterns that match, the leftmost is the one used ("an A0", rather than the "A0019" we wanted).

On the other hand, the pattern "A0???" should match only the the IDs, since they (and no names) start with "A0" and have exactly 5 characters in them!

Now Delete the IDs

1. Make sure your column B names and IDs are still selected.
2. Open the Replace dialog (Edit->Replace or ^H)
3. Use " A0???" as the target text. Note we added a space to the left of the "A". Why? Your target text should have a space there too, and presumably you want to remove that space as well.
4. The Replace With box should be empty, of course.

Note: we could also have used " A0*" (Space A Zero Star) instead of " A0???" as the target text. This will work too. Why?

Sort the IDs and Names

Now that you have both the IDs and names, and IDs and scores, you can continue with your strategy of having Excel join them. First, you will sort the first 2 columns which contain the IDs and names.

1. Select the both IDs and names in columns A and B using shift+click or by dragging through the cells. Important: do not select the empty cells in row 1! and make sure that only the first 2 columns with IDs and names are selected.
2. Select Sort in the Data Menu.
3. In the Sort dialog, set the "Sort by" to the column with the IDs (Column A), and check that "No header row" under "My list has" near the bottom of the dialog is checked.
4. Click OK
5. The IDs and names should move so the IDs start with A0001. The first row should be A0001 Krusty Clown.
6. If you make a mistake, you may be able to undo it (CMD+Z) using the Edit Menu, and then try again.

Sort the IDs and Quiz Scores

1. Select the IDs in column C and all the data through column G by dragging through it (or better, using shift+click). Important: make sure that you select all of the quiz scores as well as all of the IDs in column C. Do not select the IDs or names in the first 2 columns, and do not select the first row with the column headings (Student ID, Quiz 1, etc...), only the cells C2-G2 and then down until the end of the data.
2. Select Sort in the Data Menu.
3. In the Sort dialog, make sure the "Sort By" is the column with the IDs (Column C).
4. Click OK
5. The IDs and scores should move so the IDs start with A0001.

Verify the Sort Results

At this point, your worksheet should look something like (with more students, of course):

Quiz Totals

First, in column B1, type: Student Names. Then in column H1, type: Totals.

Now create a formula in H2 that adds the quiz scores for the first student. A formula is a calculation that uses numbers, cell references, and functions to derive a result. For instance, simply add (sum) the quiz scores in the cells D2 through G2 like: =D2+E2+F2+G2. (Formulas are always started with an "=".)

Or we could add those same cells using the Excel sum() function. (Functions are predetermined formulas that do their calculations using the values you specify called arguments.) The advantage of using the SUM() function is that we can specify which cells to add up as a range, which is easier to type than enumerating a large list of cells.

To enter the formula with the SUM() function:

1. Click in cell H2
2. Formulas always start with a "=", so
   Type: =
3. Type: sum(
4. Now you need to enter either a comma separated list like D2,E2,F2,G2 or the range of cells: D2:G2. Note there is a colon (:) between D2 and G2. There are several ways to enter a range of cells into a formula:
1. Click in the first cell in the range (D2), and then shift+click in the end cell of the range (G2),
2. Click in the first cell in the range (D2) and drag to the end cell of the range (G2),
3. Click in the first cell in the range (D2), and then hold down the shift key and use the right arrow key repeatedly to select each cell in the range (G2),
4. Or simply type in: D2:G2
5. Now type the closing parenthesis: )
6. Type: RETURN, or click the check box in the formula menu to complete the formula.
7. Notice how the cell H2 no longer shows the formula, only the results (number) of the formula. Once you have completed a formula, the worksheet cell shows you the result, not the formula. If you need to change/edit the formula, click in the cell and then edit using the formula bar. You will also notice that Excel has capitalized SUM.

Fill Down

Copy the formula in H2 down through the column as follows:

1. Select H2 down through as many cells as there are in the adjacent G column.
2. In the Edit Menu, select Fill-> Down. All the cells that you selected should now have a number in them.

Now click in H5 and look at the formula bar; notice how the row references which were D2:G2 (row 2) have all been changed to D5:G5 (row 5) by Excel. The Fill Down operation has copied the formula and kept the cell references in the formula relative to the cell which contains the formula. I.e., the formula in each of the H cells sums the cells on its own row, not the original H2 row; e.g., cell H13 is the sum of D13:G13.

Relative and Absolute References

Excel normally uses relative references in formulas. That is, the formula is the sum of the 4 cells to the left, or, perhaps in another case, the cell above and the cell to the left. If you copy and paste the formula, or use Fill Down or Fill Right, the formula uses new cell references that are relative to the new cell that the formula is now in; i.e., the sum of the 4 cells to the left of that new cell.

The use of relative cell references in formulas is what let's you copy and paste formulas, and allows Fill Down and Fill Right work correctly. You can also specify absolute cell references when you wish a formula to refer to a single specific cell any where on the spreadsheet. Absolute references use a "$" to indicate their non-relative nature. For example: $B$3, or in a range: $D$2:$G:$22.

Find Average Scores

Suppose we wanted the average for the cells D2:D22 (you have more data than this, but for this example, let's assume). You now know that you can express the sum of the cells in D2:D22 as sum(D2:D22); then you just need to figure out what N is (how many cells there are in the range D2:D22).

The rows() Function

1. Locate the last cell used in the D (Quiz 1) column
2. Click in the cell in the D column that is 2 cells below the last quiz score in the D column; i.e., so there is a blank cell between the last score and the cell you clicked in.
3. Type in the formula: =rows(D2:DX)
   where X is the row number of the last quiz score in column D.
4. When you complete the formula (with Enter or Tab), you should see the value X-2+1 in that cell.
5. Click in the cell with the formula again, then use Edit->Fill->Right over to column H to copy/replicate your formula through column H, for the other 3 quizzes. There is actually another, perhaps handier way to Fill.
   1. Click in the original cell with the formula again
   2. Notice the small square in the bottom right corner of the cell border.
   3. When you move the cursor over that square, the shape changes to a '+', you can then drag to the right (or down) to do the Fill function.

The count() Function

1. Click in the cell in the D column below the "rows" value/formula.
2. Type: =count(D2:DX) (Again, X is the row number of the last quiz score in column D.
3. When you complete the formula (with Enter or Tab), you should also see the value X-2+1 in that cell.
4. Then use Edit->Fill->Right over to column H to copy, replicate your "count" formula through column H.

Missing Data

The problem is empty or blank cells. Blank cells have a special meaning in Excel. Some Excel functions (like count(), sum(), and average()) ignore blank cells; in other uses, a blank cell may cause an error. You also need to be careful sorting cells; blank cells are again treated differently (and may not sort like a zero value).

That's why you have a different value for the rows() and count() functions for Quiz 3; rows() really counted the number of rows, but count() counted only the non-blank cells.

Averages

The select the "count()" formula in column D and change it to also compute the average. Then use Fill Right to copy it through to column H. The averages in column F (Quiz 3) will be different, of course, because the 2 formulas are dividing by a different number.

Excel also has an average() function built into it. Let's use the built in function too, as a comparison.

1. Click in the cell in the D column that's below the "count()" average.
2. You can just type in the formula: =average(D2:DX). Or you can Insert->Function.
3. If you don't see Average as one of the choices, try typing "average" in the "Search for" box at the top of the dialog; that will produce a list of functions that compute an average. Scroll until you find the Average() function, then double click on it.
4. A dialog box will appear looking something like this:

5. The dialog is a template the shows you what function arguments are required (and in what order). Type in your range: D2:X in the box called "Number1" to the function.
6. Click OK to complete the formula. Notice that you don't need to type an = when you insert a function as the first thing in a cell.

Let's only show 2 digits to the right of the decimal place.

1. Select the 3 rows with your Average()s in them.
2. Select Cells in the Format Menu.
3. Select the Number tab.
4. Under "Category", select the second choice (Number), and you can specify how many decimal places to display (2).
5. Click OK

Standard Deviation

1. In the "D" column, in the cell below the last average, enter a formula that computes the stdev for the quiz scores in column D (but not the Average). You can either type in the formula (don't forget to start with an '='), or use the Insert->Function dialog.
2. Again, use "Fill Right" to copy the formula in to cells E through H.
3. Use the Format menu to change the format for "stdev" row to show only 2 decimal digits (0.00) like you did with the averages.

Label the Averages and Stdev rows as follows:

1. In column B, to the left of your "rows()" average, type "Average - rows".
2. In column B, to the left of your "count()" average, type "Average - count".
3. In column B, to the left of your Average(), type "Average - built in".
4. In column B, to the left of your first stdev(), put the word: "Stdev".

The bottom part of your spreadsheet should look something like this:

Compute Pass/NoPass Grades

We would like to create a formula that looks to see if a student has more that 70% of the 80 (70% * 80) points. And if she does, assigns her a "P" (pass) grade, otherwise assigns a "NP" (no pass) grade. Here's how to do it:

1. First, select cell I1 (capital "i", one), and label it: P/NP.
2. Now in cell I2, enter:

=if(H2>=80*70%, "P", "NP")

3. Click the check box in the formula bar or type RETURN to finish the formula.
Note: you must use the quote marks (") so the Excel knows you are referring to text to be printed rather than column P or NP. This is called quoting, where a special character (a (") in this case) is used to give or take away the special interpretation of the word or symbol following. Here the quotes are used to tell Excel that we are talking about just the letters "P" and "NP" rather than the names of columns or defined names.
4. Select I2 and then Fill Down.
5. You should now have a series of "P"s and a few "NP"s in column I.

	if(test_argument , true_consequent , false_consequent)

In our if(), the value/result was text ("P" or "NP"). It could just as well have been a number, the value in a cell, or a more complicated formula involving calculations and formulas.

Letter Grades

1. Copy the formula in cell I2 into cell J2.
2. Note that instead of referencing cell H2, the formula in J2 references cell I2. Again, that's because when you copy and paste formulas in Excel, the same relative cell relationships are kept.

=if(H2>=80*70%, "C", "F")

if(H2>=80*80%, "B", "C")

Use "Fill Down" to a replicate your new formula into J2 through the end of the data. You should see some "C"s (e.g., Maude Flanders), some "B"s (e.g., Otto Mans), and a few "F"s (e.g., Krusty Clown). You're not quite done yet, though. Students above 90% (80*90%) should get an "A".

Modify cell J2 again. Replace the "B" with yet another (embedded) if that results in an "A" if H2 is greater than or equal to 80*90%, otherwise a "B". (You will need to figure out what this new if formula is.) Your final formula with have 3 if's in it.

Use "Fill Down" to a copy your new formula into the J column. You should see some of the "B"s change into "A"s; for instance, Otto Mans.

Finally, label column J (J1), "Grades".

Define a Constant

• Use the Insert->Name->Define dialog to define a name of "MaxScore" which has the value of 80. Note: there is no space between the words "MaxScore", spaces are not allowed in Excel names.
• To enter the value in the "Refers To" box, type: =80
• Then click the OK button.

Now each place you use the number 80 and that 80 means the maximum quiz score value, you can use the name MaxScore. Test it out by replacing the 80 in cell I2 with MaxScore.

• Note the value of the cell I2.
• Now click on cell I2 to open it in the formula bar.
• Replace the 80 with MaxScore.
• Type Enter or click the Check box to finish the formula
• Verify the value is the same as in the first step--unchanged.

Delete a Column

1. Click in the A to select all of column A.
2. Select Delete in the Edit Menu
3. Notice that the all the columns have been renamed (slid to the left) and the formulas in columns G and H continue to work correctly!

Save your work.

Copy Data to a New Worksheet

1. Select the total scores in in the G column, but not the "Totals" heading.
2. Copy
3. Select Sheet 2 at the bottom of the workbook.
4. Select cell A1 in the new worksheet.
5. Paste. You should see a column of "#REF!" What happened?
Recall that column G on your original worksheet contained a formula that sum()ed the cells to the left of it. When you paste into column A, there are no cells to the left of column A to reference, so the formula can no longer work. You could paste further to the right, and the formula would be reasonable, except, of course, that there is no data in this spreadsheet for the formula to work on. If you think about it, what you really want is the values in the Totals column, not the formulas.

6. Select Undo Paste in the Edit menu to clear column A on your new worksheet. If you can't "undo" simply delete the column that you pasted.
7. Select cell A1 on your new worksheet again.
8. In the Edit menu, select "Paste Special..."
9. A new "Paste Special" dialog will appear that lets you select just what attributes of a cell you want to copy.
10. Click on the Values button, then click OK.
11. The values should now appear in column A.

Note: In Excel, in some cases, a little clipboard that appears when you can paste something. If it does appear you can select "Values only" there, rather than having to go to the Edit menu.

Sort and Create Percentage Formula

To convert a number into a percentage, you take the number (the cell in the A column) and divide it by the maximum value (MaxScore) and then multiply by 100. So select cell B1, and create the following formula:

=A1/MaxScore*100

Create the Chart

1. The formula values in the B column should still be selected. If not, reselect them.
2. Select Chart in the Insert Menu.
3. Select a chart type, in the dialog that appears.  For this assignment, the default "Column" chart is fine.
4. Click Next and a preview of the chart should appear.  You can change the Data Range for the chart in this dialog, but they should already be right.

Add a Title and Grid Lines

1. Click Next to get the Chart Options.
2. Select the "Titles" tab and in the Chart Title dialog, type in: "Course Final Percentages".
3. Select the "Gridlines..." tab.
4. Select "Major Gridlines" for the Y Axis.

Remove the Legend

1. Select the Legend tab and uncheck the "Show legend" box.
2. Click Next and place the chart as a new object in sheet 2.
3. Click Finish.

Adding Color

1. Click on the chart to select it.
2. Click on one of the data bars to select it.
3. Right-click on the data bar, and choose "Format Data Point"
4. In the "Format Data Point" dialog box, choose the "Fill" option (on the left)
5. At the bottom of the right side, click "Vary colors by point"
6. You should now have a colorful chart.

Copy the Chart to Sheet1

1. Click on the chart to select it
2. Copy
3. Select the Sheet1 tab at the lower left of Excel window
4. Paste

Summarizing Data:
A Pivot Table

Excel has some built in summary capabilities as well. One of these is the pivot table that lets you count, average, or find the minimum or maximum of values.

We're going to have you create a simple pivot table count of the letter grades, and then create a pie chart from that. This will show you how many students got an 'A', 'B', etc.

1. Select the column of letter grades (not P/NP ones), including the "Grades" heading from Sheet1 and copy
2. Now select sheet 3 at the bottom of the Excel window to open a new, blank sheet
3. Now paste in column A -- don't forget to Edit->Paste Special just the values, like you did before

1. If your column of grades, including the "Grades" heading, on sheet 3 is not still selected, reselect it.
2. Then start the wizard by selecting "PivotTable Report" in the Data menu
3. Step 1 asks the type of data, which is a Microsoft Excel list, and which you have selected already
4. Step 2 asks the range of data to use, which again you have already selected
5. Step 3 asks "Where do you want to put the Pivot Table Report"? Click "Existing Worksheet", and then type: C2 into the text box below "Existing Worksheet".
   

6. Still on the Step 3 dialog, click the "Layout" button which will bring up the dialog below.
   

7. We want to summarize by grade type ("A", "B", etc). To do that click on the "Grades" button on the right hand side and drag it on top of the word "Row" in the table in the middle of the dialog
8. And the summary we want is a count of the number of each grade. This you get by clicking on the "Grades" button again, and dragging it on top of the word "Data". Excel will change "Grades" to "Count of Grades", which is just what we want. Click OK.
9. You should now be back on Step 3, and can click finish.

A Pie Chart

1. Select the 4 "data" rows of your pivot table (rows "A" through "F") including both the letter and value to the right
2. Select Insert->Chart.
3. Then select "Pie" from the list of types. The default pie chart (the one Excel picks) should be fine, so click Finish
4. Click on the chart itself again.
5. On the "Formatting Palate", select "Other Options" (under Chart Options), and in Labels, select "Percentage".

Save your work at this point.

Rename the Sheets

1. Select Sheet1 in your Workbook
2. Use Format->Sheet->Rename and change the name of the sheet (tab at the bottom) to your class account code followed by Data (e.g., cg3fxx Data)
3. Select Sheet2; Use Format->Sheet->Rename and call it Totals Chart
4. Select Sheet3 Use Format->Sheet->Rename and call it Pivot Table
5. Select the Sheet with your pivot table chart (probably called Chart1) Use Format->Sheet->Rename and call it Pivot Chart

Before you log out, verify that your work is indeed saved on the file server (i.e., in the Class Resources folder).

Extra Credit

For extra credit, you can do one or both of the following. (Hint: you can use Help in MS Excel to get some information about "Conditional formatting" and/or "text formulas".)

1. Apply "Conditional Formatting" (in the Format menu) to the list of names on your first worksheet. The formatting should turn the background color of the cell (with the name on it) to red, if the student is going to get an 'F', and to green if the student is going to get an 'A'. There are a couple of ways to do this; pick the one that makes sense to you.
2. Create a new column to right of the list of names. Create a formula using text formulas to take the "Firstname Lastname" in the cell to the left and change it to "Lastname, Firstname". Important: to get credit we want to see a formula in that column, not just the names reversed.

Save this in your hw5 folder too. The name will depend on which (or both) you attempted.

• If you attempted just the first extra credit, call it cg3fxx-extra-1.xls (use your own account code, of course).
• If you attempted just the second extra credit, call it cg3fxx-extra-2.xls (use your own account code, of course).
• If you attempted both extra credits, call it cg3fxx-extra-12.xls (use your own account code, of course).

Portions ©opyright 1995-2005 Mark R. Wallen
Revised October 29, 2006 Mary ET Boyle
Revised for Macintosh, September, 2007 Mark Wallen